I use following codes to read attached "problem.xls" Excel file to list the named ranges. But it throws IllegalArgumentException when calling namedRange.isDeleted(). -- FileInputStream fis = new FileInputStream("problem.xls"); POIFSFileSystem poifs = new POIFSFileSystem(fis); HSSFWorkbook excelWB = new HSSFWorkbook(poifs); for (int i = 0; i < excelWB.getNumberOfNames(); i++) { try { Name namedRange = excelWB.getNameAt(i); if (namedRange.isFunctionName() || namedRange.isDeleted()) { continue; } System.out.println("name: " + namedRange.getNameName()); } catch (Exception ex) { logger.error("Read named range failed."); logger.error(ex, ex); System.out.println(ex); } } -- Exception details: java.lang.IllegalArgumentException: ptgs must not be null at org.apache.poi.ss.formula.FormulaRenderer.toFormulaString(FormulaRenderer.java:48) at org.apache.poi.hssf.model.HSSFFormulaParser.toFormulaString(HSSFFormulaParser.java:80) at org.apache.poi.hssf.usermodel.HSSFName.getRefersToFormula(HSSFName.java:192) at org.apache.poi.hssf.usermodel.HSSFName.isDeleted(HSSFName.java:201) -- Please help to check this issue. Thanks!
Created attachment 23444 [details] problem.xls
It appears that the attached workbook has a defined name "UPSState" with all other properties uninitialised. Excel silently ignores the name, and if you re-save, the name is removed. It seems like the offending record was created via POI, using code like this: Workbook wb = new HSSFWorkbook(); wb.createSheet("Sheet1"); Name n = wb.createName(); n.setNameName("UPSState"); wb.write(new FileOutputStream("bad-name.xls"); It would be a big change to make POI do the same as Excel (delete the name and adjust all formulas), so it was just fixed to avoid the IllegalArgumentException. For uninitialised defined names, isDeleted() will return false and getRefersToFormula() will return null. Fixed in svn r762479 junits added