Table of Contents
Introduction
Here in this example I am going to show you how to detect hidden rows in excel file using Apache POI library with the help of Java program. The following data are used in the excel sheet. The first two lines’ data are visible in the excel sheet. The third and fourth lines are hidden in the excel sheet.
hello hey
Roy Tutorials
this is a hidden row
this is another hidden row
Prerequisites
Java 1.8+ (11 – 16), Maven 3.8.2, Apache POI 5.2.2
Project Setup
You can create a maven based project in your favorite IDE or tool. The following pom.xml file can be used for this project.
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.roytuts</groupId>
<artifactId>java-detect-hidden-row-excel</artifactId>
<version>0.0.1-SNAPSHOT</version>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>11</maven.compiler.source>
<maven.compiler.target>11</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.2</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.1</version>
</plugin>
</plugins>
</build>
</project>
Detect Hidden Rows/Cells in Excel
Let’s say the excel file has the below content in first sheet:
Now I want to hide rows numbers 4 and 6. To hide a row, do right click on the row and click on the Hide option in the context menu:
After hiding rows 4 and 6 the content becomes as shown below.
If you want to unhide the rows, you can select from row 3 to 7 and do right click and click on Unhide option in the context menu to unhide the hidden rows.
Java Code
Now I am going to write Java program to detect hidden rows and their content.
package com.roytuts.java.detect.hidden.row.excel;
import java.io.File;
import java.io.IOException;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
public class HiddenRowDetectorApp {
public static void main(String[] args) {
detectHiddenRow("roytuts.xlsx");
}
public static void detectHiddenRow(final String fileName) {
Workbook wb = null;
try {
wb = WorkbookFactory.create(new File(fileName));
Sheet sheet = wb.getSheetAt(0);
for (Row r : sheet) {
if (r.getZeroHeight() || r.getHeight() == 0) {
System.out.println("This row (" + r.getRowNum() + ") is hidden!");
}
for (Cell c : r) {
System.out.print(c.getStringCellValue());
System.out.print(" ");
}
System.out.println();
}
} catch (EncryptedDocumentException | IOException e) {
e.printStackTrace();
} finally {
try {
wb.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
So for each I am checking for the hidden row using the following condition:
if (r.getZeroHeight() || r.getHeight() == 0) {
...
}
If there is no hidden row in the excel file then the above condition will not satisfy.
Testing the Hidden Rows/Cells
Now executing the above main class will give you the following output:
hello hey
Roy Tutorials
This row (3) is hidden!
this is a hidden row
This row (5) is hidden!
this is another hidden row
If you unhide the rows 4 (3 as it is 0-based index) and 6 (5 as it is 0-based index), then you will see only the following output:
hello hey
Roy Tutorials
this is a hidden row
this is another hidden row
Hope this gives you idea how to detect hidden rows and read their values in Java program.