Out of memory errors when exporting very large tables

Aspose.Cells Product Family
1

Hello,


We have some pretty big database tables (800,000x40) that sometimes need to be exported to spreadsheet. We’re okay with them taking several minutes, but as of now our individual web servers have a pretty limited amount of RAM - 2GB. We are doing a pretty simple importResultSet operation to get the data into the spreadsheet. Styling is relatively minimal and doesn’t significantly affect RAM use in my testing.

When I’m trying to export tables of this size, I always get out of memory errors. Are there any tricks you know of to make this work, perhaps a way to get Aspose to unload the parts from memory that are already written? Also, we use an OracleCachedRowSet instead of a ResultSet - does that have any effect on memory use in your testing?

Thanks a lot.

Sam
2

Hi Samuel,

The issue mentioned by you needs thorough investigation. We are working on it. The issue has been logged into our Issue Tracking System with ID CELLSJAVA-20501. We will update you about our findings and the solution.

Thanks,

3

Thank you. Please let me know when you find something.

4
<!–[if gte mso 10]> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman","serif";}

<![endif]–>

Hi,


For exporting excel files from large datasets, we will support to save cells data to stream or file directly without building the complete data structures in memory. This way we would be able to get rid of OutOfMemory errors for most of the large Excel files. Hopefully we can support this feature before the end of this week.


5

Hi,



Please try the new fix v2.4.1.7 (attached). In the new fix we have
provided a new Interface(LightCellsDataProvider) for saving large
dataset into the generated Excel file without building the complete data
structure in the memory. The attachment JAVA file is a sample
implementation of this interface based on data from database.



Thank you

6

Great, thank you. I’m a little confused about how this operates, however - what does CellsHelper.gatherStyles do? There is nothing in the API docs about it.


In fact, could you attach more info about how this works in general? It’s clearly not just a drop-in replacement for importResultSet and I wonder if it can even handle styles at all.

I did some mucking around and ended up with an almost-functional spreadsheet, but only about one-in-three values were filled in in some columns, none in others, lots of #VALUE! everywhere. I attached my modifications which I did because I need this to work as importResultSet did for styling reasons. Or would a template be the only reasonable way to do this?

By the way your forum software doesn’t take .java extensions (?).
7

Hi,

Well, because style objects are global for a workbook, it needs to be analyzed before saving a Workbook. LightCellsDataProvider works during the save process, if you want to apply style on the cells/rows, all the style objects that will be used for the cells/rows should be gathered before starting the save process. That is what CellsHelper.gatherStyles() method does. You normally input an array of all style objects will be used, we gather them into the global pool and return the corresponding style index. Later you can use Cell.setStyle(int styleIndex)/Row.setStyle(int styleIndex) to apply style on Cell/Row in LightCellsDataProvider.startCell(Cell)/startRow(Row). Also it will be much efficient than setStyle(Style) methods.

The save process uses LightCellsDataProvider to get and save sheet data row by row in ascending order of row index. For one row, cell will be processed and saved one by one in ascending order of column index. At first nextRow() will be checked, if a valid row index is returned, startRow(Row) will be called(you can set row properties, such as, row height here) and then the row properties are saved. Then nextCell() will be checked, if a valid column index to be returned, startCell(Cell) will be called(you can set cell properties and value here) and then the cell data will be saved. If nextCell() returns -1, one row finished and nextRow() will be checked to get next row to be saved. If nextRow() returns -1, one sheet finished.

Also, you may zip the .java extension files prior attaching here as you cannot directly attach the .JAVA file here.


Hope, this will help you.

Thank you.






8

The issues you have found earlier (filed as 20501) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.
9

Is this supported in the current .NET version of aspose.cells?

10

Hi,

Thanks for your question.

Aspose.Cells for .NET has done lot of memory improvements recently. You should give it a try.

Please download: Aspose.Cells
for .NET v6.0.0.3