How to add and remove page breaks in a worksheet?

18 Oct 20244 minutes to read

Page breaks in Excel separate large datasets into different pages for better organization. You can add or remove horizontal page breaks using HPageBreaks and vertical page breaks using VPageBreaks with the IWorksheet interface.

The following code example illustrates how to add or remove page breaks in a worksheet.

C# [Cross-platform]

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Create(1);
    IWorksheet worksheet = workbook.Worksheets[0];

    //Adding text to a worksheet range
    worksheet.Range["A1:J15"].Text = "Text";

    //Adding horizontal page breaks in the worksheet
    worksheet.HPageBreaks.Add(worksheet.Range["A5"]);
    worksheet.HPageBreaks.Add(worksheet.Range["C10"]);

    //Adding vertical page breaks in the worksheet
    worksheet.VPageBreaks.Add(worksheet.Range["B5"]);
    worksheet.VPageBreaks.Add(worksheet.Range["D10"]);

    //Removing vertical page break
    worksheet.VPageBreaks.Remove(worksheet.Range["D10"]);

    //Setting sheet view to see the page breaks
    worksheet.View = SheetView.PageBreakPreview;

    //Saving the workbook as stream
    FileStream outputStream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
    workbook.SaveAs(outputStream);

    //Dispose streams
    outputStream.Dispose();
}

C# [Windows-specific]

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Create(1);
    IWorksheet worksheet = workbook.Worksheets[0];

    //Adding text to a worksheet range
    worksheet.Range["A1:J15"].Text = "Text";

    //Adding horizontal page breaks in the worksheet
    worksheet.HPageBreaks.Add(worksheet.Range["A5"]);
    worksheet.HPageBreaks.Add(worksheet.Range["C10"]);

    //Adding vertical page breaks in the worksheet
    worksheet.VPageBreaks.Add(worksheet.Range["B5"]);
    worksheet.VPageBreaks.Add(worksheet.Range["D10"]);

    //Removing vertical page break
    worksheet.VPageBreaks.Remove(worksheet.Range["D10"]);

    //Setting sheet view to see the page breaks
    worksheet.View = SheetView.PageBreakPreview;

    //Saving the workbook
    workbook.SaveAs("Output.xlsx");
}

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.Create(1)
    Dim worksheet As IWorksheet = workbook.Worksheets(0)

    'Adding text to a worksheet range
    worksheet.Range("A1:J15").Text = "Text"

    'Adding horizontal page breaks in the worksheet
    worksheet.HPageBreaks.Add(worksheet.Range("A5"))
    worksheet.HPageBreaks.Add(worksheet.Range("C10"))

    'Adding vertical page breaks in the worksheet
    worksheet.VPageBreaks.Add(worksheet.Range("B5"))
    worksheet.VPageBreaks.Add(worksheet.Range("D10"))

    'Removing vertical page break
    worksheet.VPageBreaks.Remove(worksheet.Range("D10"))

    'Setting sheet view to see the page breaks
    worksheet.View = SheetView.PageBreakPreview

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