You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2017/10/31 08:34:10 UTC

[Bug 61701] New: XSSFName.getSheetName() throws when the named range refers to a formula with a Table range

https://bz.apache.org/bugzilla/show_bug.cgi?id=61701

            Bug ID: 61701
           Summary: XSSFName.getSheetName() throws when the named range
                    refers to a formula with a Table range
           Product: POI
           Version: 3.17-FINAL
          Hardware: Macintosh
                OS: Mac OS X 10.1
            Status: NEW
          Severity: normal
          Priority: P2
         Component: XSSF
          Assignee: dev@poi.apache.org
          Reporter: viktor@vaadin.com
  Target Milestone: ---

Created attachment 35478
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=35478&action=edit
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.

-- 
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

[Bug 61701] XSSFName.getSheetName() throws when the named range refers to a formula with a Table range

Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=61701

Anton Akkuzin <ak...@gmail.com> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |akkuzin@gmail.com

-- 
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

[Bug 61701] XSSFName.getSheetName() throws when the named range refers to a formula with a Table range

Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=61701

--- Comment #2 from PJ Fanning <fa...@yahoo.com> ---
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])

-- 
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

[Bug 61701] XSSFName.getSheetName() throws when the named range refers to a formula with a Table range

Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=61701

--- Comment #1 from viktor@vaadin.com ---
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)".

-- 
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org