Created attachment 35478 [details] simple-table-named-range.xlsx When a named range is defined as a reference to the formula 'SUM(Table1[c])', calling `name.getSheetName()` throws an IllegalArgumentException. java.lang.IllegalArgumentException: Invalid CellReference: SUM(Table1[c]) at org.apache.poi.ss.util.CellReference.separateRefParts(CellReference.java:395) at org.apache.poi.ss.util.CellReference.<init>(CellReference.java:113) at org.apache.poi.ss.util.AreaReference.<init>(AreaReference.java:60) at org.apache.poi.xssf.usermodel.XSSFName.getSheetName(XSSFName.java:295) Full source code to reproduce the issue: public class Main { public static void main(String argv[]) { System.out.println("POI 3.17"); try { InputStream input = Main.class.getClassLoader().getResourceAsStream("simple-table-named-range.xlsx"); Workbook workbook = WorkbookFactory.create(input); Name name = workbook.getName("total"); System.out.println("workbook.getName(\"total\").getSheetName() returned: " + name.getSheetName()); } catch (Exception e) { e.printStackTrace(); } } } The Excel file used in this sample is attached.
UPD: The issue can be reproduced without using tables. The same exception is thrown when a `Name` refers to a simple sum formula like "SUM($A$1:$C$1)". The code below shows how to reproduce the issue without an excel file: try { // Start with Creating a workbook and worksheet object XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Sheet1"); // Create a row with a three number cells XSSFRow row = sheet.createRow(0); for (int i = 0; i < 3; i++) { XSSFCell cell = row.createCell(i); cell.setCellValue(i + 1); } // Create a name referring to a formula Name name = workbook.createName(); name.setNameName("Total"); name.setRefersToFormula("SUM($A$1:$C$1)"); System.out.println("workbook.getName(\"Total\") .getSheetName() returned: " + name.getSheetName()); } catch (Exception e) { e.printStackTrace(); } NOTE: it looks like in this case the error is in the `org.apache.poi.ss.util.AreaReference#isContiguous` method which wrongly returns true for formulas like "SUM(Table1[c])" and "SUM($A$1:$C$1)".
I added a disabled test case in https://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/eventusermodel/TestXSSFReader.java?r1=1813863&r2=1813862&pathrev=1813863 and https://svn.apache.org/viewvc?view=revision&revision=1813864 The AreaReference and CellReference constructors don't currently handle values like: SUM(Table1[c]) java.lang.IllegalArgumentException: Invalid CellReference: SUM(Table1[c])