Pivot - extra summary column created

Aspose.Cells Product Family
1

hello,

I defined the pivot with A-H columns, however after CalculateData() method is called an extra column is added (I) . How to disable that? Screenshot is attached - I data highlighted in red

Thanks!

2

Hi,

Please try the attached version v5.3.3.4. If you still find any issue kindly give us your sample code and attach your input (if you have) + output files. We will check your issue soon.

Thank you.

3

Hi Anjad,

didn’t help, unfortunately

here’s my code - I copied&pasted the example code, modified - there is an extra sheet with raw data called Data, two pivot tables are created on another sheet (VPA_SAVINGS_WORKSHEET_PL)

Problem 1/ - when the sheet is opened from the Response / Attachment - data, colors etc are not in correct order, calculations are wrong. However when the file is saved and opened then - calculations/ colors are correct - this is the highest prio problem for us.

Problem 2/ extra column (N) - even after
pivotTablePLSum.ColumnGrand = false;

However there’s no extra data in the column I (??)

Excel file attached, test data in it

Thanks, Martin

__________________________

// Pivot Type
Aspose.Cells.Pivot.PivotTableAutoFormatType pivotType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Report5;


//############################make pivot PL
//Getting the pivottables collection in the sheet
int dataSheetRow = ++row; // last data row

Worksheet SummaryPLWorksheet = savingsExcel.Worksheets[SavingsReportExcelTemplate.VPA_SAVINGS_WORKSHEET_PL];

Aspose.Cells.Pivot.PivotTableCollection pivotTables = SummaryPLWorksheet.PivotTables;

//Adding a PivotTable to the worksheet
int index = pivotTables.Add("=Data!A1:I" + dataSheetRow.ToString(), “A2”, “PivotTablePL”);

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

//Showing the grand totals
pivotTable.RowGrand = false;
pivotTable.ColumnGrand = false;

//Setting the PivotTable report is automatically formatted
pivotTable.IsAutoFormat = true;

//Setting the PivotTable autoformat type.
pivotTable.AutoFormatType = pivotType;

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

//Draging the 4-9 fields to the column area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 3);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 4);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 5);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 6);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 7);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 8);

// pivotTable.RefreshDataFlag = true;
pivotTable.ColumnFields.Add(pivotTable.DataField);//move datafied to columns





//--------------Adding a Summary PivotTable to the worksheet
int indexSumPL = pivotTables.Add("=Data!A1:I" + dataSheetRow.ToString(), “J2”, “PivotTablePLSum”);

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

//Showing the grand totals
pivotTablePLSum.RowGrand = true;
pivotTablePLSum.ColumnGrand = false;

//Setting the PivotTable report is automatically formatted
pivotTablePLSum.IsAutoFormat = true;

//Setting the PivotTable autoformat type.
pivotTablePLSum.AutoFormatType = pivotType;

//Draging the second field to the row area.
pivotTablePLSum.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 1);

//Draging the sum fields to the column area.
pivotTablePLSum.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 5);
pivotTablePLSum.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 6);
pivotTablePLSum.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 8);

pivotTablePLSum.ColumnFields.Add(pivotTablePLSum.DataField);//move datafied to columns




//Setting the number format of the first data field
//main tables
for (int i = 0; i < 4; i++)
{
pivotTable.DataFields[i].NumberFormat = “#,##0”;

}
for (int i = 4; i < 6; i++)
{
pivotTable.DataFields[i].NumberFormat = “#,##0.00%”;

}

//sums
for (int i = 0; i < 2; i++)
{
pivotTablePLSum.DataFields[i].NumberFormat = “#,##0”;

}

for (int i = 2; i < 3; i++)
{
pivotTablePLSum.DataFields[i].NumberFormat = “#,##0.00%”;

}

// recalculate data
pivotTable.CalculateData();
pivotTablePLSum.CalculateData();


if (savingsExcel != null)
{
savingsExcel.Save(Response, “SavingsReport.xls”, ContentDisposition.Attachment, new XlsSaveOptions());
}




4

Hi,

After an initial test, I am able to notice the issue (extra column created). I have logged an issue with an id: CELLSNET-28352. We will look into it soon.

For your first issue, I could not find to evaluate it. Could you give us more screen shots for the first issue. We will also check it soon.

Thank you.

5

here is the screenshot of the same file - when opene directly without saving first - note weird sum values + incorrect coloring

6

Hi,

Thanks for providing us the screen shot for the first issue.

We will look into it too soon.

Thank you.

7

Hi,

Adding extra summary column is fixed in the attached version v6.0.0.1 and we will figure out the incorrect coloring issue soon.

Thank you.

8

Hi,


For incorrect colouring issue, we have released another fix version of Aspose.Cells for .NET v6.0.0.2. Please test your requirement with this latest assembly and let us know of your feedback.

Thank you

9

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

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