Bug 61701 - XSSFName.getSheetName() throws when the named range refers to a formula with a Table range
Summary: XSSFName.getSheetName() throws when the named range refers to a formula with ...
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.17-FINAL
Hardware: Macintosh Mac OS X 10.1
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2017-10-31 08:34 UTC by viktor
Modified: 2017-10-31 09:56 UTC (History)
0 users



Attachments
simple-table-named-range.xlsx (9.56 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-10-31 08:34 UTC, viktor
Details

Note You need to log in before you can comment on or make changes to this bug.
Description viktor 2017-10-31 08:34:10 UTC
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.
Comment 1 viktor 2017-10-31 09:40:46 UTC
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)".
Comment 2 PJ Fanning 2017-10-31 09:56:53 UTC
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])