Pivot Table Layout in Excel

14 Oct 202411 minutes to read

The PivotTableRowLayout enumeration can be used to set the pivot table row layout to Compact, Tabular, or Outline.

Compact Layout

The following code example illustrates how to apply a compact layout to a pivot table.

C# [Cross-platform]

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	FileStream inputStream = new FileStream(Path.GetFullPath(@"Data/InputTemplate.xlsx"), FileMode.Open, FileAccess.Read);
	IWorkbook workbook = application.Workbooks.Open(inputStream);
	IWorksheet worksheet = workbook.Worksheets[1];
	IPivotTable pivotTable = worksheet.PivotTables[0];

	//Set PivotTableRowLayout
	pivotTable.Options.RowLayout = PivotTableRowLayout.Compact;

	//Set BuiltInStyle
	pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium9;

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/Output.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.Dispose();
	inputStream.Dispose();
}

C# [Windows-specific]

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Open("InputTemplate.xlsx");
    IWorksheet worksheet = workbook.Worksheets[1];
    IPivotTable pivotTable = worksheet.PivotTables[0];

    //Set PivotTableRowLayout
    pivotTable.Options.RowLayout = PivotTableRowLayout.Compact;

    //Set BuiltInStyle
    pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium9;

    workbook.SaveAs("Output.xlsx");
}

VB.NET [Windows-specific]

Using excelEngine As New ExcelEngine()
    Dim application As IApplication = excelEngine.Excel
    application.DefaultVersion = ExcelVersion.Xlsx
    Dim workbook As IWorkbook = application.Workbooks.Open("InputTemplate.xlsx")
    Dim worksheet As IWorksheet = workbook.Worksheets(1)
    Dim pivotTable As IPivotTable = worksheet.PivotTables(0)

    'Set PivotTableRowLayout
    pivotTable.Options.RowLayout = PivotTableRowLayout.Compact

    'Set BuiltInStyle
    pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium9

    workbook.SaveAs("Output.xlsx")
End Using

A complete working example to apply a compact layout to a pivot table in C# is present on this GitHub page.

By executing the program, you will get the Excel file as below

Tabular Layout

The following code example illustrates how to apply a tabular layout to a pivot table.

C# [Cross-platform]

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	FileStream inputStream = new FileStream(Path.GetFullPath(@"Data/InputTemplate.xlsx"), FileMode.Open, FileAccess.Read);
	IWorkbook workbook = application.Workbooks.Open(inputStream);
	IWorksheet worksheet = workbook.Worksheets[1];
	IPivotTable pivotTable = worksheet.PivotTables[0];

	//Set PivotTableRowLayout
	pivotTable.Options.RowLayout = PivotTableRowLayout.Tabular;

	//Set BuiltInStyle
	pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium9;

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/Output.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.Dispose();
	inputStream.Dispose();
}

C# [Windows-specific]

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Open("InputTemplate.xlsx");
    IWorksheet worksheet = workbook.Worksheets[1];
    IPivotTable pivotTable = worksheet.PivotTables[0];

    //Set PivotTableRowLayout
    pivotTable.Options.RowLayout = PivotTableRowLayout.Tabular;

    //Set BuiltInStyle
    pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium9;

    workbook.SaveAs("Output.xlsx");
}

VB.NET [Windows-specific]

Using excelEngine As New ExcelEngine()
    Dim application As IApplication = excelEngine.Excel
    application.DefaultVersion = ExcelVersion.Xlsx
    Dim workbook As IWorkbook = application.Workbooks.Open("InputTemplate.xlsx")
    Dim worksheet As IWorksheet = workbook.Worksheets(1)
    Dim pivotTable As IPivotTable = worksheet.PivotTables(0)

    'Set PivotTableRowLayout
    pivotTable.Options.RowLayout = PivotTableRowLayout.Tabular

    'Set BuiltInStyle
    pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium9

    workbook.SaveAs("Output.xlsx")
End Using

A complete working example to apply a tabular layout to a pivot table in C# is present on this GitHub page.

