Created attachment 39150 [details] the main Excle public static void main(String[] args) { ZipSecureFile.setMinInflateRatio(-1.0d); String path = "\\apache POI\\apachePOI\\src\\main\\resources\\test_template.xlsx"; try{ OutputStream os = new FileOutputStream(new File("\\apache POI\\apachePOI\\src\\main\\resources\\extRef\\test_template_2.xlsx")); Workbook workbook = new XSSFWorkbook(new FileInputStream(path)); FormulaEvaluator mainWorkbookEvaluator = workbook.getCreationHelper().createFormulaEvaluator(); File file = new File("\\apache POI\\apachePOI\\src\\main\\resources\\extRef\\test1.xlsx"); Map<String, FormulaEvaluator> formulaEvaluatorHashMap = new HashMap<String, FormulaEvaluator>(); formulaEvaluatorHashMap.put("report1.xlsx", mainWorkbookEvaluator); formulaEvaluatorHashMap.put("test1.xlsx", WorkbookFactory.create(file).getCreationHelper().createFormulaEvaluator()); mainWorkbookEvaluator.setupReferencedWorkbooks(formulaEvaluatorHashMap); mainWorkbookEvaluator.evaluateAll(); workbook.setForceFormulaRecalculation(false); workbook.write(os); }catch (Exception e) { e.printStackTrace(); } } java.lang.NullPointerException at org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.cacheExternalWorkbookCells(BaseXSSFFormulaEvaluator.java:99) at org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFormulaCellValue(BaseXSSFFormulaEvaluator.java:62) at org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateFormulaCell(BaseFormulaEvaluator.java:184) at org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateAllFormulaCells(BaseFormulaEvaluator.java:277) at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateAll(XSSFFormulaEvaluator.java:100) in BaseFormulaEvaluator.java:96 ,XSSFWorkbook externalWorkbook =(XSSFWorkbook) xssfCell.getSheet().getWorkbook().getCreationHelper().getReferencedWorkbooks().get(externalSheet.getWorkbookName()); getReferencedWorkbooks().size()=0，so externalWorkbook is null
That external workbook code looks like it needs more mull checks. I added r1913045 as a preliminary change.
Created attachment 39152 [details] ExternalRefence Excel
Finally,I add addExternalWorkbook function in XSSFWorkbook.java to add ExternalWorkbook to XSSFCreationHelper.referencedWorkbooks and add some codes: formulaEvaluatorHashMap.put("test1.xlsx", WorkbookFactory.create(file).getCreationHelper().createFormulaEvaluator()); ((XSSFWorkbook)workbook).addExternalWorkbook("test1.xlsx", WorkbookFactory.create(file).getCreationHelper().createFormulaEvaluator())); then that's OK. But in BaseXSSFFormulaEvaluator.cacheExternalWorkbookCells(),there not check the row is null or cell is null .and there will throw null Exception. so there add if check: int nullRowCont = 0; for (int rowIndex = firstRow; rowIndex <= lastRow; rowIndex++) { XSSFRow row = sheet.getRow(rowIndex); int firstColumn = area3DPxg.getFirstColumn(); int lastColumn = area3DPxg.getLastColumn(); // System.out.println("+++++row+++:"+rowIndex+" **row**："+row); if(null!=row) { for (int cellIndex = firstColumn; cellIndex <= lastColumn; cellIndex++) { // System.out.println("*****cellIndex*****:" + cellIndex); XSSFCell cell = row.getCell(cellIndex); // System.out.println("*****cellvalue*$$$$:" + cell); String cellValue = ""; String cellR = ""; if (null != cell) { cellValue = cell.getRawValue(); cellR = new CellReference(cell).formatAsString(false); externalLinksTable.cacheData(sheet.getSheetName(), (long) rowIndex + 1, cellR, cellValue); } } }else { nullRowCont++; } if(nullRowCont>20){ break; } }
I added more null checks with r1913258
Seems some checks were added here, thus closing this for now. Please report new issues if there are still some missing in latest versions of Apache POI. At least version 5.2.5 should include the changes from here.