Apache POI字符串公式错误

Question

我使用Apache POI创建带有字符串公式的Excel文件，结果文件中出现了#VALUE!错误。

源代码如下：

public static void main(String[] args) throws IOException {
    Workbook wb = new HSSFWorkbook();
    FormulaEvaluator ev = wb.getCreationHelper().createFormulaEvaluator();
    Sheet ws = wb.createSheet();
    Row row = ws.createRow(0);
    Cell cell;
    cell = row.createCell(0);
    cell.setCellValue("abc");

    cell = row.createCell(1);
    printCellInfo(row.getCell(1),"before formula");
    cell.setCellFormula("IF(A1<>\"\",MID(A1,1,2),\" \")");

    printCellInfo(row.getCell(1), "after formula");
    ev.evaluateAll();
    printCellInfo(row.getCell(1), "after recalc");

    OutputStream os = new FileOutputStream("xx.xls");
    wb.write(os);

}

(printCellInfo 是下面我要讲的信息方法) 错误单元格是B1。Excel提示“值单元格中使用了不正确的数据类型”。 但是，如果打开此单元格进行编辑（使用F2）并按Enter键，则公式将正确计算！

现在说说单元格信息。辅助方法是

static void printCellInfo(Cell cell, String infoText) {
    System.out.println("Cell "+CellReference.convertNumToColString(cell.getColumnIndex())+cell.getRowIndex()+" "+infoText);
    int ct = cell.getCellType();
    System.out.println("  Cell type "+ct);
    if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        ct = cell.getCachedFormulaResultType();
        System.out.println("  Cached type "+ct);
    }
    switch (ct) {
        case Cell.CELL_TYPE_NUMERIC: 
            System.out.println("  Number <"+cell.getNumericCellValue()+">");
            break;
        case Cell.CELL_TYPE_STRING:
            System.out.println(" String <"+cell.getStringCellValue()+">");
    }
    System.out.println("============================");
}

main 执行的输出结果是：

Cell B0 before formula
  Cell type 3
============================
Cell B0 after formula
  Cell type 2
  Cached type 0
  Number <0.0>
============================
Cell B0 after recalc
  Cell type 2
  Cached type 1
 String <ab>
============================

所以B0一开始是空的，在公式插入后变成数字。为什么POI这样做，也许问题就从这里开始了？不幸的是，在重新计算后，POI中的正确数据类型在Excel中并没有变得正确。

顺便说一下，从POI设置的公式IF(A1<>"","A","B")或MID(A1,1,2)计算正确。并且，从POI设置的B1=MID(A1,1,2)和C1=IF(A1<>"",B1," ")也被正确计算。

有什么想法吗？我在互联网上只找到了一个主题https://openclassrooms.com/forum/sujet/apache-poi-formula-valeur，其中有一些想法，但没有可行的解决方案...

cell = row.getCell(1);
cell.setCellValue(cell.getStringCellValue());

这也没有什么帮助。

- Kotodid

---

请问您在使用哪个版本的Apache POI？另外，您能否提供您正在使用的导入部分，例如您是在使用poi.hssf还是poi.ss来处理CellReference？ - William Kinaan

import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.CellReference; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.FormulaEvaluator; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; - Kotodid

糟糕！没有换行... CellReference -不重要，我只使用数字转字符串列表示来显示。 - Kotodid

当然可以！谢谢。我将公式更改为更简单的形式，不会改变输出结果。 - Kotodid

请不要更改您的代码，我正在检查它。 - William Kinaan

Answer 1

我在我的电脑上尝试了你的代码，它运行得很好。

我正在使用 Excel for mac 2013 上的 beta POI 3.13。

你的 Excel 表格也正常工作（在你通过聊天给我之后）。

所以问题是你的 POI 版本和你的 Excel 2010 库之间的兼容性不正确。

这也是你的表格。

- William Kinaan

Answer 2

最近我也遇到了同样的问题。在保存工作簿之前，我使用以下代码解决了这个问题：

myWorkbook.getCreationHelper().createFormulaEvaluator().evaluateAll();

- Q.Dherb