EasyExcel处理Excel中的复杂公式：从原理到实战-优快云博客

EasyExcel处理Excel中的复杂公式：从原理到实战

【免费下载链接】easyexcel 快速、简洁、解决大文件内存溢出的java处理Excel工具项目地址: https://gitcode.com/gh_mirrors/ea/easyexcel

引言：Excel公式处理的痛点与解决方案

你是否在Java开发中遇到过Excel公式处理的难题？当面对复杂的计算公式、动态数据引用或跨表计算时，传统的POI库往往显得力不从心，不仅代码冗长，还容易出现内存溢出。EasyExcel作为一款快速、简洁、解决大文件内存溢出的Java处理Excel工具，提供了高效的公式处理能力。本文将深入探讨EasyExcel如何处理Excel中的复杂公式，从原理到实战，帮助你轻松应对各类公式场景。

读完本文，你将能够：

理解EasyExcel公式处理的底层原理
掌握使用EasyExcel读写包含公式的Excel文件
解决复杂公式计算、动态引用等常见问题
优化公式处理性能，避免内存溢出

一、EasyExcel公式处理的核心原理

1.1 公式数据模型

EasyExcel通过FormulaData类来表示Excel中的公式数据，该类包含了公式的表达式和计算结果。

@Getter
@Setter
@EqualsAndHashCode
public class FormulaData {
    /**
     * 公式表达式
     */
    private String formulaValue;
    
    @Override
    public FormulaData clone() {
        FormulaData formulaData = new FormulaData();
        formulaData.setFormulaValue(getFormulaValue());
        return formulaData;
    }
}

1.2 公式解析与写入流程

EasyExcel处理公式的流程可以分为读取和写入两个阶段：

mermaid

读取流程：

解析Excel文件时，识别公式单元格
创建FormulaData对象，存储公式表达式
将FormulaData关联到CellData对象
通过监听器将包含公式的数据传递给应用层

写入流程：

应用层创建FormulaData对象并设置公式表达式
将FormulaData关联到CellData对象
EasyExcel将公式写入Excel文件
Excel客户端打开文件时自动计算公式结果

1.3 公式处理的核心组件

EasyExcel中处理公式的核心组件包括：

组件类名	作用
FormulaData	存储公式数据的模型类
CellFormulaTagHandler	解析XLSX格式公式的处理器
FormulaRecordHandler	解析XLS格式公式的处理器
AbstractExcelWriteExecutor	写入公式的执行器

二、读取包含公式的Excel文件

2.1 基本读取方法

使用EasyExcel读取包含公式的Excel文件非常简单，只需要通过监听器获取单元格数据即可：

public class FormulaDataListener extends AnalysisEventListener<Map<Integer, String>> {
    
    @Override
    public void invoke(Map<Integer, String> data, AnalysisContext context) {
        // 处理每行数据
        System.out.println("读取到数据: " + data);
    }
    
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        System.out.println("Excel读取完成");
    }
    
    @Override
    public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
        // 处理表头
    }
    
    @Override
    public void onException(Exception exception, AnalysisContext context) {
        // 处理异常
        exception.printStackTrace();
    }
}

// 使用示例
public class FormulaReadDemo {
    public static void main(String[] args) {
        String fileName = "包含公式的Excel文件.xlsx";
        
        EasyExcel.read(fileName, new FormulaDataListener())
            .sheet()
            .doRead();
    }
}

2.2 获取公式表达式和计算结果

要同时获取公式表达式和计算结果，需要使用ReadCellData：

public class FormulaDetailListener extends AnalysisEventListener<Map<Integer, ReadCellData<?>>> {
    
    @Override
    public void invoke(Map<Integer, ReadCellData<?>> data, AnalysisContext context) {
        for (Map.Entry<Integer, ReadCellData<?>> entry : data.entrySet()) {
            ReadCellData<?> cellData = entry.getValue();
            
            // 判断单元格是否包含公式
            if (cellData.getFormulaData() != null) {
                String formula = cellData.getFormulaData().getFormulaValue();
                Object value = cellData.getValue();
                
                System.out.println("单元格" + entry.getKey() + 
                                   "公式: " + formula + 
                                   ", 计算结果: " + value);
            }
        }
    }
    
    // 其他重写方法...
}

2.3 读取指定工作表的公式

如果需要读取指定工作表的公式，可以通过sheet()方法指定工作表索引或名称：

String fileName = "包含公式的Excel文件.xlsx";

EasyExcel.read(fileName, new FormulaDetailListener())
    .sheet("销售数据") // 指定工作表名称
    // .sheet(0) // 或者指定工作表索引
    .doRead();

三、写入包含公式的Excel文件

3.1 基本写入方法

使用EasyExcel写入公式非常简单，只需要创建WriteCellData对象并设置公式即可：

