Empty/Blank Cell

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:

• 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.

Prerequisites

Java 8/12/19, Gradle 6.1.1 – 6.7.1, Maven 3.6.3/3.8.5, Apache POI 3.x/4.1.x/5.0.0/5.2.3

Project Setup

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

For the maven based project then you can use below pom.xml:

<?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-apache-poi-excel-deal-empty-blank-cell</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<maven.compiler.source>19</maven.compiler.source>
		<maven.compiler.target>19</maven.compiler.target>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>5.2.3</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>

In the above maven pom.xml file I 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 in your system and accordingly you need to change the path while you are reading the file using Java code.

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.

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:

• How to detect and delete empty or blank rows from excel file

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.

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 POI version 3.12 then you need to handle in 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.

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 Empty/Blank Cell App

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