Hi all, This project I inherited uses POI (XSSF currently) as a sort of calculation engine, it creates a new spreadsheet, fills it with pages of data, then fills a page with formulas evaluating them one at a time, sending the results back to the client.
Today their current biggest set is 49k formulas, and it takes ~21 minutes to evaluate. If I apply these 2 patches to disable double formula validation: https://bz.apache.org/bugzilla/show_bug.cgi?id=61033 The same thing executes in 11 minutes. In the interest of completeness, I tried SXSSF with a -1 row limit but killed it at the ~50 minute mark. However, I determined that if I run the thing through HSSF it executes in about 70 seconds. But, I need a patch that increases HSSF's limits to XSSF limits, with the understanding you can't read or write that out, it only works in memory. Patch 1 in this 3 part series was actually an existing bug that has been merged here: https://bz.apache.org/bugzilla/show_bug.cgi?id=61059 Patch 2 can be merged stand-alone and will speed up existing uses of HSSF formulas as-is: https://code.moparisthebest.com/moparisthebest/poi/commit/8ab388eb780b0759367f904455690b5721e7ee6c The last patch allows you to send in an arbitrary SpreadsheetVersion to create a blank HSSFWorkbook, I'm guessing this might be somewhat controversial to merge: https://code.moparisthebest.com/moparisthebest/poi/commit/4727ab718d374ab86f465e51a83a3ef0018a944a (these are also available at github if you prefer https://github.com/moparisthebest/poi/commits/calc_engine ) All current tests pass, no API was changed (only added to), and behavior is also unchanged unless you create your workbook like 'new HSSFWorkbook(SpreadsheetVersion.EXCEL2007)', in which case it will let you write XLSX's limit of rows and formula calculations etc. Would a patch like this be acceptable? Any other ideas? Thanks much, Travis --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