public class FormulaWriteDemo {
    public static void main(String[] args) {
        String fileName = "写入公式示例.xlsx";
        
        // 准备数据
        List<List<WriteCellData<?>>> data = new ArrayList<>();
        
        // 第一行：表头
        List<WriteCellData<?>> head = new ArrayList<>();
        head.add(createStringCell("数量A"));
        head.add(createStringCell("数量B"));
        head.add(createStringCell("总和"));
        data.add(head);
        
        // 第二行：数据
        List<WriteCellData<?>> row1 = new ArrayList<>();
        row1.add(createNumberCell(10));
        row1.add(createNumberCell(20));
        row1.add(createFormulaCell("A2+B2")); // 添加公式
        data.add(row1);
        
        // 第三行：数据
        List<WriteCellData<?>> row2 = new ArrayList<>();
        row2.add(createNumberCell(30));
        row2.add(createNumberCell(40));
        row2.add(createFormulaCell("A3+B3")); // 添加公式
        data.add(row2);
        
        // 写入Excel
        EasyExcel.write(fileName)
            .head(head)
            .sheet("公式示例")
            .doWrite(data);
    }
    
    // 创建字符串单元格
    private static WriteCellData<String> createStringCell(String value) {
        WriteCellData<String> cellData = new WriteCellData<>(value);
        return cellData;
    }
    
    // 创建数字单元格
    private static WriteCellData<Number> createNumberCell(Number value) {
        WriteCellData<Number> cellData = new WriteCellData<>(value);
        return cellData;
    }
    
    // 创建公式单元格
    private static WriteCellData<String> createFormulaCell(String formula) {
        WriteCellData<String> cellData = new WriteCellData<>();
        FormulaData formulaData = new FormulaData();
        formulaData.setFormulaValue(formula);
        cellData.setFormulaData(formulaData);
        return cellData;
    }
}

3.2 使用对象模型写入公式

除了直接操作WriteCellData，还可以通过对象模型来写入公式：

// 定义数据模型
@Data
public class FormulaDemoData {
    @ExcelProperty("数量A")
    private Integer quantityA;
    
    @ExcelProperty("数量B")
    private Integer quantityB;
    
    @ExcelProperty("总和")
    private WriteCellData<String> sum;
}

// 写入示例
public class ObjectModelFormulaWriteDemo {
    public static void main(String[] args) {
        String fileName = "对象模型公式示例.xlsx";
        
        // 准备数据
        List<FormulaDemoData> data = new ArrayList<>();
        
        FormulaDemoData data1 = new FormulaDemoData();
        data1.setQuantityA(10);
        data1.setQuantityB(20);
        data1.setSum(createFormulaCell("A2+B2"));
        data.add(data1);
        
        FormulaDemoData data2 = new FormulaDemoData();
        data2.setQuantityA(30);
        data2.setQuantityB(40);
        data2.setSum(createFormulaCell("A3+B3"));
        data.add(data2);
        
        // 写入Excel
        EasyExcel.write(fileName, FormulaDemoData.class)
            .sheet("对象模型公式")
            .doWrite(data);
    }
    
    // 创建公式单元格
    private static WriteCellData<String> createFormulaCell(String formula) {
        WriteCellData<String> cellData = new WriteCellData<>();
        FormulaData formulaData = new FormulaData();
        formulaData.setFormulaValue(formula);
        cellData.setFormulaData(formulaData);
        return cellData;
    }
}

四、处理复杂公式场景

4.1 跨工作表引用公式

EasyExcel支持写入跨工作表引用的公式，只需按照Excel的公式语法编写即可：

// 创建跨工作表引用的公式
WriteCellData<String> createCrossSheetFormulaCell() {
    WriteCellData<String> cellData = new WriteCellData<>();
    FormulaData formulaData = new FormulaData();
    // 引用"数据"工作表中的A1到A10单元格的总和
    formulaData.setFormulaValue("SUM(数据!A1:A10)");
    cellData.setFormulaData(formulaData);
    return cellData;
}

4.2 动态生成公式

在实际应用中，经常需要根据数据动态生成公式。以下是一个动态生成求和公式的示例：

// 动态生成求和公式
public List<WriteCellData<?>> generateRowData(List<Number> values, int rowNum) {
    List<WriteCellData<?>> rowData = new ArrayList<>();
    
    // 添加数值单元格
    for (Number value : values) {
        rowData.add(createNumberCell(value));
    }
    
    // 生成求和公式，如 "A1+B1+C1"
    StringBuilder formula = new StringBuilder("SUM(");
    for (int i = 0; i < values.size(); i++) {
        if (i > 0) {
            formula.append(",");
        }
        // 列字母 = A, B, C, ...
        char column = (char) ('A' + i);
        formula.append(column).append(rowNum);
    }
    formula.append(")");
    
    // 添加公式单元格
    rowData.add(createFormulaCell(formula.toString()));
    
    return rowData;
}

4.3 处理数组公式

EasyExcel也支持写入数组公式，只需在公式前添加{和}：

// 创建数组公式
private static WriteCellData<String> createArrayFormulaCell() {
    WriteCellData<String> cellData = new WriteCellData<>();
    FormulaData formulaData = new FormulaData();
    // 数组公式示例：计算A1:A10和B1:B10的乘积之和
    formulaData.setFormulaValue("{=SUM(A1:A10*B1:B10)}");
    cellData.setFormulaData(formulaData);
    return cellData;
}

