EasyExcel 公式计算大全

EasyExcel 是基于 Apache POI 的封装，主要专注于简化 Excel 的读写操作，对于公式计算的支持相对有限。以下是 EasyExcel 中处理公式计算的全面指南：

1. 基本公式写入

1.1 写入简单公式

@Data
public class FormulaData {
    @ExcelProperty("数值1")
    private Double value1;
    
    @ExcelProperty("数值2")
    private Double value2;
    
    @ExcelProperty("求和")
    private String sumFormula;
    
    @ExcelProperty("平均值")
    private String avgFormula;
}

// 写入公式
List<FormulaData> list = new ArrayList<>();
FormulaData data = new FormulaData();
data.setValue1(10.0);
data.setValue2(20.0);
data.setSumFormula("SUM(A2,B2)");  // 引用当前行的单元格
data.setAvgFormula("AVERAGE(A2,B2)");
list.add(data);

EasyExcel.write("formula_example.xlsx", FormulaData.class)
         .sheet("公式示例")
         .doWrite(list);

1.2 使用绝对引用

data.setSumFormula("SUM($A$2:$B$2)");  // 绝对引用

2. 读取包含公式的Excel

2.1 基本读取

// 监听器实现
public class FormulaDataListener extends AnalysisEventListener<FormulaData> {
    @Override
    public void invoke(FormulaData data, AnalysisContext context) {
        // 这里获取的是公式计算后的值
        System.out.println("求和结果: " + data.getSumFormula());
    }
    
    @Override
    public void doAfterAllAnalysed(ActionContext context) {}
}

// 读取文件
EasyExcel.read("formula_example.xlsx", FormulaData.class, new FormulaDataListener())
         .sheet()
         .doRead();

2.2 获取公式本身（而非计算结果）

public class FormulaReadListener extends AnalysisEventListener<Map<Integer, String>> {
    @Override
    public void invoke(Map<Integer, String> data, AnalysisContext context) {
        // 获取底层Cell
        ReadCellData<?> cellData = (ReadCellData<?>) data.get(2); // 第三列
        if (cellData.getFormulaData() != null) {
            System.out.println("公式内容: " + cellData.getFormulaData());
        }
    }
}

EasyExcel.read("formula_example.xlsx", new FormulaReadListener())
         .sheet()
         .doRead();

3. 动态公式设置

3.1 使用拦截器动态设置公式

public class FormulaWriteHandler implements CellWriteHandler {
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, 
            WriteTableHolder writeTableHolder, Cell cell, Head head, 
            Integer relativeRowIndex, Boolean isHead) {
        if (!isHead && head.getFieldName().equals("sumFormula")) {
            // 设置求和公式，如SUM(A2,B2)
            int rowNum = cell.getRowIndex() + 1; // Excel行号从1开始
            String formula = String.format("SUM(A%d,B%d)", rowNum, rowNum);
            cell.setCellFormula(formula);
        }
    }
}

// 使用
EasyExcel.write("dynamic_formula.xlsx", FormulaData.class)
         .registerWriteHandler(new FormulaWriteHandler())
         .sheet()
         .doWrite(dataList);

3.2 复杂公式示例

// IF函数示例
String ifFormula = "IF(A2>B2, \"A大于B\", IF(A2<B2, \"A小于B\", \"A等于B\"))";

// VLOOKUP函数示例
String vlookupFormula = "VLOOKUP(D2,$A$2:$B$10,2,FALSE)";

// 日期函数
String dateFormula = "TEXT(TODAY(),\"yyyy-mm-dd\")";

4. 公式计算控制

4.1 强制重新计算公式

// 写入后强制计算
ExcelWriter excelWriter = EasyExcel.write("output.xlsx", FormulaData.class).build();
WriteSheet writeSheet = EasyExcel.writerSheet("Sheet1").build();
excelWriter.write(dataList, writeSheet);

// 获取底层Workbook并强制重新计算
SXSSFWorkbook workbook = (SXSSFWorkbook) excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();

excelWriter.finish();

4.2 设置不计算公式（仅保留公式）

