IndexOutOfRangeException thrown from PivotTable.CalculateData

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,


Thank you for contacting Aspose support.

We are able to replicate your presented scenario while using the latest version of Aspose.Cells for .NET 8.2.0. The problem needs thorough investigation, therefore we have logged it in our bug tracking system under the ticket CELLSNET-42929. Please spare us little time to properly analyze the problem cause, and to provide a fix (if applicable). In the meanwhile, we will keep you posted with updates in this regard.

We are sorry for the inconvenience caused to you.

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,


Thank you for the resolution confirmation on the previously reported issue. Regarding the recently presented scenario, we have noticed that Range.SetOutlineBorder does not seem to produce correct results while using the latest version of Aspose.Cells for .NET 8.2.0.2. We have logged the issue in our bug tracking system under the ticket CELLSNET-42965 for further investigation. Please spare us little time to properly analyze the problem cause, and to provide a fix at earliest. In the meanwhile, we will keep you posted with updates in this regard.

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,


Good to know that your issue is resolved now, we have closed the ticket. Feel free to write back if you need further help or have some other issue or queries regarding Aspose.Cells APIs, we will be happy to assist you soon.

Thank you.

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.