How to set Pivot table Report Layout to tabular in EPPlus?

Question

Looking at the samples that come with EPPlus I have managed to create the pivot table, but I am unable to set the correct report layout for it. I want it to be 'tabular', not 'outline' or whatever. To me it looks like EPPlus doesn't support this now, but perhaps I'm missing something?

Answer 1

This turned out to be way easier than I thought... By setting 'everything' to 'false' it the table rendered as Tabular. So basically:

pivotTable.Compact = false;
pivotTable.CompactData = false;
pivotTable.Indent = 0;
pivotTable.RowGrandTotals = false;
pivotTable.UseAutoFormatting = true;
pivotTable.ShowMemberPropertyTips = false;
pivotTable.DataOnRows = false;

field.Outline = false;
field.Compact = false;
field.ShowAll = false;
field.SubtotalTop = false;

That way I get the pivot table with tabular layout.

Answer 2

Here is the solution. Explicitly set all fields (including hidden fields) compact and outline properties to false.

(from pf in pivot.Fields
             select pf).ToList().ForEach(f =>
                 {
                     f.Compact = false;
                     f.Outline = false;
                 });

Just so there is some context, here is my code to generate the pivot. It is run off metadata but the concept is the same.

        var pivotTabName = "Pivot";
        if (tab.Form.FormTabs.Count(ft => ft.FormTabPivotFields.Any()) > 1)
            pivotTabName = tab.DisplayName + " " + pivotTabName;

        var sheet = package.Workbook.Worksheets.Add(pivotTabName);

        const int pivotRow = 7;
        const int pivotCol = 1;

        var dataSourceRange = table.WorkSheet.Workbook.Names[table.Name + "_PivotSource"];
        var pivot = sheet.PivotTables.Add(sheet.Cells[pivotRow, pivotCol], dataSourceRange.Worksheet.Cells[dataSourceRange.Address],
                              pivotTabName.Replace(" ", string.Empty));

        const bool outline = false;
        const bool compact = false;
        const bool showAll = false;

        pivot.Compact = compact;
        pivot.CompactData = compact;
        pivot.Outline = outline;
        pivot.OutlineData = outline;
        pivot.Indent = 0;
        pivot.UseAutoFormatting = true;
        pivot.ShowMemberPropertyTips = false;
        pivot.DataOnRows = false;
        pivot.RowGrandTotals = false;
        pivot.ShowDrill = false;
        pivot.EnableDrill = false;
        pivot.RowGrandTotals = false;
        pivot.ColumGrandTotals = true;
        pivot.MultipleFieldFilters = true;

        (from pf in tab.FormTabPivotFields
         where pf.PivotFieldType.Name == "Page"
         orderby pf.DisplayOrder
         select pf.FormTabFactVcTypeDomain != null ? pf.FormTabFactVcTypeDomain.DisplayColumnName : pf.FormTabMeasureTypeColumn.DisplayColumnName).ToList().ForEach(fieldName =>
             {
                 var fld = pivot.PageFields.Add(pivot.Fields[fieldName]);
                 fld.Compact = compact;
                 fld.Outline = outline;
                 fld.ShowAll = showAll;
                 fld.SubtotalTop = false;
                 fld.SubTotalFunctions = eSubTotalFunctions.None;
             });

        (from pf in tab.FormTabPivotFields
         where pf.PivotFieldType.Name == "Row"
         orderby pf.DisplayOrder
         select pf.FormTabFactVcTypeDomain != null ? pf.FormTabFactVcTypeDomain.DisplayColumnName : pf.FormTabMeasureTypeColumn.DisplayColumnName).ToList().ForEach(fieldName =>
         {
             var fld = pivot.RowFields.Add(pivot.Fields[fieldName]);
             fld.Compact = compact;
             fld.Outline = outline;
             fld.ShowAll = showAll;
             fld.SubtotalTop = false;
             fld.SubTotalFunctions = eSubTotalFunctions.None;
         });

        (from pf in tab.FormTabPivotFields
         where pf.PivotFieldType.Name == "Column"
         orderby pf.DisplayOrder
         select pf.FormTabFactVcTypeDomain != null ? pf.FormTabFactVcTypeDomain.DisplayColumnName : pf.FormTabMeasureTypeColumn.DisplayColumnName).ToList().ForEach(fieldName =>
         {
             var fld = pivot.ColumnFields.Add(pivot.Fields[fieldName]);
             fld.Compact = compact;
             fld.Outline = outline;
             fld.ShowAll = showAll;
             fld.SubtotalTop = false;
             fld.SubTotalFunctions = eSubTotalFunctions.None;
         });

        (from pf in tab.FormTabPivotFields
         where pf.PivotFieldType.Name == "Data"
         orderby pf.DisplayOrder
         select new
             {
                 FieldName = pf.FormTabFactVcTypeDomain != null ? pf.FormTabFactVcTypeDomain.DisplayColumnName : pf.FormTabMeasureTypeColumn.DisplayColumnName,
                 Format = pf.FormTabMeasureTypeColumn != null ?
                    (pf.FormTabMeasureTypeColumn.MeasureFormatIndex == 1 ? pf.FormTab.MeasureColumnExcelNumberFormat1 : pf.FormTab.MeasureColumnExcelNumberFormat2)
                    : "General"
             }).ToList().ForEach(md =>
             {
                 var df = pivot.DataFields.Add(pivot.Fields[md.FieldName]);
                 df.Format = md.Format;
                 df.Field.Compact = compact;
                 df.Field.Outline = outline;
             });

        (from pf in pivot.Fields
             where pf.Axis == ePivotFieldAxis.None && pf.IsDataField == false
             select pf).ToList().ForEach(f =>
                 {
                     f.Compact = compact;
                     f.Outline = outline;
                 });

        // apply pivot table styling
        pivot.TableStyle = TableStyles.Medium15;