I am new to Aspose and large data export. When we writing large rows using into xls file where the rows storing? Please look into the following two cases and suggest.
Case1:
Workbook wb = new Workbook() then all the rows loaded to memory
Case2:
Workbook wb = new Workbook(filePath+“book1.xls”) this will not load all the rows into memory? please suggest, i need avoid the rows saving into the memory.
In Case1, a new (blank) workbook will be created (into memory) without any data in the rows/cols.
In Case2, an existing file “book1.xls” will be loaded into Aspose.Cells’ object model. In this case, data in the workbook would be loaded into memory.
Let us know if you still have any confusion or issue.
Thank you so much for the clarification. How to avoid keeping the rows in memory when writing large excel file. When we using the Light cell api if we don’t know how many sheet, rows, column will come then how to pass the sheet/row and column count. Every data is dynamic, I am getting the data’s from database and writing to multiple sheets. in the example(Using LightCells API|Documentation) the sheet/row and column count is predefined, how can i make it dynamic.Specifically the sheet count. Can you suggest me how to avoid all the rows keeping in memory?
Yes, the LightCells API is designed to manipulate cell data one by one without building a complete data model block (using the Cell collection etc.) into memory. It works in an event-driven mode. Please note, in light weight mode, one Cell object is processed and then discarded, the Workbook object does not hold the collection. In this mode, memory is saved when importing and exporting Microsoft Excel files that has a large data set which would otherwise use a lot of memory.
As you are getting data from some data source or database, so, you may use built-in .NET APIs to retrieve total number of records/rows or its count, so you may evaluate and specify respective pointers (row count, col count and sheet count, etc.) accordingly using your code.
1 Like
Thanks for the reply. My Scenario is i have multiple cards each card have multiple rows. In the first sheet i will write all the rows of first board and if the rows count exceed 65000(xls) then i will create next sheet. So a board can have multiple sheet, i cannot decide how many sheets for all the board. So it’s better to have sheet count in the light api is dynamic. please look into my sample code and suggest how to make it in the light API in JAVA.
If i not use the lightcall api, can you suggest how to keep all the rows in a file instead of memory. Once i write a row its needs to go file not memory.
I think you may evaluate and count records for each card and then specify how many sheets would be generated. By the way, if you could use XLSX file format instead, you do not need to have only 65K rows in a single sheet as for XLSX file format, you can have 1048576 rows and 16384 columns.
I do not find your sample code. If you find any issue with your sample code, kindly do share runnable sample code, we will check it.
I can evaluate but it is query cost. if any option to flush the rows in memory like Apache poi((SXSSFSheet)sh).flushRows(100)) ? My concern is i like to avoid keeping the rows in memory. please suggest.
Please try your scenario/case using Aspose.Cells APIs and in case you find any performance issue, let us know with details, sample code (runnable) and template file(s), we will check it soon.
With Aspose.Cells, the only way to output Cell/Row data directly to a file instead of memory is to use LightCells. However, when generating files, a lot of global information(including the properties and settings of Worksheet) must be collected and written to the file before writing Cell/Row data, so we can not support dynamic generation/modification of worksheets for LightCells APIs while processing Cell/Row data.