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

Introduction

This tutorial will show you how we can deal with empty or blank cell in excel file using Apache POI. We cannot handle blank or empty cell if we use cell iterator, so we have to get the maximum cell index for the row and iterate through index to deal with empty cell. You can go through another tutorial on detect empty or blank rows and delete from excel file.

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.

For more information please go through https://poi.apache.org/

Prerequisites

Eclipse 2019-12, JDK at least 1.8, Gradle 6.1.1, Maven 3.6.3, Apache POI 3.x or 4.1.x

Create Project

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

Update Build File

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' //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 or 12</jdk.version>
		<poi.version>3.12 or 4.1.1 or 4.1.2</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 we 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.

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.

We 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:

We create Workbook object and retrieve the sheet from it. We iterate each row from the sheet. We skip the headers from the sheet.

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

We 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 we 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.

Test Class

Now we will create a test class to test our 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 BlankCellHandlerTest {

	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

Thanks for reading.

Related posts

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