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

Free Support Forum - aspose.com

How can I center the heading labels on a Pivot Table?

My pivot table appears like so (note "Description", "Jun 16" ... "Nov 16", and "Grand Total") like the first screen shot below.

The users want these headers to be centered, as is seen in the second screen shot below.

This is easy to do manually, after the spreadsheet has been generated, but how can I center these programmatically?

My code to create these fields is:

pivotTable.RowGrand = true;
pivotTable.ColumnGrand = true;

. . .

// Dragging the first field to the row area.
pivotTable.AddFieldToArea(PivotFieldType.Row, DESCRIPTION_COLUMN);
pivotTable.RowHeaderCaption = "Description";
//pivotTable. <= how to center these text labels/headers?

// Dragging the second field to the column area.
pivotTable.AddFieldToArea(PivotFieldType.Column, MONTHYR_COLUMN);
pivotTable.ColumnHeaderCaption = "Months";

For more context, here is the entire method:

private void PopulatePivotTableSheet()
{
int DESCRIPTION_COLUMN = 1;
int MONTHYR_COLUMN = 3;
int TOTALQTY_COLUMN = 4;
int TOTALPRICE_COLUMN = 5;
int COLUMNS_IN_DATA_SHEET = 11;
int FREEZEPANE_ROW = 7;

AddPreDataSectionToPivotTableSheet();

PivotTableCollection pivotTables = pivotTableSheet.PivotTables;
int colcount = COLUMNS_IN_DATA_SHEET;
string lastColAsStr = ReportRunnerConstsAndUtils.GetExcelColumnName(colcount);
int rowcount = sourceDataSheet.Cells.Rows.Count;
string sourceDataArg = string.Format("sourceDataSheet!A1:{0}{1}", lastColAsStr, rowcount);
int index = pivotTableSheet.PivotTables.Add(sourceDataArg, "A6", "PivotTableSheet");
PivotTable pivotTable = pivotTables[index];

pivotTable.RowGrand = true;
pivotTable.ColumnGrand = true;

pivotTable.DisplayNullString = true;
pivotTable.NullString = "0";
pivotTable.ErrorString = "---";

// Dragging the first field to the row area.
pivotTable.AddFieldToArea(PivotFieldType.Row, DESCRIPTION_COLUMN);
pivotTable.RowHeaderCaption = "Description";
//pivotTable. <= how to center these text labels/headers?

// Dragging the second field to the column area.
pivotTable.AddFieldToArea(PivotFieldType.Column, MONTHYR_COLUMN);
pivotTable.ColumnHeaderCaption = "Months";

// Dragging the third field to the data area.
pivotTable.AddFieldToArea(PivotFieldType.Data, TOTALQTY_COLUMN);
pivotTable.DataFields[0].DisplayName = "Total Packages";

pivotTable.AddFieldToArea(PivotFieldType.Data, TOTALPRICE_COLUMN);
pivotTable.DataFields[1].DisplayName = "Total Purchases";

pivotTable.AddCalculatedField("Average Price", "=IF(TotalQty<>0,TotalPrice/TotalQty,0)", true);

pivotTable.AddCalculatedField("PercentOfTotal", "=TotalPrice", true);
pivotTable.DataFields[3].DisplayName = "Percentage of Total";
pivotTable.DataFields[3].DataDisplayFormat = PivotFieldDataDisplayFormat.PercentageOfColumn;
pivotTable.RowFields[0].IsAutoSubtotals = false;

pivotTable.RefreshDataFlag = true;
pivotTable.RefreshData();
pivotTable.CalculateData();
pivotTable.RefreshDataFlag = false;
List contractItemDescs = GetContractItemDescriptions();
ColorizeContractItemBlocks(contractItemDescs);
FreezePanePivotTable(FREEZEPANE_ROW, 1);
FormatPivotTableNumbers();
ConfigureForPrinting(pivotTableSheet.Cells.Rows.Count);

PivotField field = pivotTable.RowFields[0];
field.IsAutoSort = true;
field.IsAscendSort = false;
field.AutoSortField = 1;

Style style = workBook.CreateStyle();
style.Font.Name = "Calibri";
style.Font.Size = 12;
pivotTable.FormatAll(style);
pivotTable.PivotTableStyleType = PivotTableStyleType.PivotTableStyleLight16;

sourceDataSheet.IsVisible = false;
}

Hi Clay,


Thank you for contacting Aspose support. Could you please share the sample spreadsheet for this scenario so we can provide the solution as per available Pivot Table?

Hi again,


I have found a sample on one of your other threads, and I am able to achieve your desired results while using the following piece of code. Please give it a try on your side and let us know your feedback. Please note, if you call PivotTable.FormatAll method after executing the following snippet, these changes will be overridden therefore it is best that you format the Pivot Table as desired and execute these statements at the end.

C#

var workbook = new Workbook(dir + “ABUELOS±+Produce+Usage±+from+Nov+2015_PROCESSED_1608.xlsx”);
var sheet = workbook.Worksheets[“PivotTableSheet”];
var pivot = sheet.PivotTables[0];
var range = pivot.TableRange1;
var style = sheet.Cells[range.StartRow + 1, range.StartColumn].GetStyle();
style.HorizontalAlignment = TextAlignmentType.Center;
for (int c = range.StartColumn; c < range.EndColumn + 1; c++)
{
pivot.Format(range.StartRow + 1, c, style);
}
pivot.RefreshData();
pivot.CalculateData();
workbook.Save((dir + “Aspose-output.xlsx”), SaveFormat.Xlsx);

Worked like a charm bracelet - thanks!

Hi,


Good to know that the suggested sample code helps you to accomplish your task. Feel free to write us back in case you have further comments or questions, we will be happy to assist you soon.

Thank you.