Java easyExcel實(shí)現(xiàn)導(dǎo)入多sheet的Excel

更新時(shí)間：2025年06月27日 11:15:50 作者：Uluoyu

這篇文章主要為大家詳細(xì)介紹了如何使用Java easyExcel實(shí)現(xiàn)導(dǎo)入多sheet的Excel,文中的示例代碼講解詳細(xì),感興趣的小伙伴可以了解一下

1.官網(wǎng)

easyExcel官網(wǎng)

2.Excel樣式

3.代碼

@Slf4j
public class DynamicImportListener implements ReadListener<Map<Integer, String>> {

    /**
     * 從哪一行開始讀數(shù)據(jù)
     */
    private final int headRowNumber;
    /**
     * 公司信息列
     */
    private final int companyInfoNumber;
    /**
     * 數(shù)據(jù)列
     */
    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;
    }

    @Override
    public 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);
        }
    }

    @Override
    public void extra(CellExtra extra, AnalysisContext context) {
        if (extra.getType() == CellExtraTypeEnum.MERGE
                && extra.getFirstRowIndex() >= headRowNumber - 1) {
            extraMergeInfoList.add(extra);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        log.info("讀取完畢：數(shù)據(jù)總行 {}，表頭列 {}，合并單元格 {} 條",
                rawRowsMap.size(), headerMap.size(), extraMergeInfoList.size());
    }

    /**
     * 獲取首條公司信息
     */
    public String getCompanyInfo() {
        return companyInfoMap.isEmpty() ? null : companyInfoMap.firstEntry().getValue();
    }

    /**
     * 獲取所有合并單元格元數(shù)據(jù)
     */
    public List<CellExtra> getMergedRegions() {
        return Collections.unmodifiableList(extraMergeInfoList);
    }

    /**
     * 回填合并單元格數(shù)據(jù)
     */
    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);
                }
            }
        }
    }

    /**
     * 構(gòu)建 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("行 {} 構(gòu)建 VO 失敗: {}", idx + 1, e.getMessage());
            }
        }
        return result;
    }

    /**
     * @param targetType 目標(biāo)類型
     * @param text 文本
     * @return java.lang.Object
     * @description 數(shù)據(jù)類型轉(zhuǎn)換
     * @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;
        }
        // 原生數(shù)字類型
        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);
        }
        // BigDecimal
        if (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 日期時(shí)間
        if (targetType == LocalDate.class) {
            // 默認(rèn) 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.Date
        if (targetType == java.util.Date.class) {
            try {
                // 你可以根據(jù) Excel 導(dǎo)出格式，調(diào)整 SimpleDateFormat
                return 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) // 設(shè)置 chain = false，避免excel導(dǎo)入有問題
public class SopExtractMdsaVO {

   
    @ExcelProperty(value = "一級(jí)xxx")
    private String firstLevel;

  
    @ExcelProperty(value = "二級(jí)xxx")
    private String secondLevel;


    @ExcelProperty(value = "三級(jí)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;

    /**
     * 動(dòng)態(tài)列：所有未在 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 開始計(jì)）
        DynamicImportListener listener = new DynamicImportListener(headRowNumber, 0, 1);

        // 1) 讀數(shù)據(jù)、收集表頭 & 合并單元格 & 公司信息
        EasyExcel.read(inputStream, listener)
                .extraRead(CellExtraTypeEnum.MERGE)
                .sheet("TEST")
                .headRowNumber(headRowNumber)
                .doRead();

        // 2) 外部拿公司信息
        String company = listener.getCompanyInfo();
        // 3) 回填合并單元格
        listener.fillMergedCells();
        // 4) 構(gòu)建 VO 列表（包含固定字段 + extraFields）
        List<SopExtractMdsaVO> rows = listener.buildVoList(SopExtractMdsaVO.class);

到此這篇關(guān)于Java easyExcel實(shí)現(xiàn)導(dǎo)入多sheet的Excel的文章就介紹到這了,更多相關(guān)Java easyExcel導(dǎo)入Excel內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: