Search code examples
c#excelpivot-tableepplusepplus-4

How can I pre-select the fields to be included in a PivotTable?

I've got this EPPlus code to create a PivotTable:

private void AddPivotTable()
{
    string colAlphaRowNum = string.Format("A{0}", locationWorksheet.Dimension.End.Row+5);
    ExcelAddressBase eab = locationWorksheet.Cells[colAlphaRowNum];
    ExcelRangeBase erb = locationWorksheet.Cells[6, 1, locationWorksheet.Dimension.End.Row, locationWorksheet.Dimension.End.Column];
    var pt = locationWorksheet.PivotTables.Add(eab, erb, "Pivotous");

    pt.MultipleFieldFilters = true;
    pt.RowGrandTotals = true;
    pt.ColumGrandTotals = true;
    pt.Compact = true;
    pt.CompactData = true;
    pt.GridDropZones = false;
    pt.Outline = false;
    pt.OutlineData = false;
    pt.ShowError = true;
    pt.ErrorCaption = "[error]";
    pt.ShowHeaders = true;
    pt.UseAutoFormatting = true;
    pt.ApplyWidthHeightFormats = true;
    pt.ShowDrill = true;
    pt.DataOnRows = false;

    pt.FirstHeaderRow = 1;  // first row has headers
    pt.FirstDataCol = 1;    // first col of data
    pt.FirstDataRow = 2;    // first row of data

    pt.TableStyle = TableStyles.Medium6; // There is a "custom" and several Dark, Light, and Medium options
}

This kinda-sorta works; I get this on the sheet:

enter image description here

If I then (manually) select all six available fields in the "PivotTable Field list" in the NE corner of the sheet, the PivotTable changes its appearnce to this:

enter image description here

That's pretty good, because the user can "mess around" with it - selecting any subset of available data for each field. But I'd like the PivotTable to start off in this state, rather than it be necessary for the user to manually select the fields.

How is that possible?

NOTE: I had similar problems with Excel Interop, and discovered that "things" are much easier using EPPlus as contrasted with that; still, though, programmatically selecting fields seems a challenge...

Solution

  • It is much easier than Excel Interop; here's all it takes:

    pt.RowFields.Add(pt.Fields[0]);
pt.RowFields.Add(pt.Fields[1]);
pt.RowFields.Add(pt.Fields[2]);
pt.RowFields.Add(pt.Fields[3]);
pt.RowFields.Add(pt.Fields[4]);
pt.RowFields.Add(pt.Fields[5]);