How can I override the column label text on a Pivot Table (Aspose Cells)?

Question

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.):

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?

UPDATE

It seems as if the code offered from the answer's link should work; I can loop through it, and the values are right, but nothing changes. This is my code:

// Get "10/1/2015" to display as "Oct 15"
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);
}

UPDATE 2

Even this doesn't do anything - the value of C7 is still "10/1/2015"

Cell cell = pivotTableSheet.Cells["C7"];
cell.PutValue("Oct 15");

score 1 · Accepted Answer · answered Dec 03 '16 at 12:53

1

You can just replace the GetMonthAsMMM function by some formula like WorksheetFunction.TEXT(monthPortion & "/01/" & yearPortion , "mmm")

answered Dec 03 '16 at 12:53

nightcrawler23

2,056
1
14
22

score 0 · Answer 2 · answered Nov 29 '16 at 17:55

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";
}

If somebody wants to answer this (even if just using my answer as a basis), I would like to award the bounty rather than let it just vanish into the labyrinthine depths of Bounty Wasteland (UN Membership pending). — B. Clay Shannon-B. Crow Raven, Nov 30 '16 at 18:20
Can't you just replace the GetMonthAsMMM function by some formula like WorksheetFunction.TEXT(monthPortion & "/01/" & yearPortion , "mmm") — nightcrawler23, Dec 02 '16 at 09:42
Maybe, but it's already fixed with the above, so I'm not going to revisit it now. I have other havoc to wreak. — B. Clay Shannon-B. Crow Raven, Dec 02 '16 at 16:12
@nightcrawler23: Nevertheless, make that an answer, and I will give you the bounty - somebody may as well have it! It's expiring... — B. Clay Shannon-B. Crow Raven, Dec 02 '16 at 21:53

How can I override the column label text on a Pivot Table (Aspose Cells)?

UPDATE

UPDATE 2

2 Answers2