By executing the program, you will get the Excel file as below

Outline Layout

The following code example illustrates how to apply a outline layout to a pivot table.

C# [Cross-platform]

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	FileStream inputStream = new FileStream(Path.GetFullPath(@"Data/InputTemplate.xlsx"), FileMode.Open, FileAccess.Read);
	IWorkbook workbook = application.Workbooks.Open(inputStream);
	IWorksheet worksheet = workbook.Worksheets[1];
	IPivotTable pivotTable = worksheet.PivotTables[0];

	//Set PivotTableRowLayout
	pivotTable.Options.RowLayout = PivotTableRowLayout.Outline;

	//Set BuiltInStyle
	pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium9;

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/Output.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.Dispose();
	inputStream.Dispose();
}

C# [Windows-specific]

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Open("InputTemplate.xlsx");
    IWorksheet worksheet = workbook.Worksheets[1];
    IPivotTable pivotTable = worksheet.PivotTables[0];

    //Set PivotTableRowLayout
    pivotTable.Options.RowLayout = PivotTableRowLayout.Outline;

    //Set BuiltInStyle
    pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium9;

    workbook.SaveAs("Output.xlsx");
}

VB.NET [Windows-specific]

Using excelEngine As New ExcelEngine()
    Dim application As IApplication = excelEngine.Excel
    application.DefaultVersion = ExcelVersion.Xlsx
    Dim workbook As IWorkbook = application.Workbooks.Open("InputTemplate.xlsx")
    Dim worksheet As IWorksheet = workbook.Worksheets(1)
    Dim pivotTable As IPivotTable = worksheet.PivotTables(0)

    'Set PivotTableRowLayout
    pivotTable.Options.RowLayout = PivotTableRowLayout.Outline

    'Set BuiltInStyle
    pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium9

    workbook.SaveAs("Output.xlsx")
End Using

A complete working example to apply a outline layout to a pivot table in C# is present on this GitHub page.

By executing the program, you will get the Excel file as below

Classic Layout

For classic layout, you can set the ShowGridDropZone property to true as below.

The following code example illustrates how to apply a classic layout to a pivot table.

C# [Cross-platform]

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	FileStream inputStream = new FileStream(Path.GetFullPath(@"Data/InputTemplate.xlsx"), FileMode.Open, FileAccess.Read);
	IWorkbook workbook = application.Workbooks.Open(inputStream);
	IWorksheet worksheet = workbook.Worksheets[1];
	IPivotTable pivotTable = worksheet.PivotTables[0];

	//Set classic layout
	(pivotTable.Options as PivotTableOptions).ShowGridDropZone = true;

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/ClassicLayout.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.Dispose();
	inputStream.Dispose();
}

C# [Windows-specific]

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;

  IWorkbook workbook = application.Workbooks.Open("InputTemplate.xlsx");
  IWorksheet worksheet = workbook.Worksheets[1];
  IPivotTable pivotTable = worksheet.PivotTables[0];

  //Set PivotTableRowLayout
  pivotTable.Options.RowLayout = PivotTableRowLayout.Tabular;

  //Set classic layout
  (pivotTable.Options as PivotTableOptions).ShowGridDropZone = true; 

  workbook.SaveAs("PivotTable_ClassicLayout.xlsx");

  //No exception will be thrown if there are unsaved workbooks
  excelEngine.ThrowNotSavedOnDestroy = false;
}

VB.NET [Windows-specific]

Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx

  Dim workbook As IWorkbook = application.Workbooks.Open("InputTemplate.xlsx")
  Dim sheet As IWorksheet = workbook.Worksheets(1)
  Dim pivotTable As IPivotTable = sheet.PivotTables(0)

  'Set PivotTableRowLayout
  pivotTable.Options.RowLayout = PivotTableRowLayout.Tabular

  'Set classic layout
  (TryCast(pivotTable.Options, PivotTableOptions)).ShowGridDropZone = True

  workbook.SaveAs("PivotTable_ClassicLayout.xlsx")
End Using

A complete working example to layout a pivot table classically in C# is present on this GitHub page.