Apache POI生成报表跨机器数据丢失及自定义公式求值问题求助

我正在开发一个基于Java的报表生成项目，使用的xlsx文件包含Report和DB两个工作表：

• DB工作表使用XLA扩展的自定义公式=AimHistValue(P1, P2, P3, P4)从数据库取值，预期结果为1.92
• Report工作表通过引用DB!E33关联数据

该软件在数据库所在主机运行正常，但将文件转移到其他机器后，xlsx文件内容丢失。尝试用Apache POI的FormulaEvaluator处理公式，但无法解析自定义的AimHistValue函数，求解决方案。

针对自定义XLA公式无法被POI解析的问题，有以下几种可行方案：

方案1：预计算公式结果（在数据库主机完成） #

既然在数据库所在主机能正常运行，可在该机器上先打开xlsx文件，让Excel自动计算出AimHistValue的结果并保存为值而非公式，再将文件转移到其他机器使用。如果需要Java自动化完成，可借助桌面Excel的COM接口（比如Jacob库），但这种方式依赖Windows环境和Excel安装。

方案2：自定义POI公式求值器 #

为POI实现AimHistValue的解析逻辑，让FormulaEvaluator能直接计算该公式：

1. 实现Function接口，编写函数的求值逻辑（直接调用数据库获取对应值）
2. 将自定义函数注册到POI的公式管理器中
3. 使用注册后的求值器处理公式

修改后的示例代码如下：

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.ss.util.CellValue;
import org.apache.poi.ss.formula.functions.Function;
import org.apache.poi.ss.formula.OperationEvaluationContext;
import org.apache.poi.ss.formula.eval.*;
import java.io.*;

// 自定义AimHistValue函数实现
public class AimHistValueFunction implements Function {
    @Override
    public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
        if (args.length != 4) {
            return ErrorEval.VALUE_INVALID;
        }
        
        try {
            // 解析四个参数，根据实际参数类型调整
            String p1 = getStringValue(args[0], ec);
            String p2 = getStringValue(args[1], ec);
            String p3 = getStringValue(args[2], ec);
            String p4 = getStringValue(args[3], ec);
            
            // 替换为你的数据库查询逻辑
            double result = queryDatabaseForAimHistValue(p1, p2, p3, p4);
            
            return new NumberEval(result);
        } catch (Exception e) {
            return ErrorEval.NA;
        }
    }
    
    private String getStringValue(ValueEval eval, OperationEvaluationContext ec) throws EvaluationException {
        if (eval instanceof RefEval) {
            RefEval ref = (RefEval) eval;
            ValueEval innerEval = ref.getInnerValueEval(ref.getFirstSheetIndex());
            if (innerEval instanceof StringEval) {
                return ((StringEval) innerEval).getStringValue();
            } else if (innerEval instanceof NumberEval) {
                return String.valueOf(((NumberEval) innerEval).getNumberValue());
            }
        } else if (eval instanceof StringEval) {
            return ((StringEval) eval).getStringValue();
        } else if (eval instanceof NumberEval) {
            return String.valueOf(((NumberEval) eval).getNumberValue());
        }
        throw new EvaluationException(ErrorEval.VALUE_INVALID);
    }
    
    // 替换为实际数据库查询方法
    private double queryDatabaseForAimHistValue(String p1, String p2, String p3, String p4) {
        // 示例返回预期值1.92
        return 1.92;
    }
}

// 自定义函数工具包
class CustomFunctionToolPack extends org.apache.poi.ss.formula.udf.DefaultUDFFinder {
    public CustomFunctionToolPack() {
        super(new String[]{"AIMHISTVALUE"}, new Function[]{new AimHistValueFunction()});
    }
}

// 修改后的公式处理方法
private void copyFormulasAsValues(XSSFWorkbook workbook, XSSFSheet reportSheet) {
    // 注册自定义函数
    workbook.addToolPack(new CustomFunctionToolPack());
    
    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

    for (Row row : reportSheet) {
        for (Cell cell : row) {
            if (cell != null && cell.getCellType() == CellType.FORMULA) {
                CellValue formulaResult = evaluator.evaluate(cell);
                if (formulaResult.getCellType() == CellType.NUMERIC) {
                    double value = formulaResult.getNumberValue();
                    cell.setCellValue(value);
                }
            }
        }
    }
}

// 使用示例
private void generateReport() {
    try {
        FileInputStream file = new FileInputStream("C:\\percorso\\del\\tuo\\file.xlsx");
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet reportSheet = workbook.getSheet("Report");

        copyFormulasAsValues(workbook, reportSheet);

        // 保存处理后的文件
        FileOutputStream outFile = new FileOutputStream("C:\\percorso\\del\\tuo\\processed_file.xlsx");
        workbook.write(outFile);
        outFile.close();
        workbook.close();
        file.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

方案3：直接读取DB工作表的缓存值 #

如果xlsx文件在数据库主机运行后，DB!E33已保存计算后的缓存值，可直接读取该值，无需重新计算公式：

private void copyCachedValues(XSSFWorkbook workbook, XSSFSheet reportSheet) {
    XSSFSheet dbSheet = workbook.getSheet("DB");
    Row dbRow = dbSheet.getRow(32); // E33对应行索引32（从0开始），列索引4（E列）
    Cell dbCell = dbRow.getCell(4);
    
    if (dbCell != null && dbCell.getCachedFormulaResultType() == CellType.NUMERIC) {
        double value = dbCell.getNumericCellValue();
        // 找到Report中引用DB!E33的单元格，设置值（示例为A1，需根据实际调整）
        Row reportRow = reportSheet.getRow(0);
        if (reportRow == null) reportRow = reportSheet.createRow(0);
        Cell reportCell = reportRow.getCell(0);
        if (reportCell == null) reportCell = reportRow.createCell(0);
        reportCell.setCellValue(value);
    }
}

• 方案2需要完全复现AimHistValue函数的数据库查询逻辑，确保参数解析和结果与原XLA一致
• 方案3仅适用于文件在数据库主机已完成公式计算并保存缓存值的情况

内容的提问来源于stack exchange，提问作者Andrea Viola