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: [email protected]
Reporter: [email protected]
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: [email protected]
For additional commands, e-mail: [email protected]
