How to define Row and Data and have "Sum of" as columns?

Aspose.Cells Product Family
1

Hi all,

So the following code creates a PivotTable with "Sum of" shown on rows. I would like to have "Sum Of " shown on columns that is if in Excel I drag the 3rd field (which has been added automatically) from the "Row" area to the "Column" area, I have the result I wish. How to do that ? Thanks in advance.

My test code is :

Aspose.Cells.Workbook wbk=new Aspose.Cells.Workbook();
Worksheet wks = wbk.Worksheets[0];

// Test data
int index = wbk.Worksheets.Add();
wks.Cells[0, 0].PutValue("A");
wks.Cells[0, 1].PutValue("B");
wks.Cells[0, 2].PutValue("C");
wks.Cells[0, 3].PutValue("D");
// Will have much more columns
for(int i=1;i<=5;i++)
{
wks.Cells[i,0].PutValue("a");
wks.Cells[i,1].PutValue(i%2==0?"a":"b");
wks.Cells[i,2].PutValue(i);
wks.Cells[i,3].PutValue(i*10);
}

// PivotTable
Worksheet x = wbk.Worksheets[index];
index= x.PivotTables.Add(wks.Name +"!A1:"+wks.Cells.End.Name, "A1", x.Name);

PivotTable p = x.PivotTables[index];
p.AddFieldToArea(PivotFieldType.Row, 0);
p.AddFieldToArea(PivotFieldType.Row, 1);

for(int i=2; i<=3;i++)
p.AddFieldToArea(PivotFieldType.Data, i);

string fichier = System.IO.Path.Combine(System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Test.xls");
wbk.Save(fichier);
Process.Start(fichier);

Thanks in advance.

2

Hi,

Thanks for your question and interest in Aspose.Cells.

Can you please also provide me the xls/xlsx file the like of [Want.PNG]? We will help you asap.

3

Sorry,

I found finally <A href="https://forum.aspose.com/t/127610</A> which is the same cases.</P> <P>I added :</P> <P> //p.RefreshDataFlag = true;<BR> p.ColumnFields.Add(p.DataField);<BR> //p.CalculateData();</P> <P>and it seems to work fine (commented lines are shown in the answer but it seems they are not needed). The trick I missed is that the "Sum Of" is exposed under its own DataField property.

Thanks anyway for the quick help proposal...

4

Hi,

It’s great, you were finally able to resolve your problem. Please feel free to ask any other question.