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

Free Support Forum - aspose.com

Pivot tables aggregate values in columns on in rows

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

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.

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;

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

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

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

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:

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);