How can I override the column label text on a Pivot Table?

cshannon · November 23, 2016, 1:41pm

I am using source data that contains values such as "10/1/2015", "11/1/2015" etc.

Understandably, when I add these values as a Column PivotFieldType:

pivotTable.AddFieldToArea(PivotFieldType.Column, MONTHYR_COLUMN);

pivotTable.ColumnHeaderCaption = "Months";

...the values in the columns correspond to those extracted from the raw data ("10/1/2015", "11/1/2015" etc.), as seen in the screen shot below.

However, rather than that textual representation ("10/1/2015", "11/1/2015" etc.), I want the labels to be "Oct 15", "Nov 15", etc.

How can I accomplish that?

Will I have to change the data before writing it to the data sheet (from "10/1/2015" to "Oct 15", etc.) or is there a way I can interrupt the column-label-writing process on the Pivot Table?

babar.raza · November 23, 2016, 11:01pm

Hi Clay,

Thank you for contacting Aspose support.

You may use the PivotTable.Format method as demonstrated below. Attached is the resultant spreadsheet for your reference.

C#

Workbook wb = new Workbook(dir + “ABUELOS±+Produce+Usage±+from+Oct+2015_PROCESSED_1603.xlsx”);
int sheetIndex = wb.Worksheets.Add();
PivotTableCollection pts = wb.Worksheets[sheetIndex].PivotTables;
int ptIndex = pts.Add("=PivotData!$A$1:$J$415", “A5”, “testPt”);
PivotTable pt = pts[ptIndex];
pt.AddFieldToArea(PivotFieldType.Row, “Description”);
pt.AddFieldToArea(PivotFieldType.Column, “MonthYr”);
pt.AddFieldToArea(PivotFieldType.Data, “TotalQty”);
pt.DataFields[0].DisplayName = “Total Packages”;
pt.AddFieldToArea(PivotFieldType.Data, “TotalPrice”);
pt.DataFields[1].DisplayName = “Total Purchases”;
pt.AddCalculatedField(“Average Price”, “=TotalPrice/TotalQty”, true);
pt.AddCalculatedField(“PercentOfTotal”, “=TotalPrice”, true);
pt.DataFields[3].DisplayName = “Percentage of Total”;
pt.DataFields[3].DataDisplayFormat = PivotFieldDataDisplayFormat.PercentageOfColumn;
pt.RowFields[0].IsAutoSubtotals = false;
pt.RowFields[0].ShowInOutlineForm = true;
pt.RowFields[0].ShowCompact = true;
PivotField field = pt.RowFields[0];
field.IsAutoSort = true;
field.IsAscendSort = false;
field.AutoSortField = 1;
pt.PivotTableStyleType = PivotTableStyleType.PivotTableStyleLight16;

pt.RefreshDataFlag = true;
pt.RefreshData();
pt.CalculateData();
pt.RefreshDataFlag = false;

Style columnStyle = new CellsFactory().CreateStyle();
columnStyle.Custom = “mmm yy”;
CellArea columnRange = pt.ColumnRange;
for (int c = columnRange.StartColumn; c < columnRange.EndColumn; c++)
{
pt.Format(columnRange.StartRow + 1, c, columnStyle);
}

wb.Save(dir + “out.xlsx”);

cshannon · November 25, 2016, 3:38pm

Thanks - awesome work!

amjad.sahi · November 26, 2016, 3:23am

Hi,

Good to know that your issue is sorted out now. Feel free to write back in case you have further comments/queries, we will be happy to assist you soon.

Thank you.

amjad.sahi · November 28, 2016, 12:15pm

Hi,

I used a template file (containing a PivotTable in it) and used the following simplest code segment to apply your desired formatting to column fields of the existing PivotTable, it works fine. Please also find attached the template Excel file and output Excel file for your reference:

e.g

Sample code:

Workbook wb = new Workbook(“e:\test2\Bk_Source1.xlsx”);

PivotTableCollection pts = wb.Worksheets[1].PivotTables;

PivotTable pt = pts[0];

pt.RefreshDataFlag = true;

pt.RefreshData();

pt.CalculateData();

pt.RefreshDataFlag = false;

Style columnStyle = new CellsFactory().CreateStyle();

columnStyle.Custom = “mmm yy”;

CellArea columnRange = pt.ColumnRange;

for (int c = columnRange.StartColumn; c < columnRange.EndColumn; c++)

{

pt.Format(columnRange.StartRow + 1, c, columnStyle);

}

wb.Save(“e:\test2\out1Source1.xlsx”);

Thank you.

cshannon · November 28, 2016, 3:26pm

Still doesn’t work; even when I tried to “brute force” a value in a particular cell, it didn’t work - it still says “10/1/15” in C7 even with the following code:

// made no difference

//pivotTable.RefreshDataFlag = true;

//pivotTable.RefreshData();

//pivotTable.CalculateData();

//pivotTable.RefreshDataFlag = false;

//Style columnStyle = new CellsFactory().CreateStyle();

//columnStyle.Custom = “mmm yy”;

//CellArea columnRange = pivotTable.ColumnRange;

//for (int c = columnRange.StartColumn; c < columnRange.EndColumn; c++)

//{

// pivotTable.Format(columnRange.StartRow + 1, c, columnStyle);

//}

Cell cell = pivotTableSheet.Cells[“C7”];

cell.PutValue(“Oct 15”);

shakeel.faiz · November 28, 2016, 8:23pm

Hi,

