I'm trying to compare Aspose.Cells to Excel Automation using the code below. Although you claim Aspose.Cells should be faster, I'm not seeing it. Is the code I'm using an accurate test of these two technologies? I am planning to use your component to open Excel files for display on an ASP.NET page, so I need it to be fast. From my test, Excel Automation is outperforming Aspose.Cells consistently by 4-5 seconds (Aspose.Cells takes about 6 seconds, Excel Automation takes less than 2 seconds). This is a huge time difference for loading a web page for what I am trying to accomplish. I realize it may be something in my file, but can you point me to a way to determine why Aspose.Cells is so slow in this case? Thanks...
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
workbook.Open(args[1].ToString());
DateTime start = DateTime.Now;
workbook.CalculateFormula();
DateTime end = DateTime.Now;
TimeSpan ts = end.Subtract(start);
Console.WriteLine("Workbook.CalculateFormula() completed in " + ts.Seconds.ToString() + "." + ts.Milliseconds.ToString() + " seconds");
DateTime startXL = DateTime.Now;
Microsoft.Office.Interop.Excel.ApplicationClass xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook workbookXL = xlApp.Workbooks.Open(
args[0].ToString(),
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing);
xlApp.CalculateFull();
DateTime endXL = DateTime.Now;
TimeSpan tsXL = endXL.Subtract(startXL);
Console.WriteLine("xlApp.CalculateFull() completed in " + tsXL.Seconds.ToString() + "." + tsXL.Milliseconds.ToString() + " seconds");
Can you post your sample file here? We will check it ASAP. And which version of Aspose.Cells are you using?
Thanks for the quick response Laurence. The file is attached. I had some other errors initially with some functions, so I just upgraded to Aspose.Cells version 4.6.0.0…
I am also carrying out a speed comparison between Excel Automation and Aspose.Cells and I can share my timings so far.
I use a custom winforms application to harvest selected data from a sql server database (Defects from a collection of production lines). I then populate this data into a spreadsheet adding totals , yields , summaries, pivot tables and pivot charts (pareto charts of the defects).
The user selects a start date and a finsih date and the software generates 100% of the spreadsheet from the data (I don't use templates as the data is different every time).
Using Excel automation it takes the following times to harvest the data and produce the spreadsheet:
1 day of data : 19Mins 1 Sec
1 month of data : 4hrs 23Mins 59 Secs
You can see why I want to speed it up !!
With Aspose.Cells the Exact same format of spreadsheet is generated in the following times (same dates used):
1 day of data : 11 Secs
1 Month of data : 1Min 7 Sec.
For what I use this is a no brainer and the purchase order is being placed today .
Hope this helps you.
Kevin
Hi Kevin,
Thanks for sharing your findings.
We aware that all other portions (except for formula calculation engine) are quite efficient and optimized for Aspose.Cells for .NET. We are working on the component's formula calculation engine already to optimize its performance to certain extent. Hopefully, soon we would be be able to measure the performance of the component to benchmark and match the formula calculation results with any component in the market.
Thank you.
Hi Kevin,
After checking your template file, we found the main cause of your issue related formula calculation performance.
Actually the formulas are given in the format e.g.., =(SUMIF($C$16:$C$53310,"TI spent this year*",E16:E53310)) in the range CJ6:DA8 in the worksheet CCC. Please use Cells.MaxRow instead as the end row of the Range params in the formulas. For example, if the max row in the worksheet CCC is 20, you should change your formulas such as, =(SUMIF($C$16:$C$20,"TI spent this year*",E16:E20)).
And we will go on looking into this issue and try to fix it. When we have fixed this issue,we will post a fix soon for you here.
Thanks for your patience.
Amazing! Thank you for identifying that so quickly. After making that change, Aspose.Cells is now taking less than 0.5 seconds to run CalculateFormula (6-8 seconds before the change), and is outperforming Excel.Automation by 1-2 seconds.
The only problem is, that is a dynamic sheet and I don't always know the maximum number of rows that it may contain. I will try to optimize my spreadsheet and see if I can resolve it that way, but if you guys can optimize Aspose.Cells to handle that many rows efficiently, please let me know as that would be the best solution.
Thanks again for your quick response!
Hi,
Thank you for considering Aspose.
Please try the attached latest fix of Aspose.Cells. We have fixed some bugs of calculating formula and improved the performance of calculating SumIf function.
Thank You & Best Regards,
Thank you very much! That solved my issue, performance on the SUMIF is now extremely fast…a full 2.5-3 seconds faster on average than Excel automation. Thanks again for your stellar customer service and quick work…