Hi,
I have an application that creates pivot tables using Aspose.Cells, based on user supplied data, and I have found what appears to be an issue with PivotTable.CalculateData, and I am hoping that you will be able to help me find a solution.
If a pivot table is created with 2 or more Column fields, where they each only have a single distinct value, with no “Total” columns (meaning Excel will display a single column after the Row columns), CalculateData throws an IndexOutOfRangeException.
I have attached a zip file containing 2 spreadsheets that demonstrate the issue, and the code I have used to test them is further down in this post.
“PivotTest - Fails.xlsx” contains data which causes the exception on the first sheet, a pivot table manually created from that data in Excel on sheet 2, and sheet 3 is blank where the sample code below will create a pivot using Aspose.Cells, with the same fields as the manually created one.
“PivotTest - Succeeds.xlsx” contains an extra row of data (which will cause the Column fields to be displayed over 2 columns), but is otherwise the same as the first spreadsheet. The sample code does not cause an exception using this file.
The code I am using the test this is as follows (using the latest version of Aspose.Cells version):
var book = new Workbook(@“c:\tmp\rpttest\PivotTest - Fails.xlsx”);
var sheet = book.Worksheets[2];
PivotTable pivotTable = sheet.PivotTables[sheet.PivotTables.Add("=Data!TableData", “A1”, “TableName”)];
pivotTable.RowGrand = false;
pivotTable.AddFieldToArea(PivotFieldType.Row, “Period”);
pivotTable.BaseFields[“Period”].IsAutoSubtotals = false;
pivotTable.AddFieldToArea(PivotFieldType.Column, “Company”);
pivotTable.BaseFields[“Company”].IsAutoSubtotals = false;
pivotTable.AddFieldToArea(PivotFieldType.Column, “Frequency”);
pivotTable.AddFieldToArea(PivotFieldType.Data, “Name”);
pivotTable.BaseFields[“Name”].Function = ConsolidationFunction.Count;
pivotTable.RefreshDataOnOpeningFile = false;
pivotTable.RefreshData();
pivotTable.CalculateData();
book.Save(@“c:\tmp\rpttest\PivotOut.xlsx”, SaveFormat.Xlsx);
If I set pivotTable.RowGrand = true or set the AutoSubtotals = true or remove one of the Column fields, the exception is not thrown, but this does not really help me, as it will alter the pivot table and so change the output from what is expected.
Any assistance you can give would be appreciated.
Hi Bob,
Hi,
Thanks for your using Aspose.Cells.
Please download and try the latest fix: Aspose.Cells for .NET v8.2.0.2 and let us know your feedback.
Hi,
Sorry for taking so long to reply.
I have finally had a chance to try this out and it does look like it’s solved the problem I had, so thank you very much.
I did notice another issue though. I’m sure it’s unrelated, but I mention it only because I spotted it while checking this fix.
The following code produces a line along the bottom of a range of cells using our current version of Aspose.Cells (8.1.0.0), but does not seem to do anything using the version you linked.
var book = new Workbook();
var sheet = book.Worksheets[0];
var range = sheet.Cells.CreateRange(1, 1, 1, 2);
range.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Thick, Color.Black);
If I create a style with appropriate border settings, and apply it to the range it works fine, so I can use this to get around it for now.
Thanks again.
Hi Bob,
Hi,
Thanks for your using Aspose.Cells.
Please download and try the latest fix: Aspose.Cells for .NET v8.2.0.3 and let us know your feedback.
Hi,
Yes, this appears to have sorted it.
Thanks for all your help.
Hi,
The issues you have found earlier (filed as CELLSNET-42929) have been fixed in this update.
This message was posted using Notification2Forum from Downloads module by Aspose Notifier.