How to Expand/Collapse the rows in an existing Pivot Table in C#

Pivot table displays the data in sort, count total or give the average of the data stored in one table or spreadsheet. So it gives readers clear information of the data's trends and patterns rather than a large amount of similar data. Sometimes, there are so many rows in one pivot table and we may need to expand or collapse them to make the pivot table more clearly.

By using Spire.XLS for .NET, developers can create pivot table. This article will show you how to expand and collapse the rows in an existing Pivot table in C#.

Firstly, make sure that Spire.XLS for .NET (version7.5.5 or above) has been installed on your machine. And then, adds Spire.XLS.dll as reference in the downloaded Bin folder thought the below path: "..\Spire.XLS\Bin\NET4.0\ Spire.XLS.dll".

//Create a new excel document
Workbook book = new Workbook();

//load an excel document with Pivot table from the file
book.LoadFromFile("test.xlsx");

//Find the Pivot Table sheet 
Worksheet sheet = book.Worksheets["Pivot Table"];

//Get the data in Pivot Table
Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable pivotTable = sheet.PivotTables[0] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable;

//Calculate Data
pivotTable.CalculateData();

//Collapse the rows
(pivotTable.PivotFields["Vendor No"] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotField).HideItemDetail("1501", true);

//Expand the rows
(pivotTable.PivotFields["Vendor No"] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotField).HideItemDetail("1502", false);

//Save the document to file
book.SaveToFile("result.xlsx", ExcelVersion.Version2007);

Effective screenshots:

Collapse the rows in Pivot table in C#

Expand the rows in Pivot table in C#

Full codes:

using Spire.Xls;
namespace HighlightValues
{
    class Program
    {
     static void Main(string[] args)
{
           
  Workbook book = new Workbook();
  book.LoadFromFile("test.xlsx");
  Worksheet sheet = book.Worksheets["Pivot Table"];

  Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable pivotTable = sheet.PivotTables[0] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable;
  pivotTable.CalculateData();
  (pivotTable.PivotFields["Vendor No"] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotField).HideItemDetail("1501", true);
  (pivotTable.PivotFields["Vendor No"] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotField).HideItemDetail("1502", true);

  book.SaveToFile("result_1.xlsx", ExcelVersion.Version2007);

  (pivotTable.PivotFields["Vendor No"] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotField).HideItemDetail("1502", false);
   book.SaveToFile("result_2.xlsx", ExcelVersion.Version2007);
}


            }
        }

How to Expand/Collapse the rows in an existing Pivot Table in C#

See Also