We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Pivot Table Page Fields not working in Excel 2007

Based on the sample pivot table creation code from the documentation (see sample code below), the page field filters are not working in Excel 2007 for multiple filter items (see sample screenshot 1 attachment). The Excel 2003 output file does work as expected though.

By browsing through the different properties in Excel, the only difference is that the pivot table option "Tool & Filters > Filters > Subtotal filtered page items" is ticked in 2007 (see sample screenshot 2 attachment). Unfortunately, we cannot seem to programmatically untick this option.

We've also attached the 2 sample files generated.

Is this a bug in Aspose.Cells for the Excel 2007 output?

Sample Code

//Instantiating a Workbook object
Workbook workbook = new Workbook();

//Obtaining the reference of the newly added worksheet
Worksheet sheet = workbook.Worksheets[0];
Cells cells = sheet.Cells;

//Setting the value to the cells
Cell cell = cells["A1"];
cell.PutValue("Sport");
cell = cells["B1"];
cell.PutValue("Quarter");
cell = cells["C1"];
cell.PutValue("Sales");
cell = cells["A2"];
cell.PutValue("Golf");
cell = cells["A3"];
cell.PutValue("Golf");
cell = cells["A4"];
cell.PutValue("Tennis");
cell = cells["A5"];
cell.PutValue("Tennis");
cell = cells["A6"];
cell.PutValue("Basketball");
cell = cells["A7"];
cell.PutValue("Basketball");
cell = cells["A8"];
cell.PutValue("Golf");
cell = cells["B2"];
cell.PutValue("Qtr3");
cell = cells["B3"];
cell.PutValue("Qtr4");
cell = cells["B4"];
cell.PutValue("Qtr3");
cell = cells["B5"];
cell.PutValue("Qtr4");
cell = cells["B6"];
cell.PutValue("Qtr3");
cell = cells["B7"];
cell.PutValue("Qtr4");
cell = cells["B8"];
cell.PutValue("Qtr3");
cell = cells["C2"];
cell.PutValue(1500);
cell = cells["C3"];
cell.PutValue(2000);
cell = cells["C4"];
cell.PutValue(600);
cell = cells["C5"];
cell.PutValue(1500);
cell = cells["C6"];
cell.PutValue(4070);
cell = cells["C7"];
cell.PutValue(5000);
cell = cells["C8"];
cell.PutValue(6430);

Aspose.Cells.Pivot.PivotTables pivotTables = sheet.PivotTables;

//Adding a PivotTable to the worksheet
int index = pivotTables.Add("=A1:C8", "E1", "PivotTable2");

//Accessing the instance of the newly added PivotTable
Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];

//Unshowing grand totals for rows.
pivotTable.RowGrand = false;

//Draging the first field to the row area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 0);

//Draging the second field to the column area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, 1);

//Draging the third field to the data area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 2);

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page, 0);


//Saving the Excel file
string timestamp = DateTime.Now.ToString("yyyyMMddHHmmss");
workbook.Save(string.Format("C:\\temp\\test2003_{0}.xls", timestamp), FileFormatType.Excel97To2003);
workbook.Save(string.Format("C:\\temp\\test2007_{0}.xlsx", timestamp), FileFormatType.Excel2007Xlsx);

Hi,

Thanks for providing us details with sample code and template files.

After an initial test, we found the issue as you have described. We will investigate it and get back to you soon.

We have logged your issue into our issue tracking system with an issue id: CELLSNET-17982.

Thank you.

Hi,

Please try the attached version 5.0.0.4.
We have fixed the issue your mentioned.


Thank you

Brilliant. This patch version fixed our problem.

Thank you.

The issues you have found earlier (filed as 17982) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by aspose.notifier.