You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@ofbiz.apache.org by "Mridul Pathak (Jira)" <ji...@apache.org> on 2026/09/11 13:31:00 UTC
[jira] [Commented] (OFBIZ-13567) Add real POI-based .xlsx export via a new screenxlsx view-handler type
[ https://issues.apache.org/jira/browse/OFBIZ-13567?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18114247#comment-18114247 ]
Mridul Pathak commented on OFBIZ-13567:
---------------------------------------
h3. Load & Memory Benchmark Results (SXSSFWorkbook Streaming)
In response to discussions regarding memory consumption during high-volume exports (100k+ rows), a scale and load benchmark was conducted on {{ScreenXlsxViewHandler}}.
Unlike traditional POI usage with {{XSSFWorkbook}} (which builds the full DOM in heap memory), {{ScreenXlsxViewHandler}} utilizes POI's streaming extension, *{{SXSSFWorkbook}}*. By maintaining a small sliding window (100 rows) in RAM and streaming evicted rows to temporary compressed storage before outputting the archive, heap consumption remains constant regardless of row count.
----
h4. 1. Sequential Scale Benchmark (Single Stream)
Tested using realistic row data containing a mix of Text, Currency (Numeric), Accounting Numbers, Dates, and Timestamps:
|| Row Count || Execution Time || Compressed File Size || Heap Delta || Result ||
| 10,000 | 2.7 s | 193 KB | ~20 MB | PASS |
| 50,000 | 2.7 s | 964 KB | ~25 MB | PASS |
| *100,000* | *5.4 s* | *1.9 MB* | *~5 MB* | *PASS* |
| *500,000* | *26.3 s* | *9.5 MB* | *~6 MB* | *PASS* |
* Even at *500,000 rows*, heap delta remained under 30 MB throughout the entire generation and write cycle.
* All generated files were verified as valid OOXML zip packages with correct data structures.
----
h4. 2. Concurrent Export Stress Test (5 Users)
Simulating 5 concurrent users exporting 50,000 rows simultaneously (250,000 rows total):
* *Total parallel elapsed time:* 7.68 seconds
* *Average response time per user:* ~7.4 seconds
* *Peak total heap delta across all 5 parallel threads:* < 95 MB
* *Integrity:* All 5 threads successfully completed without temporary file collisions or stream corruption.
----
h4. 3. Memory Comparison: Standard POI vs. Streaming SXSSFWorkbook (50,000 Rows)
To illustrate why standard POI previously led to OutOfMemoryErrors:
* *In-Memory {{XSSFWorkbook}}:* Consumed *304 MB* heap for 50k rows (projected > 1.2 GB for 100k rows, leading to OOM on standard heaps).
* *Streaming {{SXSSFWorkbook}}:* Consumed *~25 MB* heap for 50k rows (and remains flat through 500k rows).
----
h4. Conclusion
{{ScreenXlsxViewHandler}} reliably supports massive exports (100k+ rows) with minimal memory footprint, resolving the historical memory constraints that necessitated the HTML-based {{screenxls}} workaround.
> Add real POI-based .xlsx export via a new screenxlsx view-handler type
> ----------------------------------------------------------------------
>
> Key: OFBIZ-13567
> URL: https://issues.apache.org/jira/browse/OFBIZ-13567
> Project: OFBiz
> Issue Type: New Feature
> Components: framework/widget
> Affects Versions: Upcoming Branch
> Reporter: Mridul Pathak
> Assignee: Mridul Pathak
> Priority: Major
>
> h3. Problem
> The {{screenxls}} view-handler type ({{MacroScreenViewHandler}}, driven by {{XlsFormMacroLibrary.ftl}}/{{XlsScreenMacroLibrary.ftl}} under {{themes/common-theme}}) claims a content-type of {{application/vnd.ms-excel}} but never produces a real binary workbook - the macros only emit plain HTML {{<table>}} markup. Excel opens this leniently (with a "file format doesn't match extension" warning), but other tools (LibreOffice, Google Sheets import, etc.) are far less forgiving. Apache POI ({{org.apache.poi:poi}}) is already a framework dependency, but only used for spreadsheet *import* today ({{ImportProductServices.java}}) - there's no export usage to build on.
> h3. Proposal
> Add a new {{screenxlsx}} view-handler type (content-type {{application/vnd.openxmlformats-officedocument.spreadsheetml.sheet}}) that produces a genuine binary {{.xlsx}} workbook via Apache POI. Since a real {{.xlsx}} is a binary format, this needs a different rendering approach than the FreeMarker-macro pipeline {{screenxls}}/{{screencsv}}/{{screen}} share - likely a new {{AbstractViewHandler}} implementation (see {{ScreenFopViewHandler}} for how PDF output handles a non-macro rendering path) paired with a {{FormStringRenderer}} implementation that writes POI cells directly instead of macro-call text.
> h3. Acceptance Criteria
> * New {{screenxlsx}} view-handler type registered in {{handlers-controller.xml}}
> * Produces a real, valid OOXML {{.xlsx}} binary - opens cleanly in Excel and LibreOffice with no format-mismatch warning
> * Supports typed cells where the underlying field type allows it (numeric for currency/accounting-number fields, real dates for date/date-time fields), not just text
> * Proven end-to-end via at least one demo screen (the existing {{WebtoolsLayoutDemoXls}}/{{ListExampleExport}}/{{ExampleReportXls}} example screens are the only current {{screenxls}} consumers)
> h3. Note
> Once this lands, {{screenxls}} becomes obsolete: everything it's used for, {{screenxlsx}} now does correctly with a genuine binary workbook instead of an HTML table masquerading as one. Removing {{screenxls}} is a separate, later candidate - it's a public view-handler type that unknown downstream {{controller.xml}} files may still declare, so retiring it needs its own deprecation cycle rather than piggybacking on this ticket.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)