最近有个项目,需要Excel来上传,其中涉及到Excel文件中公式的值,供应商采用了传统的做法,采用FormulaEvaluator来一个一个计算并获得公式值,非常之慢,我提出异议,供应商无法解决。

我不信,相信微软家的Excel产品,不至于只有这么愚笨的方式。

最终,我通过google(百度不行)找到了如下文章:https://www.baeldung.com/apache-poi-read-cell-value-formula

成功解决了性能问题

3. Fetch the Last Cached Value

Excel stores two objects for the cell when a formula calculates its value. One is the formula itself, and the second is the cached value. The cached value contains the last value evaluated by the formula.

So the idea here is we can fetch the last cached value and consider it as cell value. It may not always be true that the last cached value is the correct cell value. However, when we’re working with an Excel file that is saved, and there are no recent modifications to the file, then the last cached value should be the cell value.

Let’s see how to fetch the last cached value for a cell:

FileInputStream inputStream = new FileInputStream(new File("temp.xlsx"));
Workbook workbook = new XSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0); 
CellAddress cellAddress = new CellAddress("C2");
Row row = sheet.getRow(cellAddress.getRow());
Cell cell = row.getCell(cellAddress.getColumn()); 
if (cell.getCellType() == CellType.FORMULA) {    
  switch (cell.getCachedFormulaResultType()) {        
    case BOOLEAN:            
      System.out.println(cell.getBooleanCellValue());            
      break;        
    case NUMERIC:            
      System.out.println(cell.getNumericCellValue());           
      break;        
    case STRING:            
      System.out.println(cell.getRichStringCellValue());            
      break;   
 }
}

4. Evaluate the Formula to Get the Cell Value

Apache POI provides a FormulaEvaluator class, which enables us to calculate the results of formulas in Excel sheets.

So, we can use FormulaEvaluator to calculate the cell value at runtime directly. The FormulaEvaluator class provides a method called evaluateFormulaCell, which evaluates the cell value for the given Cell object and returns a CellType object, which represents the data type of the cell value.

Let’s see this approach in action:

// existing Workbook setup 
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();  
// existing Sheet, Row, and Cell setup 
if (cell.getCellType() == CellType.FORMULA) {
    switch (evaluator.evaluateFormulaCell(cell)) {
        case BOOLEAN:
		System.out.println(cell.getBooleanCellValue());
		break;
        case NUMERIC:
		System.out.println(cell.getNumericCellValue());
		break;
        case STRING:
		System.out.println(cell.getStringCellValue());
		break;
	}
}

发表评论