package com.ruoyi.common.core.utils.poi; import java.io.IOException; import java.io.InputStream; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.lang.reflect.ParameterizedType; import java.math.BigDecimal; import java.text.DecimalFormat; import java.time.LocalDate; import java.time.LocalDateTime; import java.util.ArrayList; import java.util.Arrays; import java.util.Collection; import java.util.Comparator; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Set; import java.util.stream.Collectors; import javax.servlet.http.HttpServletResponse; import org.apache.commons.lang3.ArrayUtils; import org.apache.commons.lang3.RegExUtils; import org.apache.commons.lang3.reflect.FieldUtils; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.ClientAnchor; import org.apache.poi.ss.usermodel.DataValidation; import org.apache.poi.ss.usermodel.DataValidationConstraint; import org.apache.poi.ss.usermodel.DataValidationHelper; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.Drawing; import org.apache.poi.ss.usermodel.FillPatternType; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.Name; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellRangeAddressList; import org.apache.poi.util.IOUtils; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFClientAnchor; import org.apache.poi.xssf.usermodel.XSSFDataValidation; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.ruoyi.common.core.annotation.Excel; import com.ruoyi.common.core.annotation.Excel.ColumnType; import com.ruoyi.common.core.annotation.Excel.Type; import com.ruoyi.common.core.annotation.Excels; import com.ruoyi.common.core.text.Convert; import com.ruoyi.common.core.utils.DateUtils; import com.ruoyi.common.core.utils.StringUtils; import com.ruoyi.common.core.utils.file.FileTypeUtils; import com.ruoyi.common.core.utils.file.ImageUtils; import com.ruoyi.common.core.utils.reflect.ReflectUtils; public class ExcelUtil<T> { private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class); public static final String FORMULA_REGEX_STR = "=|-|\+|@"; public static final String[] FORMULA_STR = { "=", "-", "+", "@" }; public static final int sheetSize = 65536; private String sheetName; private Type type; private Workbook wb; private Sheet sheet; private Map<String, CellStyle> styles; private List<T> list; private List<Object[]> fields; private int rownum; private String title; private short maxHeight; private int subMergedLastRowNum = 0; private int subMergedFirstRowNum = 1; private Method subMethod; private List<Field> subFields; private Map<Integer, Double> statistics = new HashMap<Integer, Double>(); private static final DecimalFormat DOUBLE_FORMAT = new DecimalFormat("######0.00"); public Class<T> clazz; public String[] excludeFields; public ExcelUtil(Class<T> clazz) { this.clazz = clazz; } public void hideColumn(String... fields) { this.excludeFields = fields; } public void init(List<T> list, String sheetName, String title, Type type) { if (list == null) { list = new ArrayList<T>(); } this.list = list; this.sheetName = sheetName; this.type = type; this.title = title; createExcelField(); createWorkbook(); createTitle(); createSubHead(); } public void createTitle() { if (StringUtils.isNotEmpty(title)) { subMergedFirstRowNum++; subMergedLastRowNum++; int titleLastCol = this.fields.size() - 1; if (isSubList()) { titleLastCol = titleLastCol + subFields.size() - 1; } Row titleRow = sheet.createRow(rownum == 0 ? rownum++ : 0); titleRow.setHeightInPoints(30); Cell titleCell = titleRow.createCell(0); titleCell.setCellStyle(styles.get("title")); titleCell.setCellValue(title); sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(), titleRow.getRowNum(), titleLastCol)); } } public void createSubHead() { if (isSubList()) { subMergedFirstRowNum++; subMergedLastRowNum++; Row subRow = sheet.createRow(rownum); int excelNum = 0; for (Object[] objects : fields) { Excel attr = (Excel) objects[1]; Cell headCell1 = subRow.createCell(excelNum); headCell1.setCellValue(attr.name()); headCell1.setCellStyle(styles.get(StringUtils.format("header_{}_{}", attr.headerColor(), attr.headerBackgroundColor()))); excelNum++; } int headFirstRow = excelNum - 1; int headLastRow = headFirstRow + subFields.size() - 1; if (headLastRow > headFirstRow) { sheet.addMergedRegion(new CellRangeAddress(rownum, rownum, headFirstRow, headLastRow)); } rownum++; } } public List<T> importExcel(InputStream is) throws Exception { return importExcel(is, 0); } public List<T> importExcel(InputStream is, int titleNum) throws Exception { return importExcel(StringUtils.EMPTY, is, titleNum); } public List<T> importExcel(String sheetName, InputStream is, int titleNum) throws Exception { this.type = Type.import; this.wb = WorkbookFactory.create(is);