Hello
I have a table with 17000 records and one column is using GETPIVOTDATA function on a PivotTable.
On my computer this is taking 30 seconds to complete with Aspose.Cells whereas in Excel it takes a second or two.
Is there anything that can be done to improve this performance issue?
Here is the sample code: (xlsx file attached)
var workbook = new Workbook(“PivotTableTest.xlsx”);
Stopwatch sw = new Stopwatch();
sw.Start();
Console.WriteLine(“Calculating”);
workbook.CalculateFormula();
Console.WriteLine(“Time taken: {0}”, sw.ElapsedMilliseconds);
Many thanks
Francisco Taborda
Hi,
Sample code:
var workbook = new Workbook(“PivotTableTest.xlsx”);
Stopwatch sw = new Stopwatch();
sw.Start();
Console.WriteLine(“Calculating”);
workbook.CalculateFormula();
Console.WriteLine(“Time taken: {0}”, sw.ElapsedMilliseconds);
I have logged a ticket with an id “CELLSNET-44847” for your issue. We will check if we could enhance the formula calculation engine further to calculate the formulas efficiently.
Hi
I’ve tried version 16.10.6.0. It seems to be faster, but on the example file I’ve sent previously it still takes 15seconds.
Is there any room for more optimization?
Thank you
Best regards
Francisco Taborda
Hi Francisco,
Hi again,
Hi
Were there any more improvements?
Best Regards
Francisco
Hi Francisco,
Hello
Were there any improvements?
This is still taking 30 second, at least on the second run.
On the example file I’ve sent previously:
Calculate: 13 seconds
Refresh Pivot Table: 500ms
Calculate again: 30 seconds
Many thanks
Best Regards
Francisco
Hi Francisco,
Here is the sample code:
Stopwatch sw = new Stopwatch();
var workbook = new Workbook(“PivotTableTest.xlsx”);
sw.Restart();
Console.WriteLine(“Calculating”);
workbook.CalculateFormula();
Console.WriteLine("Time taken: " + sw.ElapsedMilliseconds);
sw.Restart();
Console.WriteLine(“Refresh Pivot”);
var ws = workbook.Worksheets[0];
foreach (var pivotTable in ws.PivotTables.Cast())
{
pivotTable.RefreshData();
pivotTable.CalculateData();
}
Console.WriteLine("Time taken: " + sw.ElapsedMilliseconds);
sw.Restart();
Console.WriteLine(“Calculating again”);
workbook.CalculateFormula();
Console.WriteLine("Time taken: " + sw.ElapsedMilliseconds);
Best Regards
Francisco Taborda
Hi again,
Hi Francisco,
The issues you have found earlier (filed as CELLSNET-44847) have been fixed in this update.
This message was posted using Notification2Forum from Downloads module by Aspose Notifier.
Hello
The second run seems to be the same time as the first (~15sec).
15 seconds is still a lot compared to what excel does (~1sec), not sure if anything else can be done…
Thank you
Francisco Taborda
Hi again,
The issues you have found earlier (filed as CELLSNET-44899) have been fixed in this update.
This message was posted using Notification2Forum from Downloads module by Aspose Notifier.