Bug 66661 - Issue with table formulas being modified by POI
Summary: Issue with table formulas being modified by POI
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 5.2.3-FINAL
Hardware: All All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2023-06-20 22:46 UTC by PJ Fanning
Modified: 2023-07-03 21:21 UTC (History)
0 users


Attachments
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description PJ Fanning 2023-06-20 22:46:36 UTC 
See https://lists.apache.org/thread/92vvvvwn25sjkbqo0rp8hlnkpb66lb84

reproduction code is in https://github.com/ManiacTwister/poi-table-bisect

A formula like `SUBTOTAL(109,Table1[Heading2])` can get overwritten by POI and become `SUBTOTAL(109,Sheet0!B2:B6)`.

One way to avoid this is to call this API on XSSFWorkbook.
https://poi.apache.org/apidocs/dev/org/apache/poi/xssf/usermodel/XSSFWorkbook.html#setCellFormulaValidation-boolean-

Setting it to false will stop POI rerendering your formulas.

It is suspected that https://github.com/apache/poi/pull/269 may be making this cell formula rerendering worse for this scenario.
Comment 1 ManiacTwister 2023-06-21 11:08:49 UTC 
Thanks for your effort.

I can confirm using the workaround `setCellFormulaValidation(false)` fixes the problem for my testcase as well as for my real use case.

Let me know if i can help/test anything to fix the issue.
Comment 2 PJ Fanning 2023-06-21 11:11:09 UTC 
Thanks ManiacTwister. We do need to make POI changes. PR 269 has got some problems.
Comment 3 PJ Fanning 2023-06-22 21:37:15 UTC 
added r1910561