Hiding subtotals in pivot table in epplus

Question

I'm using EPPLUS to generate my pivot table (see this SO post). I have the following code

//group by fields
foreach (string row in _GroupByColumns)
{
    ExcelPivotTableField field = pivotTable.Fields[row];

    //field formating
    field.Sort = eSortType.Ascending;
    field.Outline = false;
    field.Compact = false;
    field.ShowAll = false;
    field.SubtotalTop = false; //doesn't work, subtotals still there when excel is opened

    //doesn't work, will result in "unreadable content" error when Excel is opened
    //field.SubTotalFunctions = eSubTotalFunctions.None;

    //add it to the pivot
    pivotTable.RowFields.Add(field);
}

_GroupByColumns is a List<string> that contains my group by columns.

I thought field.SubtotalTop = false; would hide subtotals. It doesn't. field.SubTotalFunctions = eSubTotalFunctions.None; results in "invalid data" error when I open my Excel. What else can I try?

Answer 1

I know this is an old question, but I'm adding this answer in case anybody Googles here.

You need to set the SubTotalFunctions property after adding the field to the RowFields collection. This is because adding it to the collection changes the XML node that SubTotalFunctions makes adjustments to. This will work...

ExcelPivotTableField field = pivotTable.Fields[row];

pivotTable.RowFields.Add(field);//<-- ADD IT FIRST

//field formating
field.Sort = eSortType.Ascending;
field.Outline = false;
field.Compact = false;
field.ShowAll = false;
field.SubtotalTop = false;
field.SubTotalFunctions = eSubTotalFunctions.None;//<-- THIS WILL WORK NOW