Use of Aspose.Cells to modify large Excel spreadsheets

Aspose.Cells Product Family
1
Hi guys,

We have to modify a single worksheet in a quite large Excel spreadsheet. The size of this Excel spreadsheet is about 200Mb on disk and it doesn't contain any charts, pictures or formulas. It just contains 15 million decimal values distributed across 20 worksheets.

The problem is that Aspose needs about 900Mb of memory to load this Excel spreadsheet into a Workbook object, whereas Microsoft Excel 2003 or 2007 is happy with just 250Mb.

In order to decrease the memory consuption, and given that we just need to modify a single worksheet, we took the approach to load a single worksheet by using the SheetIndexes property in the LoadDataOptions as described here:
http://www.aspose.com/community/forums/314453/direct-to-file-mode/showthread.aspx

However, after finishing our modifications on the worksheet and saving the Workbook object into the Excel file then the data in the other worksheets is all lost. The worksheets in this saved Excel spreadsheet are all empty but the one we've selected in the LoadDataOptions. We're not sure whether we're doing something wrong or whether the purpose of the LoadDataOptions is just to load partially an Excel spreadsheet without any intentions to save the changes back.

Therefore, is it Aspose capable to modify large Excel spreadsheets or shall we just look for a different solution?

Thanks.

Ben.
2

Hi,


Aspose.Cells can create, load/save or modify the large Excel spreadsheets. If you are loading a 200MB file, obviously it will need lots of memory.

Moreover, if you are loading with specific worksheets, when you save the file, it will save the specific sheets, other sheets would be empty.

Could you try our latest version/fix: v7.0.3.6 if it makes any difference.

Also, if there is no graphics, formatting, formulas, you may simply use LoadOptions.LoadDataOnly options, see the document:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/opening-files.html




3

Hi Amjad,


Thanks for your quick response.

Surely a 200MB file will need lots of memory, but in this case we have to be very precise about what “lots of memory” means because it’s quite close to the actual maximum memory that a .NET process can address.

Please find attached screenshots of memory usage when the file is loaded from Excel and when it’s loaded with Aspose.Cells. I’m using the latest version of Aspose.Cells (v7.0.3.6) and set LoadOptions.LoadDataOnly option to true as you mentioned.

As you can see, the memory used by Aspose.Cells is about 3.5 times more than Excel. We’re concerned that we would have to deal with similar files that can be modified in Excel without any problems but impossible to modify with Aspose.Cells.

Is there any option with Aspose.Cells to modify a single worksheet in an Excel file without using a large amount of memory and keeping the existing data in the other worksheets?

Thanks.
4

Hi,



I have logged a ticket for investigation, we will look into it if we have any better solution to manipulate huge files. I have logged the ticket with an id: CELLSNET-40177.

Is there any option with Aspose.Cells to modify a single worksheet in an Excel file without using a large amount of memory and keeping the existing data in the other worksheets?

Well, currently it is not possible as if you load a specific sheet from the workbook, when you save the file to disk, other sheets would be blank. Anyways, we will also look into it.

Thank you.


5

@benjamin.arroyo,
Currently you may try MemorySetting.MemoryPreference mode for the workbook/cells. This option should be able to reduce the memory used.

Also we have a plan to implement the solution of loading/modifying/saving a workbook with light cells mode. However, this implementation is complicated and requires lots of time. Currently we suspend this task until we have time to continue.