Thanks for your posting and using Aspose.Cells.

In order to investigate this issue, please provide us your original excel file. Thanks for your cooperation in this regard and have a good day.

cshannon · November 29, 2016, 10:30am

I am attaching an Excel spreadsheet that shows this problem, and the code for generating the spreadsheet, too.

cshannon · November 29, 2016, 11:57am

I bit the bullet and changed the source data (this works):

I solved the problem by changing the source data from which the Pivot Table is generated, like so:

private void AddPivotData(String ItemCode, String ItemDescription, String Unit, String MonthYear, int Quantity, Decimal TotalPrice, Boolean IsContractItem, Double PercentageOfTotal, Double MonthlyPercentage)

{

. . .

cell = sourceDataSheet.Cells[_lastRowAddedPivotTableData, 3];

cell.PutValue(ConvertToMMMYY(MonthYear));

. . .

}

// Comes in formatted YYYYMM (such as “201510”), returned as MMM YY (such as “Oct 15”)

private object ConvertToMMMYY(string MonthYear)

{

string yearPortion = MonthYear.Substring(0, 4);

string monthPortion = MonthYear.Substring(4, 2);

string yearSansCentury = yearPortion.Substring(2, 2);

string monthNumAsStr = GetMonthAsMMM(monthPortion);

return string.Format("{0}{1}", monthNumAsStr, yearSansCentury);

}

private string GetMonthAsMMM(string monthPortion)

{

if (monthPortion == “01”) return “Jan”;

if (monthPortion == “02”) return “Feb”;

if (monthPortion == “03”) return “Mar”;

if (monthPortion == “04”) return “Apr”;

if (monthPortion == “05”) return “May”;

if (monthPortion == “06”) return “Jun”;

if (monthPortion == “07”) return “Jul”;

if (monthPortion == “08”) return “Aug”;

if (monthPortion == “09”) return “Sep”;

if (monthPortion == “10”) return “Oct”;

if (monthPortion == “11”) return “Nov”;

if (monthPortion == “12”) return “Dec”;

return “Unrecognized Month Num”;

}

shakeel.faiz · November 30, 2016, 8:04am

Hi,

Thanks for your posting and using Aspose.Cells.

We have looked into this issue and found, it is not a Style issue. You have to change the value of cell C7 using a formula bar in Microsoft Excel but that even does not work in Aspose.Cells.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as

CELLSNET-44957 - Unable to change the value of cell C7 which is inside the PivotTable

I have attached the actual output excel file, expected excel file and the screenshot highlighting this issue for a reference.

cshannon · November 30, 2016, 10:06am

I was able to work around it with this:

<p style=“margin: 0px 0px 1em; padding: 0px; border: 0px; font-size: 15px; clear: both; color: rgb(36, 39, 41); font-family: Arial, “Helvetica Neue”, Helvetica, sans-serif; background-color: rgb(255, 255, 255);”>I solved the problem by changing the source data from which the Pivot Table is generated, like so:<pre style=“margin-top: 0px; margin-bottom: 1em; padding: 5px; border: 0px; font-size: 13px; width: auto; max-height: 600px; overflow: auto; font-family: Consolas, Menlo, Monaco, “Lucida Console”, “Liberation Mono”, “DejaVu Sans Mono”, “Bitstream Vera Sans Mono”, “Courier New”, monospace, sans-serif; background-color: rgb(239, 240, 241); word-wrap: normal; color: rgb(36, 39, 41);”><code style=“margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, “Lucida Console”, “Liberation Mono”, “DejaVu Sans Mono”, “Bitstream Vera Sans Mono”, “Courier New”, monospace, sans-serif; white-space: inherit;”>private void AddPivotData(String ItemCode, String ItemDescription, String Unit, String MonthYear, int Quantity, Decimal TotalPrice, Boolean IsContractItem, Double PercentageOfTotal, Double MonthlyPercentage)
{
. . .
cell = sourceDataSheet.Cells[_lastRowAddedPivotTableData, 3];
cell.PutValue(ConvertToMMMYY(MonthYear));
. . .
}

// Comes in formatted YYYYMM (such as “201510”), returned as MMM YY (such as “Oct 15”)
private object ConvertToMMMYY(string MonthYear)
{
string yearPortion = MonthYear.Substring(0, 4);
string monthPortion = MonthYear.Substring(4, 2);
string yearSansCentury = yearPortion.Substring(2, 2);
string monthNumAsStr = GetMonthAsMMM(monthPortion);
return string.Format("{0}{1}", monthNumAsStr, yearSansCentury);
}

private string GetMonthAsMMM(string monthPortion)
{
if (monthPortion == “01”) return “Jan”;
if (monthPortion == “02”) return “Feb”;
if (monthPortion == “03”) return “Mar”;
if (monthPortion == “04”) return “Apr”;
if (monthPortion == “05”) return “May”;
if (monthPortion == “06”) return “Jun”;
if (monthPortion == “07”) return “Jul”;
if (monthPortion == “08”) return “Aug”;
if (monthPortion == “09”) return “Sep”;
if (monthPortion == “10”) return “Oct”;
if (monthPortion == “11”) return “Nov”;
if (monthPortion == “12”) return “Dec”;
return “Unrecognized Month Num”;
}

shakeel.faiz · November 30, 2016, 7:42pm

Hi,

Thanks for your posting and using Aspose.Cells.

That’s great, you were able to sort out this issue. However, the ticket is still open. If there is any update for you, we will let you know asap.