C#/VB.NET: Insert Page Breaks in Excel

In Excel, a page break is a separator that divides a worksheet into several different pages/segments for the purpose of better printing. By inserting page breaks where necessary, you can avoid misalignment of data and ensure a desired print result, which is especially useful when working with a large data set. This article will demonstrate how to programmatically insert horizontal or vertical page breaks in Excel using Spire.XLS for .NET.

Install Spire.XLS for .NET

To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.

PM> Install-Package Spire.XLS

Insert Horizontal Page Breaks in an Excel Worksheet

A horizontal page break is inserted between a selected row and the row above it. After insertion, the selected row will become the top row of the new page. With Spire.XLS for .NET, developers are allowed to use the Worksheet.HPageBreaks.Add(CellRange) method to insert horizontal page breaks. The detailed steps are as follows.

  • Create a Workbook instance.
  • Load a sample Excel file using Workbook.LoadFromFile() method.
  • Get a specified worksheet using Workbook.Worksheets[sheetIndex] property.
  • Add horizontal page break to a specified cell range using Worksheet.HPageBreaks.Add(CellRange) method.
  • Set view mode to Preview mode using Worksheet.ViewMode property.
  • Save the result file using Workbook.SaveToFile() method.
  • C#
  • VB.NET
using Spire.Xls;

namespace EditExcelComment
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook instance
            Workbook workbook = new Workbook();

            //Load a sample Excel document
            workbook.LoadFromFile("input.xlsx");

            //Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            //Set Excel page break horizontally
            sheet.HPageBreaks.Add(sheet.Range["A7"]);
            sheet.HPageBreaks.Add(sheet.Range["A18"]);

            //Set view mode to Preview mode
            sheet.ViewMode = ViewMode.Preview;

            //Save the result document
            workbook.SaveToFile("SetHorizontalPageBreak.xlsx");
        }
    }
}

C#/VB.NET: Insert Page Breaks in Excel

Insert Vertical Page Breaks in an Excel Worksheet

A vertical page break is inserted between a selected column and the column to its left. After insertion, the selected column will become the left most column of the new page. To insert vertical page breaks, developers can use the Worksheet.VPageBreaks.Add(CellRange) method offered by Spire.XLS for .NET offers. The detailed steps are as follows.

  • Create a Workbook instance.
  • Load a sample Excel file using Workbook.LoadFromFile() method.
  • Get a specified worksheet using Workbook.Worksheets[sheetIndex] property.
  • Add vertical page break to a specified cell range using Worksheet.VPageBreaks.Add(CellRange) method.
  • Set view mode to Preview mode using Worksheet.ViewMode property.
  • Save the result file using Workbook.SaveToFile() method.
  • C#
  • VB.NET
using Spire.Xls;

namespace EditExcelComment
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook instance
            Workbook workbook = new Workbook();

            //Load a sample Excel document
            workbook.LoadFromFile("input.xlsx");

            //Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            //Set Excel page break vertically
            sheet.VPageBreaks.Add(sheet.Range["B1"]);

            //Set view mode to Preview mode
            sheet.ViewMode = ViewMode.Preview;

            //Save the result document
            workbook.SaveToFile("SetVerticalPageBreak.xlsx");
        }
    }
}

C#/VB.NET: Insert Page Breaks in Excel

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.