1. Overview
Apache POI is a popular open-source library in the Java community for working with Microsoft Office documents. It allows developers to programmatically manipulate files like Word documents and Excel spreadsheets easily.
Locking the header row is common when we deal with large Excel spreadsheets. This facilitates a more user-friendly experience for data navigation and analysis.
In this tutorial, we’ll learn how to use Apache POI to lock header rows in Excel spreadsheets.
2. Dependency
Let’s start with adding the following Maven dependencies to the pom.xml file:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.5</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.5</version>
</dependency>
<dependency>
poi is essential to deal with the old binary Excel files (xls). We need the additional poi-ooxml if we need to deal with XML-based Excel files (xlsx).
3. Workbook Creation
Before we dive into locking header rows, let’s quickly understand how to create an Excel sheet and populate it with data using Apache POI.
To begin, we’ll need to set up the Workbook and Sheet instances:
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("MySheet");
If we wish to create a binary Excel file instead of an XML-based one, we can replace XSSFWorkbook with HSSFWorkbook.
Next, we’ll create the header row and add the header cell values:
Row headerRow = sheet.createRow(0);
Cell headerCell1 = headerRow.createCell(0);
headerCell1.setCellValue("Header 1");
Cell headerCell2 = headerRow.createCell(1);
headerCell2.setCellValue("Header 2");
Once we’ve set up the header row, we’ll add more data to the worksheet:
Row dataRow = sheet.createRow(1);
Cell dataCell1 = dataRow.createCell(0);
dataCell1.setCellValue("Data 1");
Cell dataCell2 = dataRow.createCell(1);
dataCell2.setCellValue("Data 2");
4. Locking
Now, let’s move on to the vital part. Apache POI provides a simple method called createFreezePane() to lock rows and columns.
The createFreezePane() method takes two arguments: colSplit and rowSplit. The colSplit argument represents the column index that will remain unlocked, while the rowSplit argument represents the row index up to which rows will be locked.
4.1. Locking Single Row
In most cases, we’ll want to lock the first row to keep the header row always visible as we scroll through the data:
sheet.createFreezePane(0, 1);
We notice that the first row remains locked and fixed at the top when we scroll down:
4.2. Locking Multiple Rows
In certain scenarios, we may want to lock multiple rows, providing users with more context as they explore the data. To achieve this, we can adjust the rowSplit argument accordingly:
sheet.createFreezePane(0, 2);
In this example, the first two rows remain visible while scrolling.
4.3. Locking Columns
Apart from locking rows, Apache POI allows us to lock columns as well. This is useful when we have a large number of columns and we want to keep a specific column visible for reference:
sheet.createFreezePane(1, 0);
In this case, the first column is locked in the worksheet.
5. Conclusion
In this article, we saw how to lock header rows with Apache POI, a powerful Java library for working with Microsoft Office documents.
By using the createFreezePane() method, we can tailor the locking behavior to suit our specific requirements. This could be to keep headers fixed, lock multiple rows for context, or lock columns. This enhances the user experience in data navigation and analysis.
As usual, the source code for this article can be found over on GitHub.
