PivotTable - Move values from row to column

Aspose.Cells Product Family
1

I am not able to move the grand total of values from the row section to the column section. I have provided a sample xlsx file and the general code(doesn’t exactly match the file provided but it is the same idea).

The 2nd tab is what the code is creating. The 3rd tab is what I need. I am not sure why I am getting the Summation values in the rows section anyway…

This is a picture of what I need to do
pivot-what-i-need.PNG (10.0 KB)

			PivotTableCollection pivotTables = pivotTableSheet.PivotTables;
			string pivotSource = $"='{ratingOverviewSheet.Name}'!A1:D{workingRow}";
			int pivotTableIndex = pivotTables.Add(pivotSource, pivotTableDestCellName, pivotTableName);
			var thePivotTable = pivotTables[pivotTableIndex];
			thePivotTable.PivotTableStyleType = PivotTableStyleType.PivotTableStyleMedium9;
			thePivotTable.AddFieldToArea(PivotFieldType.Row, 0);
			thePivotTable.AddFieldToArea(PivotFieldType.Data, 1);
			thePivotTable.AddFieldToArea(PivotFieldType.Data, 2);
			thePivotTable.AddFieldToArea(PivotFieldType.Page, 3);


			thePivotTable.ColumnGrand = true;
			thePivotTable.RowGrand = true;
			thePivotTable.ShowValuesRow = true;

			//Filter by category
			PivotField pf = thePivotTable.BaseFields["Category"];
			thePivotTable.PageFields.Add(pf);
			PivotField pivotField = thePivotTable.PageFields[0];
			int pageItemCount = pivotField.PivotItems.Count;
			for (int i = 0; i < pageItemCount; i++)
			{
				if (itemCategory.Equals(pivotField.PivotItems[i].Value))
				{
					pivotField.CurrentPageItem = (short)i;
				}
			}

			int chartIndex = pivotTableSheet.Charts.Add(ChartType.Bar3DClustered, 0, 0, chartBottomRow, 26);
			pivotTableSheet.Charts[chartIndex].PivotSource = $"{pivotTableSheet.Name}!{pivotTableName}";
2

@jojoshua,

Thanks for the screenshot and code segment.

Could you try to add a line of code to your code segment (see the line in bold) if it makes any difference:
e.g
Sample code:

…
PivotTableCollection pivotTables = pivotTableSheet.PivotTables;
string pivotSource = $"=’{ratingOverviewSheet.Name}’!A1:D{workingRow}";
int pivotTableIndex = pivotTables.Add(pivotSource, pivotTableDestCellName, pivotTableName);
var thePivotTable = pivotTables[pivotTableIndex];
thePivotTable.PivotTableStyleType = PivotTableStyleType.PivotTableStyleMedium9;
thePivotTable.AddFieldToArea(PivotFieldType.Row, 0);
thePivotTable.AddFieldToArea(PivotFieldType.Data, 1);
thePivotTable.AddFieldToArea(PivotFieldType.Data, 2);
thePivotTable.AddFieldToArea(PivotFieldType.Page, 3);
thePivotTable.AddFieldToArea(PivotFieldType.Column, thePivotTable.DataField);
…

if you still find the issue, kindly do create a sample console application (runnable), zip the project and post us here to show the issue. Also attach your template files (input file (if any), output file, etc.). Moreover, the output file should contain your desired pivot table intact (which you may create/update in MS Excel manually), we will check it soon.

3

Perfect, thanks.

4

@jojoshua,

Good to know that your issue is sorted out by the suggested line of code. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.