Free Support Forum - aspose.com

PivotTables with sum in column orientation?

Aspose.Cells Product Family
#1

Hi all,

Let's say I have an Excel sheet with A, V1 and V2 columns... I would like to create a PivotTable that would :

- create a single line for each value in the A column

- display for this line the sum of V1 values and V2 values

For now I'm able to create two lines for each value of A (one for the V1 total, one for the V2 total). In Excel I can drag /drop the "Data" header to the "Total" header and I've got then a single line for each value of A with a "Sum of V1" and "sum of v2" headers... The macro created by Excel for this is :

With ActiveSheet.PivotTables("Tableau croisé dynamique2").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With

I attached a file that shows the final result i would like to get.

Can I do the same with Aspose.Cells and if yes how ? Thanks in advance.

#2

Hi,

Thanks for providing the template file containing the pivot table.

We will provide the solution using Aspose.Cells APIs soon.

Thank you.

#3

Hi,

If you drag two or more PivotFields to Data Area, we will init PivotTable.DataField and drag it to Row Area(Same as MS Excel). So you should drag PivotTable.DataField to Column Area.See following codes:

Workbook workbook = new Workbook();
workbook.Open(@"F:\FileTemp\Sample.xls");//source sheet

Worksheet pivotSheeet = workbook.Worksheets[0];

PivotTables pTables = pivotSheeet.PivotTables;
string sourceData = "Feuil1!A1:C4";
string destCellName = "D3";
int index = pTables.Add(sourceData, destCellName, "PivotTable1");

// access the new pivottable
PivotTable pTable = pTables[index];
pTable.AddFieldToArea(PivotFieldType.Row, 0);
pTable.AddFieldToArea(PivotFieldType.Data, 1);
pTable.AddFieldToArea(PivotFieldType.Data, 2);
//
pTable.AddFieldToArea(PivotFieldType.Column, pTable.DataField);
pTable.DataField.DisplayName = "Données";