News Category

Java: Freeze Rows and Columns in Excel

2022-04-27 02:05:00 Written by  support iceblue
Rate this item
(0 votes)

Excel Panes can be frozen to make certain rows or columns visible even when scrolling through the worksheet. This feature is helpful for viewing or editing data in a large worksheet. For example, once the header row is frozen, we can easily find cells in the lower part of the worksheet without having to go back to the top to check the name of the header. This article will introduce how to freeze rows or/and columns in Excel using Spire.XLS for Java.

Spire.XLS for Java provides the Worksheet.freezePanes(int rowIndex, int columnIndex) method to freeze all rows and columns above and left of the selected cell which is specified by the rowIndex and the columnIndex.

Java: Freeze Rows and Columns in Excel

This tutorial provides the code examples for the following cases:

Install Spire.XLS for Java

First, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. 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>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>14.3.2</version>
    </dependency>
</dependencies>
    

Freeze the Top Row

In order to freeze the top row, we should select the cell (2, 1) – "A2" and freeze the row above. The following are the steps to freeze the top row using Spire.XLS for Java.

  • Create a Workbook object.
  • Load an Excel file using Workbook.loadFromFile() method.
  • Get a specific worksheet using Workbook.getWorksheets().get() method.
  • Freeze the top row by passing (2, 1) to the Worksheet.freezePanes(int rowIndex, int columnIndex) method as the parameter.
  • Save the workbook to another Excel file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class FreezeTopRow {

    public static void main(String[] args) {

        //Create a Workbook instance
        Workbook workbook = new Workbook();

        //Load an Excel document
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Freeze the first row
        sheet.freezePanes(2, 1);

        //Save to another file
        workbook.saveToFile("output/FreezeTopRow.xlsx", ExcelVersion.Version2016);
    }
}

Java: Freeze Rows and Columns in Excel

Freeze the First Column

To freeze the first column, we should select the cell (1, 2) – "B1" and freeze the column on the left. The following are the steps to freeze the first column using Spire.XLS for Java.

  • Create a Workbook object.
  • Load an Excel file using Workbook.loadFromFile() method.
  • Get a specific worksheet using Workbook.getWorksheets().get() method.
  • Freeze the top row by passing (1, 2) to the Worksheet.freezePanes(int rowIndex, int columnIndex) method as the parameter.
  • Save the workbook to another Excel file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class FreezeFirstColumn {

    public static void main(String[] args) {

        //Create a Workbook instance
        Workbook workbook = new Workbook();

        //Load an Excel document
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Freeze the first column
        sheet.freezePanes(1, 2);

        //Save to another file
        workbook.saveToFile("output/FreezeFirstColumn.xlsx", ExcelVersion.Version2016);
    }
}

Java: Freeze Rows and Columns in Excel

Freeze the First Row and the First Column

To freeze the first row and the first column, the selected cell should be cell (2, 2) – "B2". The following are the detailed steps.

  • Create a Workbook object.
  • Load an Excel file using Workbook.loadFromFile() method.
  • Get a specific worksheet using Workbook.getWorksheets().get() method.
  • Freeze the first row and the first column by passing (2, 2) to the Worksheet.freezePanes(int rowIndex, int columnIndex) method as the parameter.
  • Save the workbook to another Excel file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class FreezeFirstRowAndFirstColumn {

    public static void main(String[] args) {

        //Create a Workbook instance
        Workbook workbook = new Workbook();

        //Load an Excel document
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Freeze the first row and the first column
        sheet.freezePanes(2, 2);

        //Save to another file
        workbook.saveToFile("output/FreezeFirstRowAndFirstColumn.xlsx", ExcelVersion.Version2016);
    }
}

Java: Freeze Rows and Columns in Excel

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Additional Info

  • tutorial_title:
Last modified on Tuesday, 27 September 2022 02:26