Deal with empty or blank cell in excel file using apache poi

Introduction

This tutorial will show you how you can deal with empty or blank cell in excel file using Apache POI. You won’t be able to handle blank or empty cell if you use cell iterator, so you have to get the maximum cell index for the row and iterate through index to deal with empty cell.

Related Posts:

The Apache POI is to create and maintain Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft’s OLE 2 Compound Document format (OLE2). You can read and write MS Excel files using Java. In addition, you can read and write MS Word and MS PowerPoint files using Java. Apache POI is the Java Excel solution.

Prerequisites

JDK at least 1.8, Gradle 6.1.1 – 6.7.1, Maven 3.6.3, Apache POI 3.x or 4.1.x or 5.0.0

Project Setup

Create gradle or maven based project in your favorite tool or IDE. The name of the project is java-apache-poi-excel-deal-empty-blank-cell.

If you are creating gradle based project then you can use below build.gradle script:

plugins {
    id 'java-library'
}

sourceCompatibility = 12
targetCompatibility = 12

repositories {
    jcenter()
}

dependencies {
    implementation 'org.apache.poi:poi-ooxml:4.1.2' //5.0.0 or 4.1.1 or 3.12
    //required only for jdk 9 or above
    implementation('com.fasterxml.jackson.core:jackson-databind:2.11.0')
}

If you are creating maven based project then you can use below pom.xml:

<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-apache-poi-excel-deal-empty-blank-cell</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>
	
	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<jdk.version>1.8 - 12</jdk.version>
		<poi.version>3.12 or 4.1.1 or 4.1.2 or 5.0.0</poi.version>
	</properties>
	
	<dependencies>
		<!-- apache poi for xlsx, docx etc reading/writing -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>${poi.version}</version>
		</dependency>
	</dependencies>
	
	<build>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.8.1</version>
				<configuration>
					<source>${jdk.version}</source>
					<target>${jdk.version}</target>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

In the above maven pom.xml or build.gradle file we have added only apache poi dependency for working with excel file.

Excel File

Create an excel file info.xlsx with below information. Obviously you see there are many empty or blank cells under few columns like Mobile, Phone, Communication Address.

So you will deal with empty or blank cell in excel file using apache poi. Alternatively you can also download the same file from the link given below right after the image.

I have kept under the project’s root directory to read using Java code. Of course you can put anywhere on a physical location on your system and accordingly you need to change the path while you are reading the file using Java code.

deal with empty or blank cells in excel

Download the excel file – info.xlsx

POJO Class

Create a POJO (Plain Old Java Object) class for mapping the excel file information into an object.

below Java class represents a row corresponding to excel sheet and each attribute represents a column matching to the excel sheet column name.

I have overridden the default toString() method to print all attributes at a time when called the Info object in print statement.

package com.roytuts.java.apache.poi.excel.deal.empty.blank.cell;

public class Info {

	private String name;
	private String mobile;
	private String phone;
	private String permAddress;
	private String commAddress;

	//getters and setters

	@Override
	public String toString() {
		return "Info [name=" + name + ", mobile=" + mobile + ", phone=" + phone + ", permAddress=" + permAddress
				+ ", commAddress=" + commAddress + "]";
	}

}

Handle Blank or Empty Cell

Here is the main logic to read the sheet of the above excel file and process accordingly.

Related Posts:

I created Workbook object and retrieve the sheet from it. I iterate each row from the sheet. I skip the headers from the sheet.

I calculate the maximum number of columns. Then I iterate through each column and for blank column I do not set any value. For numeric cell I use determine the cell type and set the value. I cannot use setCellType() method as it is deprecated in poi version 4.x (or 5.x) onward.

I have used NumberToTextConverter.toText() method to read the mobile or phone number as it is in the excel file otherwise it will represent something – 3.3457849651E10, which I don’t want to show in the output.

