Set Page Break (Horizontal and Vertical) in Excel

Excel page break is pretty useful especially when there are huge data in Excel file and people want to print it. This section will introduce a solution to set Excel page break both in a horizontal and vertical way with C#, VB.NET by a .NET Excel component Spire.XLS for .NET.

In my solution, after loading an Excel file from system, a method provided by Spire.XLs: Workbook.Worksheets.HPageBreaks.VPageBreaks.Add(CellRange range) can help us insert page break horizontal while when we replace the HPageBreaks in the method with VPageBreaks, we can set page break to be vertical. After adding page break, we can set view mode as Page Break Preview which displays excel as it will print. In order to check clearly, we have three view modes: Normal, Layout and Preview which can be set by the enum Spire.Xls.ViewMode. Now, please see the effect of page break as below picture:

Set Excel Page Break

Here you can download Spire.XLS for .NET and add Spire.Xls dll in your downloaded Bin folder after installing it. Then, please see below code. The Excel file I use is the same with the Excel shown in above picture.

Set Excel Page Break Horizontally:

[C#]
            workbook.Worksheets[0].HPageBreaks.Add(sheet.Range["A7"]);
            workbook.Worksheets[0].HPageBreaks.Add(sheet.Range["A13"]);
            workbook.Worksheets[0].ViewMode = ViewMode.Preview;
[VB.NET]
          workbook.Worksheets(0).HPageBreaks.Add(sheet.Range("A7"))
          workbook.Worksheets(0).HPageBreaks.Add(sheet.Range("A13"))
          workbook.Worksheets(0).ViewMode = ViewMode.Preview

Set Excel Page Break Vertically:

[C#]
            workbook.Worksheets[0].VPageBreaks.Add(sheet.Range["B1"]);
            workbook.Worksheets[0].VPageBreaks.Add(sheet.Range["C1"]);
            workbook.Worksheets[0].ViewMode = ViewMode.Preview;
[VB.NET]
          workbook.Worksheets(0).VPageBreaks.Add(sheet.Range("B1"))
          workbook.Worksheets(0).VPageBreaks.Add(sheet.Range("C1"))
          workbook.Worksheets(0).ViewMode = ViewMode.Preview