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.

Source Code

Download