五、高级应用与性能优化

5.1 大批量数据的公式处理

当处理大批量数据并包含公式时，建议使用EasyExcel的分批写入功能，以避免内存溢出：

public class BigDataFormulaDemo {
    public static void main(String[] args) {
        String fileName = "大批量数据公式示例.xlsx";
        
        // 准备数据
        int totalRows = 100000;
        int batchSize = 1000;
        
        try (ExcelWriter excelWriter = EasyExcel.write(fileName).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet("大批量数据").build();
            
            // 写入表头
            List<List<String>> head = new ArrayList<>();
            List<String> headRow = new ArrayList<>();
            headRow.add("数值");
            headRow.add("平方值");
            head.add(headRow);
            excelWriter.write(head, writeSheet);
            
            // 分批写入数据
            for (int i = 0; i < totalRows; i += batchSize) {
                int end = Math.min(i + batchSize, totalRows);
                List<List<WriteCellData<?>>> dataBatch = new ArrayList<>();
                
                for (int j = i; j < end; j++) {
                    List<WriteCellData<?>> rowData = new ArrayList<>();
                    // 当前行号（考虑表头行）
                    int rowNum = j + 2;
                    
                    // 添加数值单元格
                    rowData.add(createNumberCell(j + 1));
                    
                    // 添加平方公式单元格
                    WriteCellData<String> formulaCell = createFormulaCell("A" + rowNum + "*A" + rowNum);
                    rowData.add(formulaCell);
                    
                    dataBatch.add(rowData);
                }
                
                // 写入批次数据
                excelWriter.write(dataBatch, writeSheet);
            }
        }
    }
    
    // 创建数字单元格
    private static WriteCellData<Number> createNumberCell(Number value) {
        WriteCellData<Number> cellData = new WriteCellData<>(value);
        return cellData;
    }
    
    // 创建公式单元格
    private static WriteCellData<String> createFormulaCell(String formula) {
        WriteCellData<String> cellData = new WriteCellData<>();
        FormulaData formulaData = new FormulaData();
        formulaData.setFormulaValue(formula);
        cellData.setFormulaData(formulaData);
        return cellData;
    }
}

5.2 公式计算模式设置

虽然EasyExcel本身不计算公式结果，但可以通过设置Excel的计算模式来控制公式的计算行为：

// 设置Excel计算模式
public static void setCalculationMode(String fileName) {
    try (XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(fileName))) {
        XSSFSheet sheet = workbook.getSheetAt(0);
        
        // 设置计算模式为自动
        workbook.getCTWorkbook().getCalcPr().setCalcMode(STCalcMode.AUTO);
        // 设置迭代计算
        workbook.getCTWorkbook().getCalcPr().setIterateCount(100);
        workbook.getCTWorkbook().getCalcPr().setIterate(true);
        
        try (FileOutputStream out = new FileOutputStream(fileName)) {
            workbook.write(out);
        }
    } catch (IOException e) {
        e.printStackTrace();
    }
}

5.3 常见问题与解决方案

问题	解决方案
读取公式时只获取到公式表达式，没有计算结果	EasyExcel默认不计算公式结果，需要Excel客户端计算或使用POI的公式计算器
写入的公式无法正确计算	确保公式语法正确，单元格引用正确，Excel计算模式设置正确
大批量公式导致内存溢出	使用分批写入，避免一次性加载所有数据
跨表引用公式不生效	确保被引用的工作表名称正确，并且路径引用格式正确

六、总结与展望

6.1 本文要点总结

EasyExcel通过FormulaData类存储公式信息，支持读写Excel公式
读取公式时，通过监听器获取CellData中的FormulaData对象
写入公式时，创建FormulaData对象并关联到CellData
支持复杂公式场景，包括跨表引用、动态公式和数组公式
处理大批量数据时，建议使用分批写入以优化性能

6.2 进阶学习建议

深入学习EasyExcel的事件监听机制，自定义公式处理逻辑
结合POI的公式计算功能，实现服务端公式计算
研究EasyExcel的源码，特别是公式解析和写入的实现
探索公式与样式、数据验证等功能的结合应用

6.3 最佳实践

对于简单公式，直接使用EasyExcel的公式写入功能
对于复杂计算，考虑在Java代码中计算结果，再写入Excel
大批量数据处理时，始终使用分批写入模式
写入公式后，测试不同Excel版本的兼容性

通过本文的介绍，相信你已经掌握了EasyExcel处理Excel公式的核心技巧。无论是简单的求和公式还是复杂的跨表计算，EasyExcel都能提供高效、简洁的解决方案，帮助你轻松应对各类Excel公式处理场景。

如果觉得本文对你有帮助，请点赞、收藏并关注，后续将带来更多EasyExcel高级应用技巧！

【免费下载链接】easyexcel 快速、简洁、解决大文件内存溢出的java处理Excel工具项目地址: https://gitcode.com/gh_mirrors/ea/easyexcel

创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考