How to freeze pivot table headers in .NET Excel Library

16 Aug 20262 minutes to read

The following code examples demonstrate freezing the header row and column of a PivotTable using C# (Cross-platform and Windows-specific) and VB.NET.

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Open(Path.GetFullPath(@"Data/Input.xlsx"));
    
    //Freeze row and column
    IWorksheet freezeSheet = workbook.Worksheets[1];
    IRange range = freezeSheet.PivotTables[0].Location;
    freezeSheet[range.Row + 1, range.Column + 1].FreezePanes();

    //Save the workbook
    workbook.SaveAs(Path.GetFullPath(@"Output/Output.xlsx"));
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Open("Input.xlsx");

    //Freeze row and column (use correct worksheet index or name)
    IWorksheet freezeSheet = workbook.Worksheets[1];
    IRange range = freezeSheet.PivotTables[0].Location;
    freezeSheet[range.Row + 1, range.Column + 1].FreezePanes();

    //Save the workbook
    workbook.SaveAs("Output.xlsx");
}
Using excelEngine As New ExcelEngine()
    Dim application As IApplication = excelEngine.Excel
    application.DefaultVersion = ExcelVersion.Xlsx
    Dim workbook As IWorkbook = application.Workbooks.Open("Input.xlsx")

    'Freeze row and column (use correct worksheet index or name)
    Dim freezeSheet As IWorksheet = workbook.Worksheets(1)
    Dim range As IRange = freezeSheet.PivotTables(0).Location
    freezeSheet(range.Row + 1, range.Column + 1).FreezePanes()

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

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