LightCells Provider for Large Excel Files

Aspose.Cells Product Family
1

Hello - I am trying to create large excel files 100K Rows with 300 columns and 5 worksheets. I looked at the lightcells example and tried it out without much success. The resultant excel file cannot be opened at all in Excel 2010 or 2013. Is LightCells designed for such largefiles. I am evaluating Aspose Cells and a different product. The competing product is able to create such files without major issues.


I have attached sample code. The code is in C#.
2

Hi Sameer,


Thank you for considering Aspose products.

The Light Cells APIs has been designed to manipulate cell data one by one without building the complete data model into the memory, thus saving the memory usage during the process. Although such huge data files (of 100000x300 Matrix) can be build using Light Cells API, but you must note that putting all data in a single sheet (as of your sample code) will surely need a lot of RAM for the process. If you do not have sufficient RAM, you may end up with an exception or an endless execution. Moreover, if you are able to save such Excel file in any case it will be huge is size and MS Excel will also take some time to open it.

For the elaboration purposes, I have attached my sample project along with the snapshot of results in Microsoft Excel 2010 64bit. My machine has 6GB of RAM and running Windows 7 Home Premium 64 bit edition. It took a while to generate a matrix of 10000x290. But when I tried to execute the same code with 100000x300, the execution time was exponentially high as compared to the smaller matrix.

Please check the attached project on your end and let us know of your feedback.
3

Hi,


I have tested your scenario and run your codes in a project, it works fine with Aspose.Cells for .NET v7.5.3.x on my end, it takes some time though (5 - 8 minutes or so) to complete the process on my Windows 8 machine with 8GB RAM), I have successfully created the big Excel file (approx. 76MB in size) on my end. Also, Babar is right, MS Excel will also take some time to open into it successfully. I tested to open the file into MS Excel, after 7-10 minutes or so, I got the error message in MS Excel:
“Excel cannot complete this task with available resources. Choose less data or close other applications.”. As you are setting lots of formulas to all the cells, so when you open the file into MS Excel, it will try to evaluate or calculate all the formulas in the cells which demands lots of resources and surely will take time.

I think this is not an issue with Aspose.Cells product as it successfully created the huge file. For MS Excel’s error, you may try to close all other applications and resources and then try to open it into MS Excel it it makes any difference. For confirmation, you may also try to create such a file having your huge data list of formulas in the cells (having a long list of contents and formulas list i.e. 100000 rows * 300 cols in a single worksheet) manually in MS Excel and save the file, close MS Excel and then open the file into it if you can do it.

We still recommend you to kindly don’t put such a huge data list with formulas in a single worksheet rather split your data list into multiple worksheets or in multiple files (if possible).

Let us know if you still have any concern or issue.

Thank you.