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

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

由于有超过200000行，我没有选择写大文件 . 但我需要使用一些公式或vlookup与外部表 . 我用的时候：

cell.setCellFormula(formulasString);

但它没有工作，它返回0值 .

Q- How to use formulas with 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");

}

}

请尽可能给我建议 . 谢谢 .