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 2015/03/17 12:14:39 UTC
[Bug 57721] New: Evaluating formulas in named Excel table
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
[Bug 57721] Evaluating formulas in named Excel table
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57721
--- Comment #1 from gruber.chri@gmx.at ---
Created attachment 32582
--> https://bz.apache.org/bugzilla/attachment.cgi?id=32582&action=edit
java test pgm
--
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 57721] Evaluating formulas in named Excel table
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57721
--- Comment #2 from Stephen Matta <st...@gmail.com> ---
This is preventing me from shifting rows that use the formula. My workaround is
to use the "Table Tools/Design" tab and "Convert to Range" the table in Excel.
This is not ideal but it's allowing me to move forward for now.
--
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 57721] Evaluating formulas in named Excel table
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57721
Stephen Matta <st...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |stephenjmatta@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 57721] Evaluating formulas in named Excel table
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57721
Bug 57721 depends on bug 57840, which changed state.
Bug 57840 Summary: [PATCH] Support for structured references with Excel tables.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57840
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution|--- |FIXED
--
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 57721] Evaluating formulas in named Excel table
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57721
Javen O'Neal <on...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution|--- |FIXED
--- Comment #10 from Javen O'Neal <on...@apache.org> ---
Closing. This was added to the changelog under Version 3.15-beta2 (2016-07-02)
https://poi.apache.org/changes.html
--
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 57721] Evaluating formulas in named Excel table
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57721
Javen O'Neal <on...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Depends on| |57840
--
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 57721] Evaluating formulas in named Excel table
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57721
--- Comment #5 from GW <gr...@gmail.com> ---
This isn't a problem with formula function coverage, it is a core problem with
XSSF formula SYNTAX evaluation. POI just doesn't understand Excel Table
"Structured References" in formulas. These are XSSF format objects only, and
have no equivalent in HSSF.
https://support.office.com/en-us/article/Using-structured-references-with-Excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e
This means POI formula support is limited to pre-2007 syntax only, which is an
increasing problem.
The fix should not be too hard, but would be deep into XSSFEvaluationWorkbook.
There is no function to register. It would need to extend getName(String, int)
to also check all sheets for tables matching the given name (tables have
globally scoped names but are tied to specific sheets), and return something
similar to EvaluationName.
Since table references are just convenience syntax, this could just map
directly to a range specification and parse the formula using the range
expression from that point on, I think.
I plan to dig a bit, but any fix will involve some extensive refactoring, so
I'm not sure anyone outside the core committers can write a patch acceptable to
the repository owners.
--
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 57721] Evaluating formulas in named Excel table
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57721
--- Comment #4 from Javen O'Neal <on...@apache.org> ---
Patches are always welcome!
Submitting patches: https://poi.apache.org/guidelines.html#SubmittingPatches
Understanding formula evaluation:
https://poi.apache.org/spreadsheet/eval.html
https://poi.apache.org/spreadsheet/eval-devguide.html
--
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 57721] Evaluating formulas in named Excel table
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57721
--- Comment #6 from GW <gr...@gmail.com> ---
I found this issue, with a potential patch, that has received no attention in
over a year:
https://bz.apache.org/bugzilla/show_bug.cgi?id=57840
From the description, this looks like exactly what is needed.
--
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 57721] Evaluating formulas in named Excel table
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57721
Javen O'Neal <on...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |NEEDINFO
--- Comment #8 from Javen O'Neal <on...@apache.org> ---
Is anything else needed to close this bug?
--
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 57721] Evaluating formulas in named Excel table
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57721
Greg Woolsey <gr...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEEDINFO |NEW
--- Comment #9 from Greg Woolsey <gr...@gmail.com> ---
I think the other patches accepted for Structured Reference syntax cover this.
I've been on other tasks for a bit, will be digging back into the project that
needed this shortly. If I find anything else, it would be new bugs filed
separately (hopefully with patches :D )
--
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 57721] Evaluating formulas in named Excel table
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57721
GW <gr...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Version|3.12-dev |3.12-FINAL
Hardware|PC |All
CC| |greg.woolsey@gmail.com
--- Comment #3 from GW <gr...@gmail.com> ---
Tables are used widely in formulas, but POI formula evaluation doesn't handle
them. This is a significant problem for us, as Excel table references make
writing complex formulas much simpler and less error-prone.
--
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 57721] Evaluating formulas in named Excel table
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57721
--- Comment #7 from Javen O'Neal <on...@apache.org> ---
With the added support of structured references from bug 57840, using the
XSSFFormulaEvaluator to evaluate all formulas in a workbook containing
structured references works. Applied unit test in r1747740 to make sure this
continues to work into the future.
--
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 57721] Evaluating formulas in named Excel table
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57721
Dominik Stadler <do...@gmx.at> changed:
What |Removed |Added
----------------------------------------------------------------------------
Blocks| |63934
Referenced Bugs:
https://bz.apache.org/bugzilla/show_bug.cgi?id=63934
[Bug 63934] The column doesn't exist in lookup table
--
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 57721] Evaluating formulas in named Excel table
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57721
Dominik Stadler <do...@gmx.at> changed:
What |Removed |Added
----------------------------------------------------------------------------
OS| |All
--
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