Hello!
I have been using Aspose.Cells for .NET for a while now, and it has been working great. I am now using version 7.2.0, the latest version at this time.
My questions concern recommended best practices for generating and sending large Excel files via ASP.NET (4.0, webforms). Especially where they may be 10 users requesting a large file almost simultaneously.
In hopes of best performance, this is what I am currently doing (high-level) in an ASP.NET 4.0 webforms application:
1. Using an xltx template with column headers and column formatting applied.
2. Retrieving data into custom object collections. The final workbook would contain 2-3 worksheets populated from each object collection, with 2 of the worksheets having up to 150,000-200,000 potential rows total across the sheets, and 40-44 columns (mostly text, one number, and several dates). The data is stored in the Cache to reduce database hits.
3. Using the custom object population method, specifying some of the import options.
4. Autofitting the columns (I know this takes RAM and time).
5. Saving the final workbook to the response stream.
Questions:
1. Is using a template with pre-formatted columns a waste of time? Does the formatting occur again when the data gets populated? For columns that are definitely text-only (no “numbers” that should be text), should the formatting be turned off (left at General)?
2. Are custom object collections the fastest or best way to populate the worksheets? I’ve noticed they are pretty fast for the sample size I used (50K on one worksheet, 70K on second worksheet).
3. I’ve noticed that the biggest slowdown occurs when calling Workbook.Save, on the Response whether using .xlsx or .xslb (all end-users have Office 2007+). I’ve read in other posts that saving the file first, and then streaming it over http to the user, can be faster, but my own tests (limited) don’t show much of a difference. If using this latter technique, is it recommended to use a FileStream? I’d want to remove the files after download, as they become stale every day.
I am aware that importing data, auto-fitting, and applying styles all take up RAM, along with any type of Workbook.Save call.
Can you provide any recommendations or best practices for this scenario? Pre-generating the files might normally be an option, however, there would be at least 400 different possible Excel spreadsheets (of varying size, same columns)–which is why I’d like to only generate the ones actually requested by the end user.
Thank you, Jason
Hi,
Thanks for your posting and using Aspose.Cells for .NET.
The quick way could be to import your data from databases/datatables into your worksheet instead of custom object. Please see this article.
Importing Data to Worksheets
Also, I think, it will be better if you first save your file and then transmit it later, because it will free up the memory resources that the workbook object could take.
Also, if you can pregenerate anything or few things, it will definitely speed up the performance.
I have logged your questions in our database. Development team will look into it and then give you further advice.
This issue has been logged as CELLSNET-40613.
Thanks for considering Aspose APIs.
Please use LightCells or MemorySetting.MemoryPreference mode to reduce memory.
Reference Articles: