You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by "isagi-y0ichi (via GitHub)" <gi...@apache.org> on 2026/05/10 03:21:54 UTC
[D] Is There a Better Architecture Than Per-Formula Ephemeral Workbooks? [poi]
GitHub user isagi-y0ichi created a discussion: Is There a Better Architecture Than Per-Formula Ephemeral Workbooks?
We operate a report-generation engine built on top of Apache POI (poi-ooxml
5.x, Java 17). Our workflow is:
1. An Excel (.xlsx) template is parsed ONCE at registration time using the
SAX-based XSSFReader to extract all formula cells and their PTG dependency
graphs (via FormulaParser / Ptg[]). The dependency graph is serialized
and cached. we use hacks like sheet name ending with _ex contains formulas which are extrapolated through entire columns so special parsing is required
2. At report-generation time, data from external sources is written into per-sheet CSV files on disk. A CSVAccessor
holds the full CSV content in a List<List<String>> in-memory cache.
3. Formulas are evaluated in topological order. For each formula node we:
a. Create a brand-new XSSFWorkbook.
b. Populate only the cells the formula depends on by reading from the
CSV cache and calling row.createCell().setCellValue().
c. Call workbook.getCreationHelper().createFormulaEvaluator()
.evaluateCellFormula(cell).
d. Write the result back to the CSV cache.
e. Call our clearAllData() method to discard the workbook and all its internal structures, freeing
memory for the next formula evaluation.
4. After all formula nodes are evaluated, a SXSSFWorkbook (rowsInMemory=1)
is streamed out and uploaded.
For a template with 10 000 formula cells this means 10 000 XSSFWorkbook
allocations with all their internal CTWorkbook/CTStylesheet XML structures,
each discarded immediately after one formula evaluation. GC pressure is
enormous and we regularly see long pause times.
For large-range aggregates (SUM(A1:A10000000)) we wrote a custom
FormulaSplitter that breaks the PTG range into chunks:
SUM(A1:A10000000)
→ SUM(A1:A5000000) evaluated separately, result written to CSV
→ SUM(A5000001:A10000000) evaluated separately, result written to CSV
→ final SUM of the two partial results
This is completely serial, still creates one workbook per chunk, and only
supports a hard-coded set of aggregation functions (SUM, COUNT, SUMIFS,
COUNTIFS, SUBTOTAL). all this to make memory effiecient at the cost of time :( .
Is there a way to evaluate a formula in POI without materialising data into an XSSFWorkbook at all while still leveraging evaluation provided by poi or decoupling entire formula evaluation is the way ? was thinking of extracting the formula ( input will always be an excel cant change that ) convert the formulas to a map reduce ? then evaluate the same paralelly across instances too atp idk
Any guidance, pointers to internal POI APIs, or alternative architectural
approaches
GitHub link: https://github.com/apache/poi/discussions/1061
----
This is an automatically sent email for dev@poi.apache.org.
To unsubscribe, please send an email to: dev-unsubscribe@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: [D] Is There a Better Architecture Than Per-Formula Ephemeral Workbooks? [poi]
Posted by "isagi-y0ichi (via GitHub)" <gi...@apache.org>.
GitHub user isagi-y0ichi added a comment to the discussion: Is There a Better Architecture Than Per-Formula Ephemeral Workbooks?
ps : also was looking at making it realtime , i vibe coded a prototype wherein teh agent extened the EvaluationWorkbook , delegated most of the operations and intercepted the getCells getSheets method to return the avlues from csv access , didnt benchmark the formula evaluation when the formula becomes complicated tho
GitHub link: https://github.com/apache/poi/discussions/1061#discussioncomment-16876706
----
This is an automatically sent email for dev@poi.apache.org.
To unsubscribe, please send an email to: dev-unsubscribe@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: [D] Is There a Better Architecture Than Per-Formula Ephemeral Workbooks? [poi]
Posted by "centic9-dt (via GitHub)" <gi...@apache.org>.
GitHub user centic9-dt added a comment to the discussion: Is There a Better Architecture Than Per-Formula Ephemeral Workbooks?
Apache POI is mostly concerned about reading and writing the various Microsoft formats. Evaluating and handling formulas is more of a necessity for being able to read and write files.
Therefore formula evaluation is currently depending a lot on the underlying workbook-structures, formulas also usually reference other cells, so there is a natural need for being able to access data in the workbook throughout formula evaluation.
Large parts of formula evaluation should be independent of the actual Workbook-format (HSSF, XSSF, SXSSF), so you might be able to experiment with a few things:
* Run performance evaluation to find where actual CPU and GC hotspots are. Tools like IntelliJ IDEA, Java Flight Recorder or similar tools, maybe things can be improved for your specific case
* Try to cache the XSSFWorkbook and just "clean it" between calls, thus reducing memory allocations and lower GC impact, but at the risk of potential side-effects between operations
* The XML-based XSSF/SXSSF classes introduce more memory churn than the older binary format. Used the .xls format via HSSWorkbook might improve performance if your downstream processing can handle this older binary format
GitHub link: https://github.com/apache/poi/discussions/1061#discussioncomment-16875933
----
This is an automatically sent email for dev@poi.apache.org.
To unsubscribe, please send an email to: dev-unsubscribe@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: [D] Is There a Better Architecture Than Per-Formula Ephemeral Workbooks? [poi]
Posted by "isagi-y0ichi (via GitHub)" <gi...@apache.org>.
GitHub user isagi-y0ichi added a comment to the discussion: Is There a Better Architecture Than Per-Formula Ephemeral Workbooks?
@centic9-dt thanks for your insight !
- we are doing something similar , for each formula since we know the depdencies have a CSVAcessor wrapper over a csv , which i use to prepopulate the workbook with the data then populate the formula itself and then run evaluate
- when i checked for "cleaning" the cells after every formula evaluations haven't profiled the reason yet , i found heap spikes , thats the reason i resorted to just generate a new notebook , leaving it to the gc
will look at if upgrading jdk to 21 works + profile it a better way had last done it over 6-7 months ago
do you have any other ideas on how i can achieve this workflow more efficiently
- dont want to implement the formula myself still want to leverage poi formula evaluation and report creation
- have it in xlsx only since there operations heavily use google sheets and find it easier to just upload and we configure the data source with manual schema validation
- maintain the same easy onboarding flow
GitHub link: https://github.com/apache/poi/discussions/1061#discussioncomment-16876679
----
This is an automatically sent email for dev@poi.apache.org.
To unsubscribe, please send an email to: dev-unsubscribe@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: [D] Is There a Better Architecture Than Per-Formula Ephemeral Workbooks? [poi]
Posted by "centic9-dt (via GitHub)" <gi...@apache.org>.
GitHub user centic9-dt added a comment to the discussion: Is There a Better Architecture Than Per-Formula Ephemeral Workbooks?
Another thing to investigate would be upgrading to JDK 21, we saw considerable GC improvements in an unrelated very large application which was bound mostly on memory and GC.
GitHub link: https://github.com/apache/poi/discussions/1061#discussioncomment-16875948
----
This is an automatically sent email for dev@poi.apache.org.
To unsubscribe, please send an email to: dev-unsubscribe@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org