Pivot tables aggregate values in columns on in rows

Aspose.Cells Product Family
1

I want the aggregate values of Pivot table by default in Column lables and not in Row lables.

Please find the attached files. The problem area is highlited with red box

2

Hi,

Please test your issue with the latest version:
Aspose.Cells
for .NET v6.0.1.4 it should fix the problem.

If the problem persists, then please provide me simple sample C# project replicating the problem. We will investigate it and provide you a solution/fix asap.

3

Updating the Aspose.dll hasnt solved my problem.

Here the code I am using

Aspose.Cells.Pivot.PivotTableCollection pivotTables = wSheet.PivotTables;

int index0 = pivotTables.Add("=Detail!A1:AF" + rowCount, "A3", "PivotTable1");

Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index0];

pivotTable.RowGrand = true;

pivotTable.ColumnGrand = true;

pivotTable.AutoFormatType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Report8;

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, "Service");

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, "Group");

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, "TemplateID");

pivotTable.DataFields[0].AddCalculatedItem("Template Count", "=COUNT('TemplateID')");

pivotTable.DataFields[0].DragToColumn = true;

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, "Hours");

pivotTable.DataFields[1].AddCalculatedItem("Total Hours (000’s)", "=SUM('Hours')");

pivotTable.DataFields[1].DragToColumn = true;

4

Is there any way I can wrap the header. Please see the screen shots attached above.

5

Hi,

I am confused, which headers you are talking about. However, if you want to wrap text inside a cell, then you should refer to this article: Line Break and Text Wrapping

6

Even the new Aspose ddl haven't helped me.

Find find the attached project.

Can you also tell me how to wrap the header of the pivot table. I read the article you gave but didn' t help. I couldn't find style property for it.

Aspose.Cells.Cells cell = wSheet.Cells;

cell[0,0].S

7

Hi,

Please see the code below that illustrates how to wrap the header. Please see the screenshot, the input and output file generated by the code.

C#


string path = @“F:\Shak-Data-RW\Downloads\source.xlsx”;

Workbook workbook = new Workbook(path);


Worksheet worksheet = workbook.Worksheets[0];


PivotTable pvtTable = worksheet.PivotTables[0];


CellArea columnRange = pvtTable.DataBodyRange;


string firstColumn = CellsHelper.CellIndexToName(columnRange.StartRow, columnRange.StartColumn);


Cell cell = worksheet.Cells[firstColumn];


Style style = cell.GetStyle();

style.IsTextWrapped = true;

cell.SetStyle(style);


worksheet.AutoFitRow(columnRange.StartRow);


workbook.Save(path + “.out.xlsx”);

Screenshot:
8

Hi,

Please use this sample code to put the datafield in column labels.

C#


pivotTable0.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, “Col2”);

pivotTable0.DataFields[0].DragToColumn = true;

pivotTable0.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, “Col3”);

pivotTable0.DataFields[1].DragToColumn = true;

pivotTable0.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, pivotTable0.DataField);