Controlling Page Breaks When Editing Excel Files in .NET Core

Excel workbooks help us maintain records and data entries for future reference. This generally involves editing an existing Excel file. Maintaining these changes on the correct page might become a tedious task when page breaks exist in the sheet and these breaks aren't adjusted as per the changes made.

To make this task easier, GrapeCity Documents for Excel adds the FixedPageBreaks that can be set to define whether the page breaks are fixed on inserting or deleting rows/columns.

If the property is set to True on inserting or deleting records, the page breaks remain fixed i.e. they remain at the same location and do not change as per the rows/columns inserted or deleted. When it is set to False, the page breaks shift as per the insertion or deletion made to the rows/columns.

Let's take an instance of a company printing its sales record from Excel to PDF, such that each region should appear on separate page. The page break is inserted before a region starts. It may happen that some rows may be added or removed depending on the sales, so the page breaks should always be adjusted such that each region always starts from a new page. In this case, FixedPageBreaks property must be set to False to avoid fixed page breaks and make them adjust as per the changes.

If the Excel sheet looks like the one shown below, a horizontal page break can be inserted at A9 cell to ensure that page breaks are observed after the entries of the "East" region are displayed:

This is done as follows:

sheet.HPageBreaks.Add(sheet.Range["A9"]); 

where "sheet" is the IWorkSheet instance depicting the particular sheet.

Deletion of Rows

When a range of cells is deleted such that we wish the page break to be adjusted to show the records from one region on each page, we will set the FixedPageBreaks property to False.

sheet.FixedPageBreaks = false; 

//Deleting 4th and 5th rows   

sheet.Range["4:5"].Delete(); 

With this, the final workbook can be saved to PDF to obtain the result:

    PrintManager printManager = new PrintManager(); 

    printManager.SavePDF("AdjustedPageBreaks_Deletion.pdf", workbook); 

In case the property is set to True, the region does not start from the new page and the page break remains fixed:

Insertion of Rows

When a row is inserted into a sheet, we would expect the page break to be adjusted to show the records from one region on each page. For this, as we have discussed, we will set the FixedPageBreaks property to False.

sheet.FixedPageBreaks = false; 

sheet.Range["A3:G4"].EntireRow.Insert(); 

            object[,] data = new object[,]{ 

                {"4/1/18","East","Robert","Pen Set","80","$15.99","$1279.2"}, 

                {"9/18/18","East","Damon","Pencil","60","$4.99","$299.4"} 

                }; 

            sheet.Range["A3:G4"].Value = data; 

With this, the final workbook can be saved to PDF to obtain the result:

            printManager.SavePDF("AdjustedPageBreaks_Insertion.pdf", workbook); 

In case the property is set to True, the page break remains fixed:

Likewise, these operations can be carried out for Columns as well.

Download the sample

Let us know how the property helps to improve your documents in the comments below! Happy Coding.