Hide subtotals in pivot table in epplus
I am using EPPLUS to create a pivot table ( see this 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
- this List<string>
that contains my column group.
I thought it would field.SubtotalTop = false;
hide the subtotals. This is not true. field.SubTotalFunctions = eSubTotalFunctions.None;
results in an "invalid data" error when opening my Excel. What else can I try?
source to share
I know this is an old question, but I am adding this answer in case anyone on Google.
You need to set the SubTotalFunctions property after adding the field to the RowFields collection. This is because adding it to the collection modifies the XML node that SubTotalFunctions is executing. 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
source to share