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

Free Support Forum - aspose.com

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

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.

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.

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...

Hi,

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