// 在读取时不计算公式
EasyExcel.read("input.xlsx", new FormulaReadListener())
         .ignoreEmptyRow(false)
         .autoTrim(true)
         .formulaResult(false)  // 不计算公式结果
         .sheet()
         .doRead();

5. 常见函数示例

5.1 数学函数

// 基本数学运算
String addFormula = "A2+B2";
String multiplyFormula = "PRODUCT(A2,B2)";
String roundFormula = "ROUND(A2,2)";

// 条件求和
String sumifFormula = "SUMIF(A2:A10,\">10\",B2:B10)";

5.2 文本函数

// 连接文本
String concatFormula = "CONCATENATE(A2,\"-\",B2)";

// 提取子串
String leftFormula = "LEFT(A2,3)";
String midFormula = "MID(A2,2,3)";

5.3 日期函数

// 计算日期差
String dateDiffFormula = "DATEDIF(A2,B2,\"d\")";

// 添加天数
String addDaysFormula = "A2+7";  // A2是日期单元格

6. 高级技巧

6.1 跨工作表公式

// 引用其他工作表的单元格
String crossSheetFormula = "SUM(Sheet2!A1:A10)";

// 引用其他工作簿（需要文件在同一目录）
String externalRefFormula = "'[OtherWorkbook.xlsx]Sheet1'!A1";

6.2 数组公式

// 数组公式需要用大括号包围
String arrayFormula = "{SUM(A2:A10*B2:B10)}";

// 写入时需要特殊处理
cell.setCellFormula("SUM(A2:A10*B2:B10)");
// 然后需要将单元格标记为数组公式区域
CellRangeAddress range = new CellRangeAddress(
    cell.getRowIndex(), cell.getRowIndex(),
    cell.getColumnIndex(), cell.getColumnIndex());
sheet.addMergedRegion(range);
sheet.setArrayFormula(cell.getCellFormula(), range);

6.3 命名范围公式

// 创建命名范围
Name namedRange = workbook.createName();
namedRange.setNameName("SalesData");
namedRange.setRefersToFormula("Sheet1!$A$2:$A$10");

// 使用命名范围
String namedRangeFormula = "SUM(SalesData)";

// 1. 定义数据类
@Data
public class FinancialData {
    @ExcelProperty("月份")
    private String month;
    
    @ExcelProperty("收入")
    private Double income;
    
    @ExcelProperty("支出")
    private Double expense;
    
    @ExcelProperty("利润")
    private String profitFormula;
    
    @ExcelProperty("利润率")
    private String marginFormula;
}

// 2. 公式写入处理器
public class FinancialFormulaHandler implements CellWriteHandler {
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, 
            WriteTableHolder writeTableHolder, Cell cell, Head head, 
            Integer relativeRowIndex, Boolean isHead) {
        if (!isHead) {
            int rowNum = cell.getRowIndex() + 1;
            String column = head.getFieldName();
            
            if ("profitFormula".equals(column)) {
                cell.setCellFormula(String.format("B%d-C%d", rowNum, rowNum));
            } else if ("marginFormula".equals(column)) {
                cell.setCellFormula(String.format("(B%d-C%d)/B%d", rowNum, rowNum, rowNum));
                // 设置百分比格式
                CellStyle style = writeSheetHolder.getSheet().getWorkbook().createCellStyle();
                style.setDataFormat((short)0xa);  // 百分比格式
                cell.setCellStyle(style);
            }
        }
    }
}

// 3. 使用示例
public void exportFinancialReport() {
    List<FinancialData> dataList = new ArrayList<>();
    // 添加数据...
    
    ExcelWriter excelWriter = EasyExcel.write("financial_report.xlsx", FinancialData.class)
                                     .registerWriteHandler(new FinancialFormulaHandler())
                                     .build();
    
    WriteSheet writeSheet = EasyExcel.writerSheet("财务报告").build();
    excelWriter.write(dataList, writeSheet);
    
    // 强制计算公式
    SXSSFWorkbook workbook = (SXSSFWorkbook) excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
    workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
    
    excelWriter.finish();
}