Configure Page Breaks | Document Solutions for Excel, .NET Edition

Configure Page Breaks

In This Topic

DsExcel .NET allows users to configure the vertical and horizontal page breaks by using the VPageBreaks property and HPageBreaks property of the IWorksheet interface. You can also determine whether to adjust the horizontal and vertical page breaks or keep them fixed (while performing the insert and delete operations on the rows and columns) by using the FixedPageBreaks property of the IWorksheet interface.

This feature is useful especially when users need to print different reports from Excel to a PDF file. With the option to choose whether to adjust page breaks or keep them fixed, users can specify whether each section appears on a separate page or starts from a new page whenever any rows and columns are inserted or deleted in a spreadsheet.

If the FixedPageBreaks property is set to false (this is the default behavior), then:

The horizontal and vertical page breaks are adjusted when the rows and columns are inserted or deleted from the worksheet.
The row or column index of the page break is increased or decreased according to the inserted and deleted rows or columns based on the following conditions:
- If the inserted or deleted rows or columns exist after the page break, the row or column index of the page break remains unchanged.
- If the deleted rows or columns are present before the page break, the row or column index of the page break is decreased accordingly.
- If the deleted rows or columns contain the page break, the page break will be removed.

If the FixedPageBreaks property is set to true, the row or column index of page breaks are not changed even after inserting or deleting rows or columns. Further, the horizontal and vertical page breaks are considered "fixed" and the page breaks can't be adjusted in this scenario.

Refer to the following example code in order to configure page breaks for customized printing.

C#	Copy Code
// Initialize workbook Workbook workbook = new Workbook(); // Fetch default worksheet IWorksheet worksheet = workbook.Worksheets[0]; object[,] data = new object[,]{ {"Name", "City", "Birthday", "Sex", "Weight", "Height", "Age"}, {"Bob", "NewYork", new DateTime(1968, 6, 8), "male", 80, 180, 56}, {"Betty", "NewYork", new DateTime(1972, 7, 3), "female", 72, 168, 45}, {"Gary", "NewYork", new DateTime(1964, 3, 2), "male", 71, 179, 50}, {"Hunk", "Washington", new DateTime(1972, 8, 8), "male", 80, 171, 59}, {"Cherry", "Washington", new DateTime(1986, 2, 2), "female", 58, 161, 34}, {"Coco", "Virginia", new DateTime(1982, 12, 12), "female", 58, 181, 45}, {"Lance", "Chicago", new DateTime(1962, 3, 12), "female", 49, 160, 57}, { "Eva", "Washington", new DateTime(1993, 2, 5), "female", 71, 180, 81}}; // Set data worksheet.Range["A1:G9"].Value = data; // Add a horizontal page break before the fourth row var hPageBreak = worksheet.HPageBreaks.Add(worksheet.Range["F4"]); // Add a vertical page break before the third column var vPageBreak = worksheet.VPageBreaks.Add(worksheet.Range["F3"]); // Saving workbook to xlsx workbook.Save(@"PageBreaks.xlsx", SaveFileFormat.Xlsx); // Delete rows and columns before the page breaks, the page breaks will be adjusted worksheet.Range["1:2"].Delete(); // the hPageBreak is before the fourth row worksheet.Range["B:C"].Delete(); // the vPageBreak is before the fourth column // Set the page breaks are fixed, it will not be adjusted when inserting/ deleting rows/ columns worksheet.FixedPageBreaks = true; // Saving the edited workbook to xlsx workbook.Save(@"PageBreaksAfterDeletingRows&ColumnsWithFixedPageBreaks.xlsx", SaveFileFormat.Xlsx); // Delete rows and columns after the page breaks, the page breaks will not be adjusted worksheet.Range["1:2"].Delete(); // the hPageBreak is still before the fourth row worksheet.Range["B:C"].Delete(); // the vPageBreak is still before the fourth column // Insert rows worksheet.Range["A3:A5"].EntireRow.Insert(); // Inserting rows after deleting row and column ranges // Saving the finalized workbook to xlsx workbook.Save(@"PageBreakAfterDeletingRows&Columns.xlsx", SaveFileFormat.Xlsx);

Copy Code

// Initialize workbook
Workbook workbook = new Workbook();
        
// Fetch default worksheet 
IWorksheet worksheet = workbook.Worksheets[0];

object[,] data = new object[,]{
{"Name", "City", "Birthday", "Sex", "Weight", "Height", "Age"},
{"Bob", "NewYork", new DateTime(1968, 6, 8), "male", 80, 180, 56},
{"Betty", "NewYork", new DateTime(1972, 7, 3), "female", 72, 168, 45},
{"Gary", "NewYork", new DateTime(1964, 3, 2), "male", 71, 179, 50},
{"Hunk", "Washington", new DateTime(1972, 8, 8), "male", 80, 171, 59},
{"Cherry", "Washington", new DateTime(1986, 2, 2), "female", 58, 161, 34},
{"Coco", "Virginia", new DateTime(1982, 12, 12), "female", 58, 181, 45},
{"Lance", "Chicago", new DateTime(1962, 3, 12), "female", 49, 160, 57},
{ "Eva", "Washington", new DateTime(1993, 2, 5), "female", 71, 180, 81}};

// Set data
worksheet.Range["A1:G9"].Value = data;

// Add a horizontal page break before the fourth row
var hPageBreak = worksheet.HPageBreaks.Add(worksheet.Range["F4"]);
        
// Add a vertical page break before the third column
var vPageBreak = worksheet.VPageBreaks.Add(worksheet.Range["F3"]);

// Saving workbook to xlsx
workbook.Save(@"PageBreaks.xlsx", SaveFileFormat.Xlsx);

// Delete rows and columns before the page breaks, the page breaks will be adjusted
worksheet.Range["1:2"].Delete(); // the hPageBreak is before the fourth row
worksheet.Range["B:C"].Delete(); // the vPageBreak is before the fourth column

// Set the page breaks are fixed, it will not be adjusted when inserting/ deleting rows/ columns
worksheet.FixedPageBreaks = true;

// Saving the edited workbook to xlsx
workbook.Save(@"PageBreaksAfterDeletingRows&ColumnsWithFixedPageBreaks.xlsx", SaveFileFormat.Xlsx);

// Delete rows and columns after the page breaks, the page breaks will not be adjusted
worksheet.Range["1:2"].Delete(); // the hPageBreak is still before the fourth row
worksheet.Range["B:C"].Delete(); // the vPageBreak is still before the fourth column

// Insert rows
worksheet.Range["A3:A5"].EntireRow.Insert(); // Inserting rows after deleting row and column ranges

// Saving the finalized workbook to xlsx
workbook.Save(@"PageBreakAfterDeletingRows&Columns.xlsx", SaveFileFormat.Xlsx);