Pivot - how to move Sum Values to Column Labels

Aspose.Cells Product Family
1

I have a a few fields as PivotFieldType. Row - works fine. Also a few as PivotFieldType.Data - however the Sum Values for Data is in Row Labels area and I need them in Column Labels. How to do that programatically?

Thanks, Martin

2

Hi Martin,


You may try the following lines of code, and let us know of your results.

wb.Worksheets[0].PivotTables[0].RefreshDataFlag = true;

wb.Worksheets[0].PivotTables[0].ColumnFields.Add(wb.Worksheets[0].PivotTables[0].DataField);//move datafied to columns

wb.Worksheets[0].PivotTables[0].CalculateData();//if the pivottable range is changed ,please add this code.




3

this line throws error - null object when saving

wb.Worksheets[0].PivotTables[0].RefreshDataFlag = true; // causing


This works, thank you - my fix!:
wb.Worksheets[0].PivotTables[0].ColumnFields.Add(wb.Worksheets[0].PivotTables[0].DataField);//move datafied to columns

this method doesn't seem to exist:
wb.Worksheets[0].PivotTables[0].CalculateData();//if the pivottable range is changed ,please add this code.

4

Hi,


Thank you for your feedback. Can you please attach your Excel file, so that we can look into it. Also please share with us the version of Aspose.Cells you are currently using.
5

well, the error is in the code, not from the Excel.

I'm using the latest Aspose.Cells - 5.3.3 - the sshot attached, no CalculateData method

6

Hi,

Well, the method is there. Please make sure that you are using Aspose.Cells.dll file from net2.0 folder from your installation location.

I am also attaching the screen shot for your reference. I am using Aspose.Cells for .NET v5.3.3.1 (latest fix) (attached).

Thank you.

7

this is weird, I downloaded the zip, refreshed references in the VS project, but no CalculateData method shows up

btw when I try to open the file in the browser - the pivot tables are empty. When saved & re-opened everything is OK. The file I load to populate with the data and pivots is in the Excel 2003 format. The code used:

savingsExcel.Save(Response, "SavingsReport.xls", ContentDisposition.Attachment, new XlsSaveOptions());

Could it be related to CalculateData missing?

Thanks,

Martin

8

Hi,

Could you create a sample application with v5.3.3.1, zip it and post it here to show the issue. I want to check if CalculateData() is missing.

Also, for your issue, could you change your code to:

savingsExcel.Save(Response, “SavingsReport.xls”, ContentDisposition.Inline, new XlsSaveOptions());

Thank you.

9

Thank you, Amjad - it was my mistake, when referencing DLL the actual file was copied over from different folder. Everything works now - perfect!

Thank you for all your support! :)

Martin

10

You are welcome and good job Martin! You have figured out your issue.

Have a great day!

11

java i need this type reqiuremet ya pls post code.

12
Hi,

I think you may add the line (in JAVA)):
e.g

[JAVA]
//..............
pivotTable.getColumnFields().add(pivotTable.getDataField());//move data fields to column area

Thanks,