On Tue, 19 Apr 2011, Alex Geller wrote:
We need different styles, colspan, rowspan, etc. because the output is supposed to resemble the layout of the report as closely as possible. This keeps us from using the csv trick. For the same reason, we suspect that the XML zip injection trick (see http://www.realdevelopers.com/blog/code/excel Streaming xlsx files ) that can also be found on this forum cannot be applied either. Is this assumption correct? The XML for the data looks straightforward but what about other issues like cell styles?
Take a look a the BigGridDemo - it may do what you need it to. The idea there is to use the friendly UserModel code to generate fiddly bits like fonts, styles, formatting etc. Then, generate the data with a low level xml streaming, and merge the two.
This should hopefully let you generate a fairly rich file, with lots of data, without using much memory
Translated into rows and cells this means that with a heap space of 265 MB, one can produce 101,000 rows using HSSF and only 12,300 rows using XSSF. Using XSSF we can't even get over the 65535 limit with the maximum of 1.1 GB heap space.
The usual answer for XSSF is either to use something along thing BigGridDemo style, or just bump up your heap size (8gb memory modules usually cost something like half a day's billable rate for a programmer, so you can buy a lot of memory for the price of someone optimising the code...)
There are references to both the sheet (_sheet) and the workbook (_book). Isn’t it possible remove _book and implement getBoundWorkbook() as getSheet().getWorkbook()?
Possibly. Are you able to use your test rig to check the performance impact of this?
The values are apparently stored in _record based on _cellType to cater for the different data types (double, date string ..). Why not get rid of the type field and query the value for the type (getCellType() { return _record.getCellType(); }? The case of setting a style before a value can be handled by assigning a "type only" value.
I think we've generally gone for the simplest option. If you can see how this'd work and would save memory, please send in a patch and we'll look at applying it
It seems that the member variable _stringValue is used to store string values. Couldn't this be stored in _record?
We need to store the parsed form somewhere. Wouldn't it be the same memory use no matter if we stored it against the cell or the cell's record?
The member variable _comment apparently stores a cell comment.
Finding a cell's comment is a bit tricky, so we cache it once we locate it.
Assuming that per average there are more values than comments one could surely find a more efficient storage strategy. As an example one could introduce extra value types so that for every cell record type there is a commented and a non commented version (e.g. DoubleCellValueRecord, CommentedDoubleCellValueRecord).
Hmm, that doesn't look very clean to me. One thing that we could do is push the cache down into the sheet, since that's where the records are stored. If we used a map there to cache the comments once created from records, that'd probably help with the memory footprint, wouldn't it? Assuming so, please send in a patch and I'll review + apply.
Looking at the storage method used in the rows (HSFFRow) to store the cells, there is also potential for simple memory optimization. Currently, the rows are stored in a vector that grows the capacity by doubling, starting with an initial size of 5. A spread sheet of 81 columns and 400,000 rows wastes (79*400,000=32MB).
I'd be tempted to switch this to just using an ArrayList, instead of handling it ourself. We could probably also do something smart with the sizing of the row when reading in, because we can probably figure out then how many cells we have. Would that help for your case? If so, please either send in a patch, or give me a shout and I'd be happy to tackle that
Keeping a list of the row widths seen so far can make the allocation much faster and avoid the waste. Aren't most sheets square so that the list would have only one entry?
Not sure where that logic is to check, but if you'd like to send in a patch I'll happily review it, or point me at the code and I'll look and comment :)
Regarding XSSF it seems that there is a more basic problem. Can an all purpose (xmlbeans) model be as a efficient as a custom model?
Almost certainly not. It's a hell of a lot quicker to code though!
Can the memory consumption realistically be lowered from now 630 byte/cell to 37 bytes/cell without significant loss of performance (which isn't great to begin with)?
It's not impossible that something specific and lightweight could be coded up for a few hot bits, though I've never tried it. The issue is that at the moment, most of the people volunteering their time to work on POI can't spend as long as they'd like working on POI. The resource that's short is programmer time, and for us it isn't memory (there are workarounds like BigGridDemo that work well enough)
If the XSSF memory is a problem for you, and if you have some programmer time to throw at it, we'd love for you to help! However so far everyone who's hit problems has either switched to BigGridDemo, or thrown a grand at their favourite server manufacturer and bought 16gb of memory to make the problem go away...
A solution that would perhaps solve the problem would be to have a common in-memory model for both HSSF and XSSF and just have two separate serializers for the different formats.
The two formats probably aren't quite close enough for this. We've got common interfaces, but the code underneath is different enough that it needs different logic. Some bits are common, see the concrete classes in org.apache.poi.ss.usermodel and ss.util for those, but the rest currently needs to differ
Otherwise, thanks for doing all this checking! And if you have some time to help work on solutions, we'd love for you to help and send in patches to improve things :)
Cheers Nick
--------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
