Behaviour of Workbook object for Excel

Aspose.Cells Product Family
1

I was just curious to understand if the Workbook object that works on the file needs to be saved after each action that I take. For example I have following set of actions

  1. Copy the data from database to excel(reading data from each table into one excel sheet). If the data is large in each sheet say 10 lakh record and 50 columns, does the cell get corrupted
  2. Filter - should the workbook object be saved after each Filter. In case I do not save it and work on some other operations like Refresh Pivot or data copy in some other sheet, will the Work book object retain the filter value?

I would like to understand the behaviour of Workbook object. It would be great if there is some documentation around it which can help with the Do’s and Dont’s and the impact it has on memory? Also what all operations can be carried out on a Workbook object before the need to Save it?

2

@shahsidd1,

I think for your scenario/case, you do not necessarily need to save the Workbook after performing every piece of task. You may import all the data into the spreadsheet, perform/do all the data filtering and then refresh filters, refresh pivot tables, copying data, etc., and finally save the workbook.

I won’t recommend adding such a huge list data to be inserted into a single sheet , it may affect performance (consume CPU and more memory). You should split data into multiple sheets.

3

Thanks Amjad,

Have tried your recommendation. Will let you know if we encounter any issues around it

4

@shahsidd1
You are welcome. If you have any questions or confusion, please feel free to contact us at any time.

5

Hi Amjad,

We tried a couple of scenarios where we have more than 50 actions back to back. While using the same Workbook object we encountered issues in Formula Copy, sheets containing more than a specified number of rows(for our case 10 lakh) and using Sheet Filter or Pivot Filter. It gives exception around Iterator iterating over array. A sample exception message is

Exception in getFinalSheetDataToSave: java.lang.IndexOutOfBoundsException: Index 3789 out of bounds for length 3789

So am not sure if using the Same workbook object over and over again does provide the expected behaviour. Is there any guidelines you can provide?

6

@shahsidd1,

Could you please provide a sample console Java program (source code without compilation errors) to reproduce the issue on our end. Also, please zip the resource files prior attaching here. We will check your issue soon.