当前位置：首页 > news >正文

easyExcel导入多sheet的Excel，存在合并单元格、列不固定的情况

news 2025/6/27 15:49:26

1.官网

easyExcel官网

2.Excel样式

在这里插入图片描述

3.代码

@Slf4j
public class DynamicImportListener implements ReadListener<Map<Integer, String>> {/*** 从哪一行开始读数据*/private final int headRowNumber;/*** 公司信息列*/private final int companyInfoNumber;/*** 数据列*/private final int headNumber;private final Map<Integer, Map<Integer, String>> rawRowsMap = new HashMap<>();private final List<CellExtra> extraMergeInfoList = new ArrayList<>();private final Map<Integer, String> headerMap = new LinkedHashMap<>();private final NavigableMap<Integer, String> companyInfoMap = new TreeMap<>();public DynamicImportListener(int headRowNumber, int companyInfoNumber, int headNumber) {this.headRowNumber = headRowNumber;this.companyInfoNumber = companyInfoNumber;this.headNumber = headNumber;}@Overridepublic void invoke(Map<Integer, String> rowMap, AnalysisContext context) {int rowIndex = context.readRowHolder().getRowIndex();if (rowIndex == companyInfoNumber) {String company = rowMap.get(0);if (StrUtil.isNotBlank(company)) {companyInfoMap.put(rowIndex, company.trim());}} else if (rowIndex == headNumber) {for (Map.Entry<Integer, String> e : rowMap.entrySet()) {String v = e.getValue();if (StrUtil.isNotBlank(v)) {headerMap.put(e.getKey(), v.trim());}}}else {rawRowsMap.put(rowIndex, rowMap);}}@Overridepublic void extra(CellExtra extra, AnalysisContext context) {if (extra.getType() == CellExtraTypeEnum.MERGE&& extra.getFirstRowIndex() >= headRowNumber - 1) {extraMergeInfoList.add(extra);}}@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {log.info("读取完毕：数据总行 {}，表头列 {}，合并单元格 {} 条",rawRowsMap.size(), headerMap.size(), extraMergeInfoList.size());}/*** 获取首条公司信息*/public String getCompanyInfo() {return companyInfoMap.isEmpty() ? null : companyInfoMap.firstEntry().getValue();}/*** 获取所有合并单元格元数据*/public List<CellExtra> getMergedRegions() {return Collections.unmodifiableList(extraMergeInfoList);}/*** 回填合并单元格数据*/public void fillMergedCells() {if (extraMergeInfoList.isEmpty()) return;for (CellExtra extra : extraMergeInfoList) {int r1 = extra.getFirstRowIndex();int r2 = extra.getLastRowIndex();int c1 = extra.getFirstColumnIndex();String init = rawRowsMap.get(r1).get(c1);for (int rr = r1; rr <= r2; rr++) {Map<Integer, String> row = rawRowsMap.get(rr);if (row == null) continue;for (int cc = c1; cc <= extra.getLastColumnIndex(); cc++) {row.put(cc, init);}}}}/*** 构建 VO 列表，固定字段 + extraFields*/public <T> List<T> buildVoList(Class<T> voClass) {List<T> result = new ArrayList<>();int headerIdx = headNumber;int maxRow = rawRowsMap.keySet().stream().max(Integer::compareTo).orElse(headerIdx);for (int idx = headerIdx + 1; idx <= maxRow; idx++) {Map<Integer, String> rowMap = rawRowsMap.get(idx);if (rowMap == null) continue;try {T vo = voClass.getDeclaredConstructor().newInstance();// 填充列for (Map.Entry<Integer, String> head : headerMap.entrySet()) {String headerName = head.getValue();String cellVal = rowMap.get(head.getKey());String value = (cellVal == null) ? "" : cellVal.trim();boolean matched = false;for (Field f : voClass.getDeclaredFields()) {ExcelProperty prop = f.getAnnotation(ExcelProperty.class);if (prop != null && Arrays.asList(prop.value()).contains(headerName)) {f.setAccessible(true);f.set(vo, convertType(f.getType(), value));matched = true;break;}}if (!matched) {Method m = voClass.getMethod("getExtraFields");@SuppressWarnings("unchecked")Map<String, String> extra = (Map<String, String>) m.invoke(vo);extra.put(headerName, value);}}result.add(vo);} catch (Exception e) {log.error("行 {} 构建 VO 失败: {}", idx + 1, e.getMessage());}}return result;}/*** @param targetType 目标类型* @param text 文本* @return java.lang.Object* @description 数据类型转换* @author zhaohuaqing* @date 2025/6/26 11:42*/private Object convertType(Class<?> targetType, String text) {if (text == null) {return null;}String trimmed = text.trim();// 字符串if (targetType == String.class) {return trimmed;}// 原生数字类型if (targetType == Integer.class || targetType == int.class) {return Integer.valueOf(trimmed);}if (targetType == Long.class    || targetType == long.class) {return Long.valueOf(trimmed);}if (targetType == Double.class  || targetType == double.class) {return Double.valueOf(trimmed);}if (targetType == Float.class   || targetType == float.class) {return Float.valueOf(trimmed);}if (targetType == Short.class   || targetType == short.class) {return Short.valueOf(trimmed);}if (targetType == Byte.class    || targetType == byte.class) {return Byte.valueOf(trimmed);}// BigDecimalif (targetType == BigDecimal.class) {return new BigDecimal(trimmed);}// 布尔if (targetType == Boolean.class || targetType == boolean.class) {// 支持 "true"/"false"，也支持 "1"/"0"if ("1".equals(trimmed) || "0".equals(trimmed)) {return "1".equals(trimmed);}return Boolean.valueOf(trimmed);}// Java 8 日期时间if (targetType == LocalDate.class) {// 默认 ISO 格式，或自定义return LocalDate.parse(trimmed, DateTimeFormatter.ISO_LOCAL_DATE);}if (targetType == LocalTime.class) {return LocalTime.parse(trimmed, DateTimeFormatter.ISO_LOCAL_TIME);}if (targetType == LocalDateTime.class) {return LocalDateTime.parse(trimmed, DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));}if (targetType == OffsetDateTime.class) {return OffsetDateTime.parse(trimmed, DateTimeFormatter.ISO_OFFSET_DATE_TIME);}if (targetType == ZonedDateTime.class) {return ZonedDateTime.parse(trimmed, DateTimeFormatter.ISO_ZONED_DATE_TIME);}// 旧版 java.util.Dateif (targetType == java.util.Date.class) {try {// 你可以根据 Excel 导出格式，调整 SimpleDateFormatreturn new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(trimmed);} catch (ParseException e) {throw new RuntimeException("日期解析失败: " + trimmed, e);}}return trimmed;}
}

如何使用

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = false) // 设置 chain = false，避免excel导入有问题
public class SopExtractMdsaVO {@ExcelProperty(value = "一级xxx")private String firstLevel;@ExcelProperty(value = "二级xxx")private String secondLevel;@ExcelProperty(value = "三级xxx")private String thirdLevel;@ExcelProperty(value = "xxx")private String formula;@ExcelProperty(value = "xxx")private String factor;@ExcelProperty(value = "xxx")private String referenceValue;@ExcelProperty(value = "xxx")private String element;@ExcelProperty(value = "xxx")private String scheme;@ExcelProperty(value = "超链接1")private String hyperlink1;@ExcelProperty(value = "超链接2")private String hyperlink2;@ExcelProperty(value = "超链接3")private String hyperlink3;/*** 动态列：所有未在 VO 明确定义的列*/private Map<String, String> extraFields = new LinkedHashMap<>();/*** 管控方案IDs*/private String controlPlanIds;/*** 输出物IDs*/private String outputMaterialIds;/*** @return 非空的 hyperlink 列表*/public List<String> nonBlankHyperlinks() {List<String> list = new ArrayList<>(10);if (StrUtil.isNotBlank(hyperlink1)) list.add(hyperlink1);if (StrUtil.isNotBlank(hyperlink2)) list.add(hyperlink2);if (StrUtil.isNotBlank(hyperlink3)) list.add(hyperlink3);return list;}
}

        InputStream inputStream = file.getInputStream();int headRowNumber = 0;  // 表头在 Excel 的第 2 行（从 1 开始计）DynamicImportListener listener = new DynamicImportListener(headRowNumber, 0, 1);// 1) 读数据、收集表头 & 合并单元格 & 公司信息EasyExcel.read(inputStream, listener).extraRead(CellExtraTypeEnum.MERGE).sheet("TEST").headRowNumber(headRowNumber).doRead();// 2) 外部拿公司信息String company = listener.getCompanyInfo();// 3) 回填合并单元格listener.fillMergedCells();// 4) 构建 VO 列表（包含固定字段 + extraFields）List<SopExtractMdsaVO> rows = listener.buildVoList(SopExtractMdsaVO.class);