FastExcel 与 EasyExcel 如何迁移？企业级数据处理实战指南

// FastExcel日期处理示例
@Override
public void onCellData(CellData cellData, CellReadState state) {
    if (state.getColumnIndex() == ) { // 假设日期列在第3列
        LocalDate date = LocalDate.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy/MM/dd"));
        // 业务逻辑处理
    }
}

// EasyExcel日期处理示例
@ExcelProperty(value = "创建时间", index = )
@DateTimeFormat("yyyy/MM/dd")
private LocalDate createTime;

// FastExcel三级表头配置
List<List<String>> headers = Arrays.asList(
    Arrays.asList("费用数据", "", ""),
    Arrays.asList("差旅费", "办公费", "交通费"),
    Arrays.asList("金额", "报销人", "金额", "报销人", "金额", "报销人")
);
readerConfig.setHeader(headers);

// EasyExcel嵌套表头POJO
public class ExpenseData {
    @ExcelProperty(value = "费用数据", index = , colspan = )
    private String expenseData; // 此属性仅用于表头展示，无需实际赋值

    @ExcelProperty(value = "差旅费", index = )
    public static class TravelExpense {
        @ExcelProperty("金额")
        private Double amount;
        @ExcelProperty("报销人")
        private String applicant;
    }

    // 类似定义办公费、交通费的嵌套类
}

// EasyExcel大文件流式处理
EasyExcel.read(fileName, DataModel.class, new AnalysisEventListener<DataModel>() {
    @Override
    public void invoke(DataModel data, AnalysisContext context) {
        // 逐行处理逻辑，与FastExcel的onCellData类似
    }
}).useStreamingReader().sheet().doRead();

// FastExcel模板填充示例
Map<String, Object> data = new HashMap<>();
data.put("totalSales", 123456.78);
data.put("productList", productList); // 列表数据填充
TemplateEngine engine = new TemplateEngine();
engine.process(templateFile, data, outputStream);

// EasyExcel注解填充示例
@Data
public class ReportData {
    @ExcelProperty("总销售额")
    private Double totalSales;

    @ExcelProperty({"产品列表", "产品名称"})
    private String productName;

    @ExcelProperty({"产品列表", "销量"})
    private Integer salesVolume;
}

// 写入逻辑
List<ReportData> dataList = new ArrayList<>();
dataList.add(new ReportData(...)); // 填充总销售额数据
dataList.addAll(productList); // 填充产品列表数据
EasyExcel.write(outputStream, ReportData.class).sheet("月度报表").doWrite(dataList);

// EasyExcel设置打开密码示例
try (ExcelWriter excelWriter = EasyExcel.write(outputStream, DataModel.class).build()) {
    Sheet sheet = excelWriter.getSheetList().get();
    // 获取POI的Workbook对象
    Workbook workbook = excelWriter.getWriteSheetHolder().getWorkbook();
    // 设置打开密码（需引入poi-ooxml依赖）
    if (workbook instanceof XSSFWorkbook) {
        XSSFWorkbook xssfWorkbook = (XSSFWorkbook) workbook;
        xssfWorkbook.protectWorkbook("password".getBytes());
    }
    // 后续写入逻辑
}

// 设置每500行写入一次磁盘
EasyExcel.write(fileName, DataModel.class)
    .registerWriteHandler(new WriteBatchSizeHandler())
    .sheet()
    .doWrite(dataList);

// 多线程处理多个Sheet示例
ExecutorService executor = Executors.newFixedThreadPool();
List<SheetData> sheetDataList = ...; // 各Sheet的数据列表

sheetDataList.forEach(sheetData -> {
    executor.submit(() -> {
        try (ExcelWriter excelWriter = EasyExcel.write(outputStream).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetData.getSheetName()).build();
            excelWriter.write(sheetData.getDataList(), writeSheet);
        }
    });
});
executor.shutdown();
executor.awaitTermination(, TimeUnit.HOURS);

// 强制刷新公式计算结果
try (ExcelWriter excelWriter = EasyExcel.write(fileName).build()) {
    Sheet sheet = excelWriter.getSheetList().get();
    Workbook workbook = excelWriter.getWriteSheetHolder().getWorkbook();
    if (workbook instanceof XSSFWorkbook) {
        XSSFSheet xssfSheet = (XSSFSheet) sheet.getSheet();
        xssfSheet.setForceFormulaRecalculation(true);
    }
    // 写入数据逻辑
}

// 启用磁盘缓存（需提前创建临时目录）
EasyExcel.read(fileName, DataModel.class, new AnalysisEventListener<>())
    .withTempDir(new File("temp_dir"))
    .sheet()
    .doRead();