How to enable PivotTable details in .NET Excel Library

16 Aug 20263 minutes to read

The PivotTable “Show Details” (drilldown) behavior can be enabled by setting the EnableDrilldown implementation property to true using syncfusion XlsIO.

The following code examples show how to enable drilldown in XlsIO.

using (ExcelEngine excelEngine = new ExcelEngine())
{
    // Create application and set default version
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;

    // Open existing workbook with a PivotTable
    IWorkbook workbook = application.Workbooks.Open(Path.GetFullPath("Data/InputTemplate.xlsx"));
    IWorksheet worksheet = workbook.Worksheets[1];

    // Get the first PivotTable
    IPivotTable pivotTable = worksheet.PivotTables[0];

    // Enable drilldown (Show Details) so double-click opens detail rows
    (pivotTable as PivotTableImpl).EnableDrilldown = true;

    // Save changes
    workbook.SaveAs(Path.GetFullPath("Output/Output.xlsx"));
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
    // Windows-specific usage is identical for this feature
    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];

    // Enable pivot table drilldown
    (pivotTable as PivotTableImpl).EnableDrilldown = true;

    workbook.SaveAs("Output.xlsx");
}
Using excelEngine As New ExcelEngine()
    ' Create application and set default version
    Dim application As IApplication = excelEngine.Excel
    application.DefaultVersion = ExcelVersion.Xlsx

    ' Open existing workbook with a PivotTable
    Dim workbook As IWorkbook = application.Workbooks.Open("InputTemplate.xlsx")
    Dim worksheet As IWorksheet = workbook.Worksheets(1)

    ' Get the first PivotTable
    Dim pivotTable As IPivotTable = worksheet.PivotTables(0)

    ' Enable drilldown (Show Details)
    CType(pivotTable, PivotTableImpl).EnableDrilldown = True

    ' Save changes
    workbook.SaveAs("Output.xlsx")
End Using

A complete working example in C# is present on this GitHub page.