package com.ruoyi.common.utils.poi; import com.ruoyi.common.annotation.Excel; import com.ruoyi.common.annotation.Excel.ColumnType; import com.ruoyi.common.annotation.Excels; import com.ruoyi.common.config.RuoYiConfig; import com.ruoyi.common.core.domain.AjaxResult; import com.ruoyi.common.core.text.Convert; import com.ruoyi.common.exception.UtilException; import com.ruoyi.common.utils.*; import com.ruoyi.common.utils.file.FileTypeUtils; import com.ruoyi.common.utils.file.ImageUtils; import com.ruoyi.common.utils.reflect.ReflectUtils; import org.apache.commons.lang3.ArrayUtils; import org.apache.commons.lang3.RegExUtils; import org.apache.commons.lang3.reflect.FieldUtils; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellRangeAddressList; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFClientAnchor; import org.apache.poi.xssf.usermodel.XSSFDataValidation; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import javax.servlet.http.HttpServletResponse; import java.io.*; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.lang.reflect.ParameterizedType; import java.math.BigDecimal; import java.nio.file.Files; import java.nio.file.Paths; import java.text.DecimalFormat; import java.time.LocalDate; import java.time.LocalDateTime; import java.util.*; import java.util.concurrent.atomic.AtomicInteger; import java.util.function.Predicate; import java.util.stream.Collectors; /** * Excel 处理工具类 - 支持分页导出和大批量数据处理 * * @author ruoyi */ public class ExcelUtil { private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class); // 常量定义 private static final int MAX_ROWS_PER_SHEET = 1_048_576; // Excel 单 Sheet 最大行数 private static final int ROW_ACCESS_WINDOW_SIZE = 1000; // SXSSFWorkbook 内存窗口大小,超出后自动写入磁盘 private static final String FORMULA_REGEX = "=|-|\\+|@" ; // 公式触发字符正则表达式 private static final String[] FORMULA_CHARS = {"=", "-", "+", "@"}; // 可能触发公式的字符 private static final DecimalFormat DOUBLE_FORMAT = new DecimalFormat("######0.00"); // 数字格式化 private static final String DEFAULT_FONT = "Arial" ; // 默认字体 // 核心属性 private final Class clazz; // 实体类类型 private SXSSFWorkbook workbook; // Excel 工作簿对象 private Workbook wb; // Excel 工作簿对象 private Sheet currentSheet; // 当前工作表 private Map styles; // 单元格样式缓存 private List fields; // 字段及其注解信息 private final AtomicInteger currentRowNum = new AtomicInteger(0); // 当前 Sheet 的行数 private final AtomicInteger totalRowCount = new AtomicInteger(0); // 所有 Sheet 的总行数 private int sheetIndex = 0; // 当前 Sheet 索引 // 配置属性 private String sheetName; // 工作表名称 private String title; // 标题 private Excel.Type exportType; // 导出类型(EXPORT/IMPORT) private short maxRowHeight; // 最大行高 private String[] includedColumns; // 包含的列 private String[] excludedColumns; // 排除的列 private Method subListMethod; // 子列表获取方法 private List subFields; // 子列表字段 private final Map dictCache = new HashMap<>(); // 字典缓存,避免重复查询 private final Map statistics = new HashMap<>(); // 统计数据 private boolean isInitialized = false; // 初始化标志 /** * 构造方法,初始化实体类类型 * * @param clazz 实体类类型 */ public ExcelUtil(Class clazz) { this.clazz = Objects.requireNonNull(clazz, "Class type cannot be null"); } /** * 设置需要导出的列 * * @param columns 列名数组,例如 {"id", "name"} */ public void includeColumns(String... columns) { this.includedColumns = columns; } /** * 设置需要排除的列 * * @param columns 列名数组,例如 {"id", "name"} */ public void excludeColumns(String... columns) { this.excludedColumns = columns; } /** * 初始化工具类,准备导出环境 * * @param sheetName 工作表名称 * @param title 标题 * @param type 导出类型(EXPORT/IMPORT) */ public void initialize(String sheetName, String title, Excel.Type type) { this.sheetName = Objects.requireNonNull(sheetName, "Sheet name cannot be null"); this.title = title; this.exportType = type; prepareWorkbook(); this.isInitialized = true; } /** * 准备工作簿,初始化字段、样式和工作表 */ private void prepareWorkbook() { initializeFields(); workbook = new SXSSFWorkbook(ROW_ACCESS_WINDOW_SIZE); currentSheet = workbook.createSheet(sheetName + (sheetIndex == 0 ? "" : "_" + sheetIndex)); styles = createStyles(workbook); createTitleRow(); if (hasSubList()) { createSubHeader(); } writeHeaderRow(); } /** * 创建标题行 */ private void createTitleRow() { if (StringUtils.isEmpty(title)) return; Row titleRow = currentSheet.createRow(currentRowNum.getAndIncrement()); titleRow.setHeightInPoints(30); Cell titleCell = titleRow.createCell(0); titleCell.setCellStyle(styles.get("title")); titleCell.setCellValue(title); int lastCol = fields.size() - 1 + (hasSubList() ? subFields.size() - 1 : 0); currentSheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(), 0, lastCol)); } /** * 创建子表头(针对嵌套列表) */ private void createSubHeader() { Row subRow = currentSheet.createRow(currentRowNum.getAndIncrement()); int column = 0; int subFieldSize = Optional.ofNullable(subFields).map(List::size).orElse(0); for (Object[] fieldInfo : fields) { Field field = (Field) fieldInfo[0]; Excel attr = (Excel) fieldInfo[1]; Cell cell = subRow.createCell(column); cell.setCellValue(attr.name()); cell.setCellStyle(styles.get(getHeaderStyleKey(attr))); if (Collection.class.isAssignableFrom(field.getType()) && subFieldSize > 1) { currentSheet.addMergedRegion(new CellRangeAddress(subRow.getRowNum(), subRow.getRowNum(), column, column + subFieldSize - 1)); column += subFieldSize; } else { column++; } } } /** * 写入表头行 */ private void writeHeaderRow() { Row row = currentSheet.createRow(currentRowNum.getAndIncrement()); int column = 0; for (Object[] fieldInfo : fields) { Field field = (Field) fieldInfo[0]; Excel excel = (Excel) fieldInfo[1]; if (Collection.class.isAssignableFrom(field.getType())) { for (Field subField : subFields) { createHeadCell(subField.getAnnotation(Excel.class), row, column++); } } else { createHeadCell(excel, row, column++); } } } /** * 分页导出数据到 HttpServletResponse * * @param data 当前页数据 */ public void exportExcel(List data) { validateInitialized(); appendData(data); } /** * 验证是否已初始化 */ private void validateInitialized() { if (!isInitialized) { throw new IllegalStateException("ExcelUtil must be initialized before exporting."); } } /** * 追加数据到当前工作簿 * * @param data 要追加的数据列表 */ private void appendData(List data) { if (data == null || data.isEmpty()) return; int rowsToAdd = calculateRowsWithSubLists(data); checkAndSwitchSheet(rowsToAdd); fillData(data); totalRowCount.addAndGet(rowsToAdd); } /** * 计算数据总行数(包括子列表) * * @param data 数据列表 * @return 总行数 */ private int calculateRowsWithSubLists(List data) { return data.stream().mapToInt(this::getMaxSubListSize).sum(); } /** * 检查是否需要切换 Sheet * * @param rowsToAdd 新增行数 */ private void checkAndSwitchSheet(int rowsToAdd) { if (currentRowNum.get() + rowsToAdd > MAX_ROWS_PER_SHEET) { addStatisticsRow(); sheetIndex++; currentSheet = workbook.createSheet(sheetName + "_" + sheetIndex); currentRowNum.set(0); createTitleRow(); if (hasSubList()) createSubHeader(); writeHeaderRow(); } } /** * 填充数据到当前 Sheet * * @param data 数据列表 */ private void fillData(List data) { for (T item : data) { Row row = currentSheet.createRow(currentRowNum.getAndIncrement()); int column = 0; int maxSubSize = getMaxSubListSize(item); for (Object[] fieldInfo : fields) { Field field = (Field) fieldInfo[0]; Excel excel = (Excel) fieldInfo[1]; if (Collection.class.isAssignableFrom(field.getType())) { fillSubList(item, field, excel, column, row.getRowNum()); column += subFields.size(); } else { addCell(excel, row, item, field, column); if (maxSubSize > 1 && excel.needMerge()) { currentSheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum() + maxSubSize - 1, column, column)); } column++; } } currentRowNum.addAndGet(maxSubSize - 1); } } /** * 填充子列表数据 * * @param item 主对象 * @param field 子列表字段 * @param excel 注解信息 * @param startColumn 起始列 * @param startRow 起始行 */ private void fillSubList(T item, Field field, Excel excel, int startColumn, int startRow) { try { Collection subList = (Collection) getTargetValue(item, field, excel); if (subList != null && !subList.isEmpty()) { int rowIndex = 0; for (Object subItem : subList) { int finalRowIndex = rowIndex; Row subRow = Optional.ofNullable(currentSheet.getRow(startRow + rowIndex)) .orElseGet(() -> currentSheet.createRow(startRow + finalRowIndex)); int subColumn = startColumn; for (Field subField : subFields) { addCell(subField.getAnnotation(Excel.class), subRow, (T) subItem, subField, subColumn++); } rowIndex++; } } } catch (Exception e) { log.error("Failed to fill sublist: {}", e.getMessage(), e); } } /** * 完成导出到 HTTP 响应 * * @param response HTTP 响应对象 */ public void finishExport(HttpServletResponse response) { validateInitialized(); addStatisticsRow(); try (OutputStream out = response.getOutputStream()) { workbook.write(out); } catch (Exception e) { log.error("Failed to finish export: {}", e.getMessage(), e); throw new UtilException("Export failed!"); } finally { closeWorkbook(); } } /** * 完成导出到文件 * * @param fileName 文件名称 */ public void finishExport(String fileName) { validateInitialized(); addStatisticsRow(); try (OutputStream out = Files.newOutputStream(Paths.get(getAbsoluteFile(fileName)))) { workbook.write(out); } catch (Exception e) { log.error("Failed to finish export: {}", e.getMessage(), e); throw new UtilException("Export failed!"); } finally { closeWorkbook(); } } /** * 创建单元格样式集合 * * @param wb 工作簿对象 * @return 样式映射 */ private Map createStyles(Workbook wb) { Map styles = new HashMap<>(); styles.put("title", createTitleStyle(wb)); styles.put("data", createDataStyle(wb)); styles.put("total", createTotalStyle(wb)); styles.putAll(createAnnotationStyles(wb)); return styles; } /** * 创建标题样式 * * @param wb 工作簿对象 * @return 标题样式 */ private CellStyle createTitleStyle(Workbook wb) { CellStyle style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); Font font = wb.createFont(); font.setFontName(DEFAULT_FONT); font.setFontHeightInPoints((short) 16); font.setBold(true); style.setFont(font); style.setDataFormat(wb.createDataFormat().getFormat("@")); return style; } /** * 创建数据单元格样式 * * @param wb 工作簿对象 * @return 数据样式 */ private CellStyle createDataStyle(Workbook wb) { CellStyle style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); setDefaultBorders(style); Font font = wb.createFont(); font.setFontName(DEFAULT_FONT); font.setFontHeightInPoints((short) 10); style.setFont(font); return style; } /** * 创建统计行样式 * * @param wb 工作簿对象 * @return 统计样式 */ private CellStyle createTotalStyle(Workbook wb) { CellStyle style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); Font font = wb.createFont(); font.setFontName(DEFAULT_FONT); font.setFontHeightInPoints((short) 10); style.setFont(font); return style; } /** * 根据注解创建样式 * * @param wb 工作簿对象 * @return 注解样式映射 */ private Map createAnnotationStyles(Workbook wb) { Map styles = new HashMap<>(); fields.forEach(fieldInfo -> { Field field = (Field) fieldInfo[0]; Excel excel = (Excel) fieldInfo[1]; if (Collection.class.isAssignableFrom(field.getType())) { ParameterizedType pt = (ParameterizedType) field.getGenericType(); Class subClass = (Class) pt.getActualTypeArguments()[0]; FieldUtils.getFieldsListWithAnnotation(subClass, Excel.class) .forEach(subField -> createStylesForField(styles, wb, subField)); } else { createStylesForField(styles, wb, field); } }); return styles; } /** * 为字段创建样式 * * @param styles 样式映射 * @param wb 工作簿对象 * @param field 字段 */ private void createStylesForField(Map styles, Workbook wb, Field field) { Excel excel = field.getAnnotation(Excel.class); if (excel == null) { return; } String headerKey = getHeaderStyleKey(excel); if (!styles.containsKey(headerKey)) { CellStyle style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setFillForegroundColor(excel.headerBackgroundColor().index); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); Font font = wb.createFont(); font.setFontName(DEFAULT_FONT); font.setFontHeightInPoints((short) 10); font.setBold(true); font.setColor(excel.headerColor().index); style.setFont(font); style.setDataFormat(wb.createDataFormat().getFormat("@")); styles.put(headerKey, style); } String dataKey = getDataStyleKey(excel); if (!styles.containsKey(dataKey)) { CellStyle style = wb.createCellStyle(); style.setAlignment(excel.align()); style.setVerticalAlignment(VerticalAlignment.CENTER); setDefaultBorders(style); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFillForegroundColor(excel.backgroundColor().getIndex()); style.setWrapText(excel.wrapText()); Font font = wb.createFont(); font.setFontName(DEFAULT_FONT); font.setFontHeightInPoints((short) 10); font.setColor(excel.color().index); style.setFont(font); if (Excel.ColumnType.TEXT == excel.cellType()) { style.setDataFormat(wb.createDataFormat().getFormat("@")); } styles.put(dataKey, style); } } /** * 创建表头单元格 * * @param attr 注解信息 * @param row 行对象 * @param column 列索引 * @return 创建的单元格 */ private Cell createHeadCell(Excel attr, Row row, int column) { Cell cell = row.createCell(column); cell.setCellValue(attr.name()); setDataValidation(attr, row, column); cell.setCellStyle(styles.get(getHeaderStyleKey(attr))); if (hasSubList() && attr.needMerge()) { currentSheet.setDefaultColumnStyle(column, styles.get(getDataStyleKey(attr))); currentSheet.addMergedRegion(new CellRangeAddress(currentRowNum.get() - 1, currentRowNum.get(), column, column)); } return cell; } /** * 添加单元格并设置值 * * @param attr 注解信息 * @param row 行对象 * @param item 数据对象 * @param field 字段 * @param column 列索引 * @return 创建的单元格 */ private Cell addCell(Excel attr, Row row, T item, Field field, int column) { if (!attr.isExport()) return null; row.setHeight(maxRowHeight); Cell cell = row.createCell(column); cell.setCellStyle(styles.get(getDataStyleKey(attr))); try { Object value = getTargetValue(item, field, attr); setCellValue(cell, value, attr); addStatisticsData(column, Convert.toStr(value), attr); } catch (Exception e) { log.error("Failed to add cell at column {}: {}", column, e.getMessage(), e); } return cell; } /** * 设置单元格值 * * @param cell 单元格对象 * @param value 值 * @param attr 注解信息 */ private void setCellValue(Cell cell, Object value, Excel attr) { if (ColumnType.NUMERIC == attr.cellType() && value != null) { String strValue = Convert.toStr(value); cell.setCellValue(StringUtils.contains(strValue, ".") ? Convert.toDouble(value) : Convert.toInt(value)); } else if (ColumnType.IMAGE == attr.cellType()) { setImageCellValue(cell, value); } else { handleSpecialCases(cell, value, attr); } } /** * 设置字符串类型的单元格值 * * @param cell 单元格对象 * @param value 值 * @param attr 注解信息 */ private void setStringCellValue(Cell cell, Object value, Excel attr) { String cellValue = Convert.toStr(value); if (StringUtils.startsWithAny(cellValue, FORMULA_CHARS)) { cellValue = RegExUtils.replaceFirst(cellValue, FORMULA_REGEX, "\t$0"); // 防止 CSV 注入 } if (value instanceof Collection && "[]".equals(cellValue)) { cellValue = StringUtils.EMPTY; } cell.setCellValue(cellValue == null ? attr.defaultValue() : cellValue + attr.suffix()); } /** * 设置图片类型的单元格值 * * @param cell 单元格对象 * @param value 图片路径值 */ private void setImageCellValue(Cell cell, Object value) { String imagePath = Convert.toStr(value); if (StringUtils.isNotEmpty(imagePath)) { try { byte[] data = ImageUtils.getImage(imagePath); ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + 1), cell.getRow().getRowNum() + 1); getDrawingPatriarch(cell.getSheet()).createPicture(anchor, workbook.addPicture(data, getImageType(data))); } catch (Exception e) { log.error("Failed to set image cell value: {}", e.getMessage(), e); } } } /** * 处理特殊类型单元格值(日期、字典等) * * @param cell 单元格对象 * @param value 值 * @param attr 注解信息 */ private void handleSpecialCases(Cell cell, Object value, Excel attr) { if (value == null) return; if (StringUtils.isNotEmpty(attr.dateFormat())) { cell.setCellValue(parseDateToStr(attr.dateFormat(), value)); } else if (StringUtils.isNotEmpty(attr.readConverterExp())) { cell.setCellValue(convertByExp(Convert.toStr(value), attr.readConverterExp(), attr.separator())); } else if (StringUtils.isNotEmpty(attr.dictType())) { cell.setCellValue(getDictLabel(Convert.toStr(value), attr.dictType(), attr.separator())); } else if (value instanceof BigDecimal && attr.scale() != -1) { cell.setCellValue(((BigDecimal) value).setScale(attr.scale(), attr.roundingMode()).doubleValue()); } else if (!attr.handler().equals(ExcelHandlerAdapter.class)) { cell.setCellValue(dataFormatHandlerAdapter(value, attr, cell)); } else if (Excel.ColumnType.STRING == attr.cellType() || ColumnType.TEXT == attr.cellType()) { setStringCellValue(cell, value, attr); } } /** * 设置数据验证(如下拉框) * * @param attr 注解信息 * @param row 行对象 * @param column 列索引 */ private void setDataValidation(Excel attr, Row row, int column) { currentSheet.setColumnWidth(column, attr.name().contains("注:") ? 6000 : (int) ((attr.width() + 0.72) * 256)); if (StringUtils.isNotEmpty(attr.prompt()) || attr.combo().length > 0 || attr.comboReadDict()) { String[] comboArray = getComboArray(attr); if (comboArray.length > 15 || StringUtils.join(comboArray).length() > 255) { setXSSFValidationWithHidden(currentSheet, comboArray, attr.prompt(), 1, 100, column, column); } else { setPromptOrValidation(currentSheet, comboArray, attr.prompt(), 1, 100, column, column); } } } /** * 获取下拉框选项数组 * * @param attr 注解信息 * @return 下拉选项数组 */ private String[] getComboArray(Excel attr) { if (!attr.comboReadDict()) return attr.combo(); String key = "combo_" + attr.dictType(); return dictCache.computeIfAbsent(key, k -> DictUtils.getDictLabels(attr.dictType())) .split(DictUtils.SEPARATOR); } /** * 设置简单下拉框或提示 * * @param sheet 工作表 * @param textList 下拉选项 * @param promptContent 提示内容 * @param firstRow 开始行 * @param endRow 结束行 * @param firstCol 开始列 * @param endCol 结束列 */ private void setPromptOrValidation(Sheet sheet, String[] textList, String promptContent, int firstRow, int endRow, int firstCol, int endCol) { DataValidationHelper helper = sheet.getDataValidationHelper(); DataValidationConstraint constraint = textList.length > 0 ? helper.createExplicitListConstraint(textList) : helper.createCustomConstraint("DD1"); CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol); DataValidation validation = helper.createValidation(constraint, regions); configureValidation(validation, promptContent); sheet.addValidationData(validation); } /** * 设置隐藏 Sheet 的下拉框(处理长列表) * * @param sheet 工作表 * @param textList 下拉选项 * @param promptContent 提示内容 * @param firstRow 开始行 * @param endRow 结束行 * @param firstCol 开始列 * @param endCol 结束列 */ private void setXSSFValidationWithHidden(Sheet sheet, String[] textList, String promptContent, int firstRow, int endRow, int firstCol, int endCol) { String hideSheetName = "combo_" + firstCol + "_" + endCol; Sheet hideSheet = workbook.createSheet(hideSheetName); for (int i = 0; i < textList.length; i++) { hideSheet.createRow(i).createCell(0).setCellValue(textList[i]); } Name name = workbook.createName(); name.setNameName(hideSheetName + "_data"); name.setRefersToFormula(hideSheetName + "!$A$1:$A$" + textList.length); DataValidationHelper helper = sheet.getDataValidationHelper(); DataValidationConstraint constraint = helper.createFormulaListConstraint(hideSheetName + "_data"); CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol); DataValidation validation = helper.createValidation(constraint, regions); configureValidation(validation, promptContent); sheet.addValidationData(validation); workbook.setSheetHidden(workbook.getSheetIndex(hideSheet), true); } // 工具方法 /** * 获取表头样式键 * * @param attr 注解信息 * @return 样式键 */ private String getHeaderStyleKey(Excel attr) { return StringUtils.format("header_{}_{}", attr.headerColor(), attr.headerBackgroundColor()); } /** * 获取数据样式键 * * @param attr 注解信息 * @return 样式键 */ private String getDataStyleKey(Excel attr) { return StringUtils.format("data_{}_{}_{}_{}_{}", attr.align(), attr.color(), attr.backgroundColor(), attr.cellType(), attr.wrapText()); } /** * 设置默认边框样式 * * @param style 单元格样式 */ private void setDefaultBorders(CellStyle style) { style.setBorderRight(BorderStyle.THIN); style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderLeft(BorderStyle.THIN); style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderTop(BorderStyle.THIN); style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); } /** * 配置 HTTP 响应头 * * @param response HTTP 响应对象 */ private void configureResponse(HttpServletResponse response) { response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); } /** * 生成唯一文件名 * * @param name 原始名称 * @return 唯一文件名 */ public static String encodeFileName(String name) { return UUID.randomUUID() + "_" + name + ".xlsx" ; } /** * 获取文件绝对路径并创建目录 * * @param filename 文件名 * @return 文件绝对路径 * @throws IOException 如果创建目录失败 */ private String getAbsoluteFile(String filename) throws IOException { String path = RuoYiConfig.getDownloadPath() + filename; File file = new File(path); //FileUtils.createParentDirectories(file); return path; } /** * 关闭工作簿并释放资源 */ private void closeWorkbook() { try { if (workbook != null) { workbook.dispose(); workbook.close(); } } catch (IOException e) { log.error("Failed to close workbook: {}", e.getMessage(), e); } } /** * 检查是否包含子列表 * * @return 是否有子列表 */ private boolean hasSubList() { return subFields != null && !subFields.isEmpty(); } /** * 获取绘图对象 * * @param sheet 工作表 * @return 绘图对象 */ private Drawing getDrawingPatriarch(Sheet sheet) { if (sheet instanceof XSSFSheet) { return Optional.ofNullable(((XSSFSheet) sheet).getDrawingPatriarch()) .orElseGet(() -> ((XSSFSheet) sheet).createDrawingPatriarch()); } else if (sheet instanceof HSSFSheet) { return Optional.ofNullable(((HSSFSheet) sheet).getDrawingPatriarch()) .orElseGet(() -> ((HSSFSheet) sheet).createDrawingPatriarch()); } else { throw new IllegalArgumentException("Unsupported sheet type"); } } /** * 获取图片类型 * * @param value 图片字节数组 * @return 图片类型常量 */ private int getImageType(byte[] value) { String type = FileTypeUtils.getFileExtendName(value); return "PNG".equalsIgnoreCase(type) ? Workbook.PICTURE_TYPE_PNG : Workbook.PICTURE_TYPE_JPEG; } /** * 获取子列表最大行数 * * @param item 数据对象 * @return 子列表最大行数 */ private int getMaxSubListSize(T item) { return fields.stream() .filter(f -> Collection.class.isAssignableFrom(((Field) f[0]).getType())) .map(f -> { try { Collection subList = (Collection) getTargetValue(item, (Field) f[0], (Excel) f[1]); return subList != null ? subList.size() : 0; } catch (Exception e) { log.error("Failed to get sublist size: {}", e.getMessage(), e); return 0; } }) .max(Integer::compareTo) .orElse(1); } /** * 获取目标字段值(支持嵌套属性) * * @param item 数据对象 * @param field 字段 * @param excel 注解信息 * @return 字段值 * @throws Exception 如果反射获取失败 */ private Object getTargetValue(T item, Field field, Excel excel) throws Exception { field.setAccessible(true); Object value = field.get(item); if (StringUtils.isNotEmpty(excel.targetAttr())) { String[] targets = excel.targetAttr().split("\\."); for (String target : targets) { value = getNestedValue(value, target); } } return value; } /** * 获取嵌套字段值 * * @param obj 对象 * @param name 字段名 * @return 字段值 * @throws Exception 如果反射获取失败 */ private Object getNestedValue(Object obj, String name) throws Exception { if (obj == null || StringUtils.isEmpty(name)) return obj; Field field = obj.getClass().getDeclaredField(name); field.setAccessible(true); return field.get(obj); } /** * 初始化字段和注解信息 */ private void initializeFields() { fields = getFields(); fields.sort(Comparator.comparing(f -> ((Excel) f[1]).sort())); maxRowHeight = calculateMaxRowHeight(); } /** * 获取字段及其注解信息 * * @return 字段和注解列表 */ private List getFields() { List result = new ArrayList<>(); List allFields = new ArrayList<>(); allFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields())); allFields.addAll(Arrays.asList(clazz.getDeclaredFields())); Predicate filter = includedColumns != null ? f -> ArrayUtils.contains(includedColumns, f.getName()) || f.isAnnotationPresent(Excels.class) : excludedColumns != null ? f -> !ArrayUtils.contains(excludedColumns, f.getName()) : f -> true; allFields.stream().filter(filter).forEach(f -> addFieldAnnotations(result, f)); return result; } /** * 添加字段的注解信息 * * @param fields 字段列表 * @param field 字段 */ private void addFieldAnnotations(List fields, Field field) { if (field.isAnnotationPresent(Excel.class)) { Excel attr = field.getAnnotation(Excel.class); if (attr.type() == Excel.Type.ALL || attr.type() == exportType) { fields.add(new Object[]{field, attr}); if (Collection.class.isAssignableFrom(field.getType())) { initializeSubList(field); } } } if (field.isAnnotationPresent(Excels.class)) { for (Excel attr : field.getAnnotation(Excels.class).value()) { if (isFieldIncluded(field, attr)) { fields.add(new Object[]{field, attr}); } } } } /** * 判断字段是否需要包含 * * @param field 字段 * @param attr 注解信息 * @return 是否包含 */ private boolean isFieldIncluded(Field field, Excel attr) { String fullName = field.getName() + "." + attr.targetAttr(); return (includedColumns != null && ArrayUtils.contains(includedColumns, fullName)) || (excludedColumns == null || !ArrayUtils.contains(excludedColumns, fullName)) && (attr.type() == Excel.Type.ALL || attr.type() == exportType); } /** * 初始化子列表相关信息 * * @param field 子列表字段 */ private void initializeSubList(Field field) { subListMethod = getSubMethod(field.getName(), clazz); ParameterizedType pt = (ParameterizedType) field.getGenericType(); Class subClass = (Class) pt.getActualTypeArguments()[0]; subFields = FieldUtils.getFieldsListWithAnnotation(subClass, Excel.class); } /** * 计算最大行高 * * @return 最大行高 */ private short calculateMaxRowHeight() { return (short) (fields.stream() .mapToDouble(f -> ((Excel) f[1]).height()) .max() .orElse(0) * 20); } /** * 获取子列表 getter 方法 * * @param name 字段名 * @param pojoClass 类对象 * @return getter 方法 */ private Method getSubMethod(String name, Class pojoClass) { try { return pojoClass.getMethod("get" + StringUtils.capitalize(name)); } catch (Exception e) { log.error("Failed to get sub method for {}: {}", name, e.getMessage(), e); return null; } } /** * 添加统计数据 * * @param column 列索引 * @param text 值字符串 * @param attr 注解信息 */ private void addStatisticsData(int column, String text, Excel attr) { if (attr.isStatistics()) { double value = statistics.getOrDefault(column, 0.0); try { value += Double.parseDouble(text); } catch (NumberFormatException ignored) { } statistics.put(column, value); } } /** * 添加统计行 */ private void addStatisticsRow() { if (statistics.isEmpty()) return; Row row = currentSheet.createRow(currentSheet.getLastRowNum() + 1); Cell cell = row.createCell(0); cell.setCellStyle(styles.get("total")); cell.setCellValue("合计"); statistics.forEach((col, value) -> { Cell statCell = row.createCell(col); statCell.setCellStyle(styles.get("total")); statCell.setCellValue(DOUBLE_FORMAT.format(value)); }); statistics.clear(); } /** * 获取字典标签 * * @param value 值 * @param dictType 字典类型 * @param separator 分隔符 * @return 字典标签 */ private String getDictLabel(String value, String dictType, String separator) { String key = dictType + value; return dictCache.computeIfAbsent(key, k -> DictUtils.getDictLabel(dictType, value, separator)); } /** * 格式化日期值 * * @param dateFormat 日期格式 * @param value 值 * @return 格式化后的字符串 */ private String parseDateToStr(String dateFormat, Object value) { if (value == null) return "" ; if (value instanceof Date) { return DateUtils.parseDateToStr(dateFormat, (Date) value); } else if (value instanceof LocalDateTime) { return DateUtils.parseDateToStr(dateFormat, DateUtils.toDate((LocalDateTime) value)); } else if (value instanceof LocalDate) { return DateUtils.parseDateToStr(dateFormat, DateUtils.toDate((LocalDate) value)); } return value.toString(); } /** * 根据表达式转换值 * * @param value 值 * @param converterExp 转换表达式 * @param separator 分隔符 * @return 转换后的值 */ public static String convertByExp(String value, String converterExp, String separator) { StringBuilder result = new StringBuilder(); String[] conversions = converterExp.split(","); if (StringUtils.containsAny(value, separator)) { for (String val : value.split(separator)) { for (String conv : conversions) { String[] pair = conv.split("="); if (pair[0].equals(val)) { result.append(pair[1]).append(separator); break; } } } return StringUtils.stripEnd(result.toString(), separator); } return Arrays.stream(conversions) .map(conv -> conv.split("=")) .filter(pair -> pair[0].equals(value)) .findFirst() .map(pair -> pair[1]) .orElse(value); } /** * 使用自定义处理器格式化数据 * * @param value 值 * @param excel 注解信息 * @param cell 单元格对象 * @return 格式化后的值 */ private String dataFormatHandlerAdapter(Object value, Excel excel, Cell cell) { try { Object instance = excel.handler().getDeclaredConstructor().newInstance(); Method formatMethod = excel.handler().getMethod("format", Object.class, String[].class, Cell.class, Workbook.class); return Convert.toStr(formatMethod.invoke(instance, value, excel.args(), cell, workbook)); } catch (Exception e) { log.error("Failed to format data with handler {}: {}", excel.handler(), e.getMessage(), e); return Convert.toStr(value); } } /** * 配置数据验证对象 * * @param validation 数据验证对象 * @param promptContent 提示内容 */ private void configureValidation(DataValidation validation, String promptContent) { if (StringUtils.isNotEmpty(promptContent)) { validation.createPromptBox("", promptContent); validation.setShowPromptBox(true); } if (validation instanceof XSSFDataValidation) { validation.setSuppressDropDownArrow(true); validation.setShowErrorBox(true); } else { validation.setSuppressDropDownArrow(false); } } public List importExcel(InputStream is) throws Exception { return importExcel(is, 10000); } public List importExcel(InputStream is, int maxSize) throws Exception { return importExcel(StringUtils.EMPTY, is, maxSize); } public List importExcel(String sheetName, InputStream is, int maxSize) throws Exception { if (is == null) { throw new IllegalArgumentException("输入流不能为空"); } if (maxSize <= 0) { throw new IllegalArgumentException("最大行数必须大于0"); } this.exportType = Excel.Type.IMPORT; try (Workbook wb = WorkbookFactory.create(is)) { this.wb = wb; Sheet sheet = StringUtils.isNotEmpty(sheetName) ? wb.getSheet(sheetName) : wb.getSheetAt(0); if (sheet == null) { throw new IOException("指定的工作表不存在: " + sheetName); } List list = new ArrayList<>(); int rows = Math.min(sheet.getPhysicalNumberOfRows(), maxSize + 1); // 加1包含表头 if (rows <= 1) { // 仅表头,无数据 return list; } Map cellMap = buildHeaderMap(sheet.getRow(0)); Map fieldsMap = buildFieldsMap(cellMap); processRows(sheet, rows, fieldsMap, list); return list; } catch (Exception e) { throw new Exception("Excel导入失败: " + e.getMessage(), e); } } /** * 构建表头映射 */ private Map buildHeaderMap(Row headerRow) { Map cellMap = new HashMap<>(); int cellCount = headerRow.getPhysicalNumberOfCells(); for (int i = 0; i < cellCount; i++) { Cell cell = headerRow.getCell(i); String value = (cell != null) ? getCellValue(headerRow, i, null).toString().trim() : null; cellMap.put(value, i); } return cellMap; } /** * 构建字段映射 */ private Map buildFieldsMap(Map cellMap) { Map fieldsMap = new HashMap<>(); Field[] allFields = clazz.getDeclaredFields(); for (Field field : allFields) { Excel attr = field.getAnnotation(Excel.class); if (attr != null && (attr.type() == Excel.Type.ALL || attr.type() == this.exportType)) { field.setAccessible(true); Integer column = cellMap.get(attr.name()); if (column != null) { fieldsMap.put(column, field); } } } return fieldsMap; } /** * 处理数据行 */ private void processRows(Sheet sheet, int rows, Map fieldsMap, List list) throws Exception { for (int i = 1; i < rows; i++) { Row row = sheet.getRow(i); if (row == null) continue; T entity = null; boolean isValid = true; for (Map.Entry entry : fieldsMap.entrySet()) { Field field = entry.getValue(); Excel attr = field.getAnnotation(Excel.class); Object val = getCellValue(row, entry.getKey(), attr); if (attr.isRequired() && (val == null || Convert.toStr(val).trim().isEmpty())) { isValid = false; break; } entity = (entity == null) ? clazz.newInstance() : entity; val = convertValue(val, field.getType(), attr); if (val != null || !attr.isRequired()) { setFieldValue(entity, field, val, attr); } } if (isValid && entity != null) { list.add(entity); } } } /** * 转换单元格值为目标类型 */ private Object convertValue(Object val, Class fieldType, Excel attr) { String strVal = Convert.toStr(val).trim(); if (StringUtils.isEmpty(strVal)) return null; try { if (String.class == fieldType) { return StringUtils.endsWith(strVal, ".0") ? StringUtils.substringBefore(strVal, ".0") : strVal; } else if (Integer.class == fieldType || Integer.TYPE == fieldType) { return StringUtils.isNumeric(strVal) ? Convert.toInt(strVal) : null; } else if (Long.class == fieldType || Long.TYPE == fieldType) { return Convert.toLong(strVal); } else if (Double.class == fieldType || Double.TYPE == fieldType) { return Convert.toDouble(strVal); } else if (Float.class == fieldType || Float.TYPE == fieldType) { return Convert.toFloat(strVal); } else if (BigDecimal.class == fieldType) { return Convert.toBigDecimal(strVal); } else if (Date.class == fieldType) { return (val instanceof String) ? DateUtils.parseDate(strVal) : DateUtil.getJavaDate(Convert.toDouble(val)); } } catch (Exception e) { // 可添加日志:log.warn("类型转换失败: {} -> {}", val, fieldType, e); } return val; } /** * 设置字段值 */ private void setFieldValue(T entity, Field field, Object val, Excel attr) throws Exception { String propertyName = field.getName(); if (StringUtils.isNotEmpty(attr.targetAttr())) { propertyName += "." + attr.targetAttr(); } else if (StringUtils.isNotEmpty(attr.readConverterExp())) { val = reverseByExp(Convert.toStr(val), attr.readConverterExp(), attr.separator()); } else if (StringUtils.isNotEmpty(attr.dictType())) { val = reverseDictByExp(Convert.toStr(val), attr.dictType(), attr.separator()); } ReflectUtils.invokeSetter(entity, propertyName, val); } /** * 获取单元格值(示例实现,需根据实际调整) */ private Object getCellValue(Row row, int column, Excel excel) { Cell cell = row.getCell(column); if (cell == null) return null; switch (cell.getCellType()) { case STRING: return cell.getStringCellValue(); case NUMERIC: { if (DateUtil.isCellDateFormatted(cell)) { return cell.getDateCellValue(); } if (excel != null) { if (excel.cellType() == ColumnType.TEXT || excel.cellType() == ColumnType.STRING) { return new DataFormatter().formatCellValue(cell); // 转为字符串 } } return cell.getNumericCellValue(); } case BOOLEAN: return cell.getBooleanCellValue(); default: return null; } } /** * 反向解析值 男=0,女=1,未知=2 * * @param propertyValue 参数值 * @param converterExp 翻译注解 * @param separator 分隔符 * @return 解析后值 */ public static String reverseByExp(String propertyValue, String converterExp, String separator) { StringBuilder propertyString = new StringBuilder(); String[] convertSource = converterExp.split(","); for (String item : convertSource) { String[] itemArray = item.split("="); if (StringUtils.containsAny(separator, propertyValue)) { for (String value : propertyValue.split(separator)) { if (itemArray[1].equals(value)) { propertyString.append(itemArray[0] + separator); break; } } } else { if (itemArray[1].equals(propertyValue)) { return itemArray[0]; } } } return StringUtils.stripEnd(propertyString.toString(), separator); } /** * 反向解析值字典值 * * @param dictLabel 字典标签 * @param dictType 字典类型 * @param separator 分隔符 * @return 字典值 */ public static String reverseDictByExp(String dictLabel, String dictType, String separator) { return DictUtils.getDictValue(dictType, dictLabel, separator); } }