How to insert or remove page breaks in Excel Worksheets using Java

When we’re printing a large Excel worksheet, in order to exactly control which and how many rows and columns appear on the printed pages, we need to insert page breaks to specify where a new page will begin. This article will demonstrate how to insert or remove page breaks in Excel worksheet using Java programmatically. Please note that Page Breaks is only visible in the View menu tab under Workbook Views with the name of Page Break Preview.

Creating a Test Environment

Before typing codes, you should create a test environment by installing JDK, Intellij IDEA and Free Spire.XLS for Java which can be gained through E-iceblue website. After downloading the package, unzip it and manually add Spire.Xls.jar in the “lib” folder to your IDEA.

Of course, if you’re creating a Maven project, the best method is to install Free Spire.XLS for Java from Maven repository. The only thing you need to do is typing the following codes in the pom.xml file and clicking the button “Import Changes”.

<repositories>

        <repository>

            <id>com.e-iceblue</id>

            <name>e-iceblue</name>

            <url>http://repo.e-iceblue.com/nexus/content/groups/public/</url>

        </repository>

</repositories>

<dependencies>

    <dependency>

        <groupId>e-iceblue</groupId>

        <artifactId>spire.xls.free</artifactId>

        <version>3.9.1</version>

    </dependency>

</dependencies>

Using the code
Add Page Breaks to Excel worksheet
Free Spire.XLS for Java provides a method to help us insert page breaks horizontally and vertically.
After adding page breaks, we can set view mode as Page Break Preview which displays excel
as it will be printed. 

import com.spire.xls.*;

public class AddPageBreak {

    public static void main(String[] args) {

        //Create a Workbook instance and load the sample file

        Workbook workbook = new Workbook();

        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\Sample.xlsx");

        //Get the second worksheet

        Worksheet sheet = workbook.getWorksheets().get(1);

        //Set Excel Page Break Horizontally

        sheet.getHPageBreaks().add(sheet.getRange().get("A6"));

        //Set Excel Page Break Vertically

        sheet.getVPageBreaks().add(sheet.getRange().get("C1"));

        sheet.getVPageBreaks().add(sheet.getRange().get("E1"));

        // Set the ViewMode as Preview mode 

        workbook.getWorksheets().get(0).setViewMode(ViewMode.Preview);

        //Save the document

        workbook.saveToFile("output/AddPageBreak.xlsx", ExcelVersion.Version2013);

    }

}
Output



Remove Page Breaks in Excel worksheet
In addition to inserting page breaks, Free Spire.XLS for Java supports removing the special page breaks in Excel
worksheet. Here comes to the steps of how to do it.
Step 1: Initialize a Workbook instance and load the sample file.
Step 2: Get the second worksheet from the workbook.
Step 3: Clear all the vertical page breaks and remove the first horizontal page break.
Step 4: Set the ViewMode as Preview to see how the page breaks work.
Step 5: Save the resulting document to file.
import com.spire.xls.*;
public class RemovePageBreak {

    public static void main(String[] args) {

        //Create a Workbook and load the sample file

        Workbook workbook = new Workbook();

        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\AddPageBreak.xlsx");

        //Get the second worksheet

        Worksheet sheet = workbook.getWorksheets().get(1);

        //Clear all the horizontal page break

        sheet.getHPageBreaks().clear();

        //Remove the first vertical Page Break

        sheet.getVPageBreaks().removeAt(0);

        //Set the ViewMode as Preview mode

        sheet.setViewMode(ViewMode.Preview);

        //Save the document

        String output = "output/removePageBreak.xlsx";

        workbook.saveToFile(output,ExcelVersion.Version2013);

    }

}
Output