Tuesday, 24 April 2012 08:37

Set Page Break (Horizontal and Vertical) in Excel

Written by  support iceblue
Rate this item
(0 votes)

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

Additional Info

  • tutorial_title: Set Page Break
Last modified on Friday, 11 July 2014 08:47