How to Insert Page Breaks in Excel with C#？

-

 

In daily office work, we often use Excel documents to help us organize data. In order to print Excel worksheets conveniently, we can insert page breaks in Excel. All kinds of processing software will automatically perform pagination according to the size of the page set by the user to beautify the visual effect of the document and simplify the user's operation. However, the result of the system's automatic paging does not necessarily meet the user's requirements. Manually insert page breaks to divide a worksheet into multiple pages to avoid data misalignment. This article will introduce how to insert page breaks in Excel programmatically through C# code. And attach the VB.NET code for your reference.

  • Insert Horizontal Page Breaks in an Excel Worksheet
  • Insert Vertical Page Breaks in an Excel Worksheet

Programming Environment

In this test, Free Spire.XLS for .NET is introduced into the program. The Spire.XLS.dll file can be referenced by:

Method 1: Download Free Spire.XLS for .NET locally, unzip it, and install it. After the installation is complete, find Spire.XLS.dll in the BIN folder under the installation path. Then open the "Solution Explorer" in Visual Studio, right-click "References", "Add Reference", and add a reference to the dll file in the BIN folder of the local path to the program.

Method 2: Install via NuGet. It can be installed by the following 2 methods:

(1) You can open the "Solution Explorer" in Visual Studio, right-click "References", "Manage NuGet Packages", then search for "Free Spire.XLS", and click "Install". Wait for the program installation to complete.

(2) Copy the following content to the PM console installation.

Install-Package FreeSpire.XLS -Version 12.7

Insert Horizontal Page Breaks in an Excel Worksheet

  • 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.

Full Code

C#

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("Test.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["A17"]);

 

            //Set view mode to Preview mode

            sheet.ViewMode = ViewMode.Preview;

 

            //Save the result document

            workbook.SaveToFile("SetHorizontalPageBreak.xlsx");

        }

    }

}

 VB.NET

Imports Spire.Xls

Namespace EditExcelComment

    Friend Class Program

        Private Shared Sub Main(ByVal args As String())

            'Create a Workbook instance

            Dim workbook As Workbook = New Workbook()

 

            'Load a sample Excel document

            workbook.LoadFromFile("Test.xlsx")

 

            'Get the first worksheet

            Dim sheet As Worksheet = workbook.Worksheets(0)

 

            'Set Excel page break horizontally

            sheet.HPageBreaks.Add(sheet.Range("A7"))

            sheet.HPageBreaks.Add(sheet.Range("A17"))

 

            'Set view mode to Preview mode

            sheet.ViewMode = ViewMode.Preview


            'Save the result document

            workbook.SaveToFile("SetHorizontalPageBreak.xlsx")

        End Sub

    End Class

End Namespace

Effective Shot


Insert Vertical Page Breaks in an Excel Worksheet

  • 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.

Full Code

C#

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("Test.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");

        }

    }

}

VB.NET

Imports Spire.Xls

Namespace EditExcelComment

    Friend Class Program

        Private Shared Sub Main(ByVal args As String())

            'Create a Workbook instance

            Dim workbook As Workbook = New Workbook()

 

            'Load a sample Excel document

            workbook.LoadFromFile("Test.xlsx")

 

            'Get the first worksheet

            Dim sheet As Worksheet = 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")

        End Sub

    End Class

End Namespace

Effective Shot


Conclusion:

In this post, you have learned how to insert page breaks in Excel with C#. Not only that, we also have other functions, such as, C#/VB.NET: Rearrange Columns in Excel，C#/VB.NET: Copy Cell Ranges Between Different Workbooks and so on. Apart from that, if you'd like to learn more, you can visit this link to explore more about for Spire.XLS for .NET.


Comments

Post a Comment

Popular posts from this blog

Java: How to encrypt or decrypt PDF documents?

-
Image
 In daily work, we encrypt important documents and set user access permissions, which are not accessible to other external personnel. Only users who have obtained this permission are eligible to open documents. In addition, restricting users' access greatly prevents those who intentionally tamper with or copy the content, thereby improving the security of the document. At the same time, another major role of document encryption is to prevent loss, as there may be situations where employees intentionally or unintentionally delete documents during business trips or resignations, resulting in document loss and causing a certain impact on the company's business and image. So how can we encrypt PDF documents? How to decrypt a document after encrypting it? The following article provides a detailed introduction to how to programmatically encrypt or decrypt PDF documents through Java code. Encrypt a PDF File with Password Remove Password to Decrypt a PDF File Programming Environm...
Read more

Replace the existing image on the PDF file in C#/VB.NET

-
Image
  To err is human and when it comes to documentation, we all can make mistakes. Modern day documents are mostly shared in the PDF format and contain lots of visuals and images. However, there will be times when you may wish to replace an image in the PDF document due to an error pasting the image or otherwise. In this article, I will take a detailed look at the methods for replacing the existing image on the PDF file. To achieve this functionality, you just need to follow a few simple steps. Read on for more information.   Programming Environment   In this test, Free Spire.PDF for .NET is introduced into the program. The Spire.PDF.dll file can be referenced by:   Method 1: Download Free Spire.PDF for .NET locally, unzip it, and install it. After the installation is complete, find Spire.PDF.dll in the BIN folder under the installation path. Then open the "Solution Explorer" in Visual Studio, right-click "References", "Add Reference", and add a r...
Read more

How to Convert PDF to Images in Java

-
Image
PDF files and image files are two completely different formats, but sometimes there is a need to convert the two formats to each other. How do you solve the problem of converting PDF files to image files at work? Is the method you use simple and convenient? If it is very troublesome, you might as well learn how to share this article. This article will introduce how to quickly and efficiently convert PDF to image format through a Java application. Convert a Whole PDF Document to Multiple Images Convert a Particular PDF Page to an Image Programming Environment Method 1: Introduced manually. Download Free Spire.PDF for Java locally, unzip it, and find the Spire.PDF.jar file in the lib folder. Open the following interface in IDEA, and import the jar file in the local path into the Java program: If you use Maven , you can easily import the JAR file in your application by adding the following code to your project’s pom.xml file. <repositories>     <repos...
Read more