How can I change the format of the ColumnField column headings in EPPlus?

Question

I create a column field in EPPlus like so:

// Column field[s]
var monthYrColField = pivotTable.Fields["MonthYr"];
pivotTable.ColumnFields.Add(monthYrColField);

...that displays like so (the "201509" and "201510" columns):

I want those values to display instead as "Sep 15" and "Oct 15"

In Excel Interop it's done like this:

var monthField = pvt.PivotFields("MonthYr");
monthField.Orientation = XlPivotFieldOrientation.xlColumnField;
monthField.NumberFormat = "MMM yy";

...but in EPPlus the corresponding variable (monthYrColField) has no "NumberFormat" (or "Style") member.

I tried this:

pivotTableWorksheet.Column(2).Style.Numberformat.Format = "MMM yy";

...but, while it didn't complain or wreak havoc, also did not change the vals from "201509" and "201510"

How can I change the format of my ColumnField column headings in EPPlus from "untransformed" to "MMM yy" format?

UPDATE

For VDWWD:

As you can see by the comments, there are many things related to PivotTables which don't work or are hard to get to work in EPPlus; Excel Interop is a bear (and not a teddy or a Koala, but more like a grizzly) compared to EPPlus, but as to PivotTables, it seems that EPPlus is kind of half-baked to compared to Exterop's fried-to-a-crispness.

private void PopulatePivotTableSheet()
{
    string NORTHWEST_CORNER_OF_PIVOT_TABLE = "A6";
    AddPrePivotTableDataToPivotTableSheet();
    var dataRange = pivotDataWorksheet.Cells[pivotDataWorksheet.Dimension.Address];
    dataRange.AutoFitColumns();
    var pivotTable = pivotTableWorksheet.PivotTables.Add(
                        pivotTableWorksheet.Cells[NORTHWEST_CORNER_OF_PIVOT_TABLE], 
                        dataRange, 
                        "PivotTable");
    pivotTable.MultipleFieldFilters = true;
    pivotTable.GridDropZones = false;
    pivotTable.Outline = false;
    pivotTable.OutlineData = false;
    pivotTable.ShowError = true;
    pivotTable.ErrorCaption = "[error]";
    pivotTable.ShowHeaders = true;
    pivotTable.UseAutoFormatting = true;
    pivotTable.ApplyWidthHeightFormats = true;
    pivotTable.ShowDrill = true;

    // Row field[s]
    var descRowField = pivotTable.Fields["Description"];
    pivotTable.RowFields.Add(descRowField);

    // Column field[s]
    var monthYrColField = pivotTable.Fields["MonthYr"];
    pivotTable.ColumnFields.Add(monthYrColField);

    // Data field[s]
    var totQtyField = pivotTable.Fields["TotalQty"];
    pivotTable.DataFields.Add(totQtyField);

    var totPriceField = pivotTable.Fields["TotalPrice"];
    pivotTable.DataFields.Add(totPriceField);

    // Don't know how to calc these vals here, so had to put them on the data sheet
    var avgPriceField = pivotTable.Fields["AvgPrice"];
    pivotTable.DataFields.Add(avgPriceField);

    var prcntgOfTotalField = pivotTable.Fields["PrcntgOfTotal"];
    pivotTable.DataFields.Add(prcntgOfTotalField);

    // TODO: Get the sorting (by sales, descending) working:
    // These two lines don't seem that they would do so, but they do result in the items 
    // being sorted by (grand) total purchases descending
    //var fld = ((PivotField)pvt.PivotFields("Description"));
    //fld.AutoSort(2, "Total Purchases");
    //int dataCnt = pivotTable.ra //DataBodyRange.Columns.Count + 1;

    FormatPivotTable();
}

private void FormatPivotTable()
{
    int HEADER_ROW = 7;

    if (DateTimeFormatInfo.CurrentInfo != null)
        pivotTableWorksheet.Column(2).Style.Numberformat.Format = 
            DateTimeFormatInfo.CurrentInfo.YearMonthPattern;
    // Pivot Table Header Row - bold and increase height
    using (var headerRowFirstCell = pivotTableWorksheet.Cells[HEADER_ROW, 1])
    {
        headerRowFirstCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
        headerRowFirstCell.Style.Font.Bold = true;
        headerRowFirstCell.Style.Font.Size = 12;
        pivotTableWorksheet.Row(HEADER_ROW).Height = 25;
    }

    ColorizeContractItemBlocks(contractItemDescs);
    // TODO: Why is the hiding not working?
    HideItemsWithFewerThan1PercentOfSales();
}

score 2 · Accepted Answer · answered Oct 28 '16 at 13:44

2

You can use the build-in Date format YearMonthPattern. which would give september 2016 as format.

pivotTableWorksheet.Column(2).Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.YearMonthPattern;

If you really want MMM yy as pattern, you need to overwrite the culture format:

Thread.CurrentThread.CurrentCulture = new CultureInfo("nl-NL")
{
    DateTimeFormat = { YearMonthPattern = "MMM yy" }
};
pivotTableWorksheet.Column(2).Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.YearMonthPattern;

answered Oct 28 '16 at 13:44

VDWWD

35,079
22
62
79

Even using the second snippet, it made no difference; I'm guessing because this is a pivot table, and pivot tables in EPPlus seem to jive to a different bongoist. – B. Clay Shannon-B. Crow Raven Oct 28 '16 at 15:09
I marked it as answer, because it answers the question, even though it doesn't work on PivotTables. – B. Clay Shannon-B. Crow Raven Oct 28 '16 at 17:29
I'm not familiar with PivotTables. Is there a snippet somewhere that gets me those `1201509` values as a column label? Then I can experiment more. – VDWWD Nov 01 '16 at 21:27
Thanks, but never mind; I got it all working in Excel Interop, and will let it go at that. – B. Clay Shannon-B. Crow Raven Nov 01 '16 at 21:31
I wouldn't mind trying to get the EPPlus PivotTable to work, as the Excel Interop version takes 75 minutes to run, and EPPlus is normally much snappier. However, besides the "YYYYMM" formatting problem, there is also that of showing "Column Labels" where it should be "Months" and "Row Labels" where it should be "Description". I will post the relevant code as an update in case you are still willing to czech it out. – B. Clay Shannon-B. Crow Raven Nov 02 '16 at 20:54
1

No luck as of yet. Even if I specify the entire sheet as a special DateTime format, the pivot table always reverts back to default DateTime. I did found a boolean property `ApplyNumberFormats`, but according to [this document](http://www.nudoq.org/#!/Packages/EPPlus/EPPlus/ExcelPivotTable/P/ApplyNumberFormats) it is legacy. – VDWWD Nov 03 '16 at 16:57
Thanks; Excel Interop is a bear, and EPPlus is a cub - in both the positive sense of that word and the "negative" (it's not as "grown up" as the bear). Hopefully it doesn't take EPPlus 108 years to win the World Series of spreadsheet generation. – B. Clay Shannon-B. Crow Raven Nov 03 '16 at 17:09

score 1 · Answer 2 · answered Oct 16 '18 at 17:34

1

It doesn't seem that you can set the format on the field itself. You have to access through the pivot table object:

pivotTable.DataFields[0].Format = "MMM yy";

Any formatting applied to the underlying worksheet seems to be completely ignored.

answered Oct 16 '18 at 17:34

Steve H

111
1

How can I change the format of the ColumnField column headings in EPPlus?

UPDATE

2 Answers2