Apache POI Hide and Unhide Excel Row using Row.setZeroHeight()

Introduction

In Excel there is an option to hide / unhide a row by select the row then righ click on it and choose Hide / Unhide option. In this tutorial we will learn how to use Apache POI library in Java to create an Excel file and hide its row. Then we try to unhide it by another Java application example.

Apache POI Overview

Apache POI is a open source library to work with Microsoft Office documents in Java.

You can get more information about the project at poi.apache.org

Adding Apache POI Dependencies to Java project

Adding below dependencies to build.gradle file if you are using Gradle build tool.

compile group: 'org.apache.poi', name: 'poi', version: '4.1.1'
compile group: 'org.apache.poi', name: 'poi-ooxml', version: '4.1.1'

Adding below XML to pom.xml file if you are using Maven build tool.

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.1</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.1</version>
</dependency>

Or you can download the .jar files directly from poi.apache.org/download.html

Example 1 - Hide/Unhide row with XSSFWorkbook for .xlsx Excel file

This Java example application to create new Excel file at D:\SimpleSolution\Sample.xlsx with the first row is hidden.

To hide a row we use setZeroHeight(true).

package dev.simplesolution;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class XSSFWorkbookHideRowsExample {
	
	public static void main(String[] args) {
		XSSFWorkbook workbook = new XSSFWorkbook();
		Sheet sheet = workbook.createSheet("XSSFWorkbookHideRowsExample");
		Row row = sheet.createRow(0);
		
		row.setZeroHeight(true);
		
		try (OutputStream fileOut = new FileOutputStream("D:\\SimpleSolution\\Sample.xlsx")) {
            workbook.write(fileOut);
            workbook.close();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
	}

}

Below is the output Excel file.

Hide row for .xlsx file

Below Java application to open the Sample.xlsx created above and unhide its first row.

To check a row is hidden or not we use row.getZeroHeight() which return boolean value to indicate a row is hide/unhide.

To unhide a row we use setZeroHeight(false).

package dev.simplesolution;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Iterator;

import org.apache.poi.EncryptedDocumentException;
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 XSSFWorkbookUnHideExample {

	public static void main(String[] args) throws EncryptedDocumentException, IOException {
		try(InputStream inputStream = new FileInputStream("D:\\SimpleSolution\\Sample.xlsx")) {
			Workbook workbook = WorkbookFactory.create(inputStream);
	        Sheet sheet = workbook.getSheetAt(0);
	        
	        Iterator<Row> rowIterator = sheet.iterator();
	        while(rowIterator.hasNext()) {
	          Row row = rowIterator.next();
	          if(row.getZeroHeight()) {
	            row.setZeroHeight(false);
	          }
	        }
	        
	        try (OutputStream fileOut = new FileOutputStream("D:\\SimpleSolution\\Sample.xlsx")) {
	            workbook.write(fileOut);
	            workbook.close();
	        } catch (IOException ex) {
	            ex.printStackTrace();
	        }
		} catch (IOException ex) {
            ex.printStackTrace();
        }
	}
}

Open the excel file again then we can see the first row is not hidden.

Unhide row for .xlsx file

Example 2 - Hide/Unhide row with HSSFWorkbook for .xls Excel file

This Java example code for hide row for .xls file and using HSSFWorkbook.

package dev.simplesolution;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

public class HSSFWorkbookHideRowsExample {

	public static void main(String[] args) {
		HSSFWorkbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet("HSSFWorkbookHideRowsExample");
        Row row = sheet.createRow(0);
        
        row.setZeroHeight(true);
        
        try (OutputStream fileOut = new FileOutputStream("D:\\SimpleSolution\\Sample.xls")) {
            workbook.write(fileOut);
            workbook.close();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
	}
}

Below is the output Excel file.

Hide row for .xls file

Below Java application to open the Sample.xls created above and unhide its first row.

package dev.simplesolution;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Iterator;

import org.apache.poi.EncryptedDocumentException;
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 HSSFWorkbookUnHideRowsExample {
	
	public static void main(String[] args) throws EncryptedDocumentException, IOException {
		try(InputStream inputStream = new FileInputStream("D:\\SimpleSolution\\Sample.xls")) {
			Workbook workbook = WorkbookFactory.create(inputStream);
	        Sheet sheet = workbook.getSheetAt(0);
	        
	        Iterator<Row> rowIterator = sheet.iterator();
	        while(rowIterator.hasNext()) {
	          Row row = rowIterator.next();
	          if(row.getZeroHeight()) {
	            row.setZeroHeight(false);
	          }
	        }
	        
	        try (OutputStream fileOut = new FileOutputStream("D:\\SimpleSolution\\Sample.xls")) {
	            workbook.write(fileOut);
	            workbook.close();
	        } catch (IOException ex) {
	            ex.printStackTrace();
	        }
		} catch (IOException ex) {
            ex.printStackTrace();
        }
	}

}

Open the excel file again then we can see the first row is not hidden.

Unhide row for .xls file

Conclusion

In this tutorial we have learned how to use Row.setZeroHeight() method to hide / unhide an Excel row using Apache POI library.