package com.roytuts.java.apache.poi.excel.deal.empty.blank.cell;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

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.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public final class ExcelUtil {

	private ExcelUtil() {
	}

	public static List<Info> extractInfo(final String file) {
		List<Info> infoList = new ArrayList<Info>();
		Workbook wb = null;

		try {
			wb = new XSSFWorkbook(new FileInputStream(new File(file)));
			Sheet sheet = wb.getSheetAt(0);

			boolean skipHeader = true;

			for (Row row : sheet) {
				if (skipHeader) {
					skipHeader = false;
					continue;
				}

				List<Cell> cells = new ArrayList<Cell>();

				int lastColumn = Math.max(row.getLastCellNum(), 5);// because my
																	// excel
																	// sheet has
																	// max 5
																	// columns,
																	// in case
																	// last
																	// column is
																	// empty
																	// then
																	// row.getLastCellNum()
																	// will
																	// return 4
				for (int cn = 0; cn < lastColumn; cn++) {
					Cell c = row.getCell(cn, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
					cells.add(c);
				}

				Info info = extractInfoFromCell(cells);
				infoList.add(info);
			}
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (wb != null) {
				try {
					wb.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}

		return infoList;
	}

	private static Info extractInfoFromCell(List<Cell> cells) {
		Info info = new Info();

		Cell nameCell = cells.get(0);
		if (nameCell != null) {
			info.setName(nameCell.getStringCellValue());
		}

		Cell mobileCell = cells.get(1);
		if (mobileCell != null) {
			switch (mobileCell.getCellType()) {
			case NUMERIC:
				info.setMobile(NumberToTextConverter.toText(mobileCell.getNumericCellValue()));
				break;
			case BLANK:
				break;
			default:
				break;
			}
		}

		Cell phoneCell = cells.get(2);
		if (phoneCell != null) {
			switch (phoneCell.getCellType()) {
			case NUMERIC:
				info.setPhone(NumberToTextConverter.toText(phoneCell.getNumericCellValue()));
				break;
			case BLANK:
				break;
			default:
				break;
			}
		}

		Cell permAddressCell = cells.get(3);
		if (permAddressCell != null) {
			info.setPermAddress(permAddressCell.getStringCellValue());
		}

		Cell commAddressCell = cells.get(4);
		if (commAddressCell != null) {
			info.setCommAddress(commAddressCell.getStringCellValue());
		}

		return info;
	}

}

If you are using apache dependency version 3.12 then you need to handle different way. The switch case statement should be replaced in the following way for numeric cell types.

Replace:

switch (mobileCell.getCellType()) {
case NUMERIC:
	info.setMobile(NumberToTextConverter.toText(mobileCell.getNumericCellValue()));
	break;
case BLANK:
	break;
default:
	break;
}

By:

mobileCell.setCellType(Cell.CELL_TYPE_STRING);
info.setMobile(mobileCell.getStringCellValue());

Similarly you can replace for phone cell also.

Main Class

Now I will create a test class to test the program how it handles blank or empty cells in the excel file.

package com.roytuts.java.apache.poi.excel.deal.empty.blank.cell;

import java.util.List;

public class BlankCellHandlerApp {

	public static void main(String[] args) {
		List<Info> infoList = ExcelUtil.extractInfo("info.xlsx");

		for (Info info : infoList) {
			System.out.println(info);
		}
	}

}

Testing the Application

Run the above class you will get the below output.

Here you see that for all blank cells you will get null as output for any column value.

Info [name=Loku, mobile=9433578459, phone=null, permAddress=Birati, commAddress=Kolkata]
Info [name=Sumit, mobile=9836547485, phone=null, permAddress=Dankuni, commAddress=Kolkata]
Info [name=Souvik, mobile=9051478954, phone=null, permAddress=Kalighat, commAddress=null]
Info [name=Liton, mobile=null, phone=33457849651, permAddress=Siliguri, commAddress=Kolkata]

Source Code

Download

6 Thoughts to “Deal with empty or blank cell in excel file using apache poi”

  1. How can we delete Filled Row OR from .xlsx sheet Java?

  2. Pratyusha

    hi, by applying above solution we are getting result, but there are many empty rows are also getting printed, Can you modify above code to remove blank rows in excel?

    1. Thanks.

      It’s only about handling empty or blank cells in excel file. I am not sure how soon I can write on handling blank rows but I will try to post asap.

  3. Bharat

    Hi This logic works fine but I want your suggestion to improvise this logic in such a way that those cells read from one excel sheet and copy into another excel sheet with same logic applied with only blank cells wherever present. Could you please guide me to achieve this?

    1. Simply set the blank value at those cells and write to another file or sheet.

Leave a Comment