Hi,
I have been using Aspose.Cells to create a report and have run into a performance issue when applying styles to a small number of cells within a pivot table.
The spreadsheet (attached) contains a pivot table with just over 4700 rows, derived from 30,000 rows of test data (present in another worksheet), and attempting to apply format to the cells in the header and grand total rows is very slow.
Reducing the number of data rows also reduces the time taken to apply the format, despite the actual number of cells being formatted remaining the same.
I have included the code I have been using to test this below (using the latest version of Aspose.Cells), and have observed the time taken to format the cells to be:
4731 rows in pivot (30,000 rows in data sheet) - 53 seconds (1 minute 30 seconds in a debug build)
2165 rows in pivot (15,000 rows in data sheet) - 11 seconds
915 rows in pivot (7,500 rows in data sheet) - 2 seconds
Any assistance you can give would be appreciated (code follows)
var book = new Workbook(@“c:\tmp\rpttest\gpr.xlsx”);
var sheet = book.Worksheets[1];
var pivot = sheet.PivotTables[0];
pivot.RefreshData();
pivot.CalculateData();
var area = pivot.TableRange1;
Style overlineStyle = book.CreateStyle();
overlineStyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thick;
overlineStyle.Borders[BorderType.TopBorder].Color = Color.Black;
Style footerStyle = book.CreateStyle();
footerStyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thick;
footerStyle.Borders[BorderType.TopBorder].Color = Color.Black;
footerStyle.Font.IsBold = true;
for (int i = 0; i <= area.EndColumn; i++)
{
pivot.Format(area.StartRow + 2, i, overlineStyle);
pivot.Format(area.EndRow, i, footerStyle);
}
Hi Bob,
Workbook book = new Workbook(myDir + “GPR.xlsx”);
var sheet = book.Worksheets[1];
var pivot = sheet.PivotTables[0];
pivot.RefreshData();
pivot.CalculateData();
var area = pivot.TableRange1;
Style overlineStyle = book.Styles[book.Styles.Add()];
overlineStyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thick;
overlineStyle.Borders[BorderType.TopBorder].Color = Color.Black;
Style footerStyle = book.Styles[book.Styles.Add()];
footerStyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thick;
footerStyle.Borders[BorderType.TopBorder].Color = Color.Black;
footerStyle.Font.IsBold = true;
//Retrieving these values in a for loop forces the API to calculate the
//PivotTable area every time loop executes, therefore store them temporarily
int startRow = area.StartRow;
int endRow = area.EndRow;
int endCol = area.EndColumn;
for (int i = 0; i <= endCol ; i++)
{
pivot.Format(startRow + 2, i, overlineStyle);
pivot.Format(endRow, i, footerStyle);
}
Hi,
Thanks for getting back to me, and for your suggestion.
I did get a slight boost simply by using the version of Aspose.Cells you mentioned (it now takes 49 seconds down from the 53 I had using 8.0.2.0), but unfortunately storing the row and col values in separate variables as you suggested did not have any impact on the speed when I tried it.
I have been testing using a Core i5 @3.20GHz with 8GB of RAM, Windows 7 Pro, 64-bit, so I am a little surprised that your timings were so much slower than mine.
It also seems a little odd that the time taken to apply format to the cells in the pivot table seems proportional to the size of the pivot, even though the format is applied to the same number of cells in each case.
As mentioned in my original post if I roughly half the number of rows in the pivot table, the time taken drops from 49 seconds to 10 seconds. This holds true even after caching the column and row values as per your suggestion.
For comparison, I tested using Cell.SetStyle in a similar for-loop to apply format to the same number of cells in the source data sheet that has no pivot table, and this took almost no time at all.
Is there anything else I can try? As it stands, it is unfortunately too slow for me to use and I’ll have to find ways to produce the file without using a pivot table, which impacts other areas of our software.
Hi Bob,
The issues you have found earlier (filed as CELLSNET-42688) have been fixed in this update.
This message was posted using Notification2Forum from Downloads module by Aspose Notifier.