Loading an MS Excel file best practices for speed and performance in .NET

[Pietswieb]

Hi.
I’m looking into using aspose.cells as a calculation engine in a .net web api. I manage to get it working but i need to optimize the performance. so i was wondering if there are any best practises for sped and performce. Now load the excel speed for each request with the help of a WorkbookDesigner();

What is the best way to load the excel file without having the change of getting results of a concurrent request mixed up.

Are there any performace tips for the loading and calculation part?

kind regards

[Amjad_Sahi]

@Pietswieb,

Thanks for your query.

Well, we already enhanced the relevant modules (internal) for loading/reading and saving/writing MS Excel file formats. Do you find any performance issue, kindly elaborate with a sample application (runnable) to reproduce the issue on our end, we will check it soon. You may zip the project (excluding Aspose.Cells assembly) with template file (if any). Please use dynamic dataset/datatable in your code to remove any inter dependencies so we could execute your sample to reproduce the issue.

By the way, we recommend you to kindly use LightCells or MemorySetting.MemoryPreference mode to reduce memory and enhance peformance.

Reference Articles:

https://docs.aspose.com/display/cellsnet/Using+LightCells+API
https://docs.aspose.com/display/cellsnet/Optimizing+Memory+Usage+while+Working+with+Big+Files+having+Large+Datasets

Hope, this helps a bit.

[Pietswieb]

Thanks for the response and I will look into the documentation you referred to…

I don’t have issues now but this api must do 150k calculations in an hour… So I need to optimize it as best as I can…

I thought there must be an elegant way to get the excel sheet ready… Better then loading it each time from disk on every request…

So if there are more tips and tricks on this subject please let me know…
Kind regards,

[shakeel.faiz]

@Pietswieb

Thanks for using Aspose APIs.

From what you have said, we are not sure whether you are using the calculating engine of Aspose.Cells or just getting data from template file by Aspose.Cells and then do calculation by yourself. If you do calculation by Aspose.Cells, then all data (including formulas, names) should be loaded and we are afraid there is no better way to improve the performance much more. If you do calculation by yourself and only need to get data from template file, then you can choose to load cell value only, which may improve performance of loading template file. And if you can manage the grid data by yourself too, then you can use LightCells APIs for loading template file, which may give better performance for both speed and memory cost.

Anyway, please check the following article relating to LoadDataFilterOptions that will enable you filter data while loading workbook.

• Filter Objects while loading Workbook or Worksheet

So, you may have options according to your specific situation:

If you want to calculate formulas by Aspose.Cells, at least you need to load CellData and DefinedNames:

LoadDataFilterOptions.CellData|LoadDataFilterOptions.DefinedNames

If you only need to get cells data from the workbook, then you can load CellValue only:

LoadDataFilterOptions.CellValue

If you only need to read out all data in the template file only once and then manage those values by yourself for being used later in your application, then you can use LightCells to fetch cell data from the template file. Meanwhile, you will still able to use LoadDataFilterOptions to filter data.

[Pietswieb]

thanks for the response and the explanation. I’ve made the changes as suggested and we are getting close the desired performance.

Thanks for the help and support.

[shakeel.faiz]

@Pietswieb

It is good to know that you are employing these changes successfully. If you find any problem, please feel free to let us know. We will be glad to help you further.