How to set pivot table table report table to tabular form in EPPlus?

Question

Looking at the samples that come with EPPlus, I managed to create a pivot table, but I can’t set the correct report layout for it. I want this to be “tabular,” not “outline” or whatever. It seems to me that EPPlus does not support it now, but maybe I missed something?

Answer 1

It turned out to be a lot easier than I thought ... Setting “everything” to “false” is a table displayed as a table. 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; 

This way I get a pivot table with a table 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 like that, there is some context, here is my code to create the bar. It is disconnected from 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;