poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bugzi...@apache.org
Subject [Bug 57721] New: Evaluating formulas in named Excel table
Date Tue, 17 Mar 2015 11:14:39 GMT
https://bz.apache.org/bugzilla/show_bug.cgi?id=57721

            Bug ID: 57721
           Summary: Evaluating formulas in named Excel table
           Product: POI
           Version: 3.12-dev
          Hardware: PC
            Status: NEW
          Severity: normal
          Priority: P2
         Component: XSSF
          Assignee: dev@poi.apache.org
          Reporter: gruber.chri@gmx.at

Created attachment 32581
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=32581&action=edit
test excel file

If you evaluate all formula cells in an XLSX file (for instance using 
XSSFFormulaEvaluator.evaluateAllFormulaCells((XSSFWorkbook) workbook) ) and are
using formulas in "named tables" in Excel 2007+, an exception is thrown.

I don't know how you call these named tables exactly, but they are created in
Excel 2007+ by selecting a range (of data) and pressing the "Table" button in
the "Insert" ribbon (including headers). Excel then offers special "table
tools" in the title bar, when selecting a cell in the table.

If you create this kind of table and are using formulas referencing cells in
the named table, excel doesn't reference these rows/cells via A1, B2, etc., but
with their column and table names:


Create this simple table (attached as formular_test.xlsx):

A | B
-----
1 | 3
2 | 4

Then select the whole table and select Insert > Table, the table gets styled
with alternating row colors etc.
If you then create a sum for all the table data, excel displays the formula as:
=SUM(Table1[#All]) or =SUM(Table1[[#All];[A]:[B]]) or =SUM(Table1)

When reading such a file with Apache POI, calling evaluateAllFormulaCells
(source attached as UpdateFormulaTest.java) this exception is thrown:
Exception in thread "main" org.apache.poi.ss.formula.FormulaParseException:
Specified named range 'Table1' does not exist in the current workbook (thrown
when using "SUM(Table1[[A]:[B]])" as formula).

It looks like it fails, as soon as formulas are referencing these named tables.

-- 
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


Mime
View raw message