java - 如何在 SXSSFWorkbook 和 Apache POI 中使用 vlookup 或公式？

Question

我是 Java 开发人员。我正在尝试写一个大的xlsx文件 (Excel) 使用 SXSSFWorkbook Apache POI 库的类。

我没有选择写大文件，因为有超过 200000 行。
但我需要使用一些公式或使用外部表格进行 vlookup。当我使用:

cell.setCellFormula(formulasString);

但它不起作用，它返回 0 值。

Q- 如何在 SXSSFWorkbook 中使用公式？

我的代码 -

import java.io.FileOutputStream;
import junit.framework.Assert;
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.util.CellReference;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

public class Demo2 
{
    public static void main(String[] args) throws Throwable 
    {
          SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk

          FormulaEvaluator fEvaluator=wb.getCreationHelper().createFormulaEvaluator();

          Sheet sh = wb.createSheet();
          for(int rownum = 0; rownum < 100000; rownum++)
          {
               Row row = sh.createRow(rownum);
               for(int cellnum = 0; cellnum < 10; cellnum++)
               {
                  Cell cell = row.createCell(cellnum);
                  String address = new CellReference(cell).formatAsString();
                  cell.setCellValue(address);
               }

               Cell cell1 = row.createCell(12);
               cell1.setCellType(Cell.CELL_TYPE_FORMULA);
               String sF = "SUM(10,20,30)";
               cell1.setCellFormula(sF);
               fEvaluator.clearAllCachedResultValues();

               Cell cell2 = row.createCell(13);
               cell2.setCellValue("Test");
         }

         // Rows with rownum < 900 are flushed and not accessible
         for(int rownum = 0; rownum < 99900; rownum++)
         {
              Assert.assertNull(sh.getRow(rownum));
         }

         // ther last 100 rows are still in memory
         for(int rownum = 99900; rownum < 100000; rownum++)
         {
              Assert.assertNotNull(sh.getRow(rownum));
         }

         FileOutputStream out = new FileOutputStream("sxssf.xlsx");
         wb.write(out);
         out.close();

         // dispose of temporary files backing this workbook on disk
         wb.dispose();
         System.out.println("Done");
    }
}

如果可能的话，请给我建议。
谢谢。

Answer 1

根据您的问题以及评论中提供的信息，您的问题是您使用的 Apache POI 版本太旧。这就是为什么您在尝试评估 SXSSF 单元时遇到异常的原因。如detailed towards the end of the Formula Evaluation documentation ，对于 SXSSF 公式评估，您需要使用 3.13 beta 2 或更高版本。

还有一个潜在的问题 - 公式评估不仅需要包含公式的单元格，还需要它引用的任何其他单元格，以及它们引用的任何单元格。这可能会导致您出现问题，具体取决于您的 vlookup 尝试调用的区域范围。 vlookup 需要的所有单元格必须在当前窗口中可用，并且不刷新到磁盘。因此，您可能需要简化公式和/或增加窗口大小，以确保每个 vlookup 单元格可以找到它在评估时所依赖的所有单元格的值