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

Question

我是Java开发者。我正试图使用Apache POI库的SXSFWorkbook类编写一个大的xlsx文件（Excel）。

我没有选择写大文件，因为有超过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

wb.SetForceFormulaRecomulation（true）；

在执行wb之前设置此选项。写（出）温u打开文件i excel。excel重新计算，u得到计算结果

Answer 2

不幸的是，SXSSFWorkbook不支持版本3.12中的公式。我发现这在代码中也是正确的，但是，我没有看到任何关于这一点的官方POI文档。

公式计算的POI留档有一些示例代码，用于在单元格创建后计算公式。它说它适用于XSSFWorkbook和HSSFWorkbook实现，但没有提到SXSSFWorkbook。

我试图在示例代码中使用SXSSFWorkbook，但收到了一个异常。从例外情况来看，很明显，尽管在POI文档中没有明确说明，但SXSFWWorkbook无法对公式进行评估。

Unexpected type of cell: class org.apache.poi.xssf.streaming.SXSSFCell. Only XSSFCells can be evaluated.  

Answer 3

根据您的问题和评论中提供的信息，您的问题是您使用的Apache POI版本太旧。这就是为什么在评估SXSSF单元时会出现异常。如公式评估文档末尾所述，对于SXSSF公式评估，您需要使用3.13 beta 2或更高版本。

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