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.

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 Excel 97-2008).

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

If you already have an idea on how to create a maven project in Eclipse will be great otherwise I will tell you here how to create a maven project in Eclipse.

Prerequisites

The following things are required in order to run the application

Eclipse Kepler
JDK 1.8
Have maven 3 installed and configured
Apache POI dependency in pom.xml

Now we will see the below steps how to create a maven based spring project in Eclipse

Step 1. Create a standalone maven project in Eclipse

Go to File -> New -> Other. On popup window under Maven select Maven Project. Then click on Next. Select the workspace location – either default or browse the location. Click on Next. Now in next window select the row as highlighted from the below list of archtypes and click on Next button.

maven-arctype-quickstart

Now enter the required fields (Group Id, Artifact Id) as shown below

Group Id : com.roytuts
Artifact Id : apache-poi

Step 2. Modify the pom.xml file as shown below.

<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>apache-poi</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>
 
    <name>apache-poi</name>
    <url>http://maven.apache.org</url>
 
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <poi.version>3.12</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>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

In the above maven pom.xml file we have added only apache poi dependency for working with excel file. The same dependency is used for word document as well and you don’t need separate dependency for that. The new formats such as xlsx, docs etc. are handled using this dependency. If you have older version of document, excel file then you have to use different version of apache poi.

Step 3. If you see JRE System Library[J2SE-1.5] then change the version by below process

Do right-click on the project and go to Build -> Configure build path, under Libraries tab click on JRE System Library[J2SE-1.5], click on Edit button and select the appropriate jdk 1.8 from the next window. Click on Finish then Ok. Now you will see that your jdk version has been changed.

Step 4. 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.

excel

Download the excel file – info.xlsx

Step 5. Create a POJO class for mapping the excel file info to object. The below Java class represents a row corresspondin to excel sheet and each attribute represents a column matching to the excel sheet column name. We have also overridden the default toString() method to print all attributes at a time when called the Info object in print statement.

package com.roytuts.excel;

public class Info {

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getMobile() {
        return mobile;
    }

    public void setMobile(String mobile) {
        this.mobile = mobile;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getPermAddress() {
        return permAddress;
    }

    public void setPermAddress(String permAddress) {
        this.permAddress = permAddress;
    }

    public String getCommAddress() {
        return commAddress;
    }

    public void setCommAddress(String commAddress) {
        this.commAddress = commAddress;
    }

    @Override
    public String toString() {
        return "Info [Name=" + name + ", Mobile=" + mobile + ", Phone=" + phone + ", Permanent Address=" + permAddress
                + ", Communication Address=" + commAddress + "]";
    }

}

Step 6. Create a class for reading the excel file. Here is the main logic to read the sheet from above excel file and process accordingly. 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 set value as null. Finally we print the values from main method.

package com.roytuts.excel;

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.xssf.usermodel.XSSFWorkbook;

public class HandleBlankExcelCell {

    public static void main(String[] args) {
        List<Info> infoList = extractInfo();
        for (Info info : infoList) {
            System.out.println(info);
        }
    }

    public static List<Info> extractInfo() {
        List<Info> infoList = new ArrayList<Info>();
        Workbook wb = null;
        try {
            wb = new XSSFWorkbook(ClassLoader.getSystemResourceAsStream("info.xlsx"));
            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.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) {
            nameCell.setCellType(Cell.CELL_TYPE_STRING);
            info.setName(nameCell.getStringCellValue());
        }
        Cell mobileCell = cells.get(1);
        if (mobileCell != null) {
            mobileCell.setCellType(Cell.CELL_TYPE_STRING);
            info.setMobile(mobileCell.getStringCellValue());
        }
        Cell phoneCell = cells.get(2);
        if (phoneCell != null) {
            phoneCell.setCellType(Cell.CELL_TYPE_STRING);
            info.setPhone(phoneCell.getStringCellValue());
        }
        Cell permAddressCell = cells.get(3);
        if (permAddressCell != null) {
            permAddressCell.setCellType(Cell.CELL_TYPE_STRING);
            info.setPermAddress(permAddressCell.getStringCellValue());
        }
        Cell commAddressCell = cells.get(4);
        if (commAddressCell != null) {
            commAddressCell.setCellType(Cell.CELL_TYPE_STRING);
            info.setCommAddress(commAddressCell.getStringCellValue());
        }
        return info;
    }

}

Step 7. 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, Permanent Address=Birati, Communication Address=Kolkata]
Info [Name=Sumit, Mobile=9836547485, Phone=null, Permanent Address=Dankuni, Communication Address=Kolkata]
Info [Name=Souvik, Mobile=9051478954, Phone=null, Permanent Address=Kalighat, Communication Address=null]
Info [Name=Liton, Mobile=null, Phone=33457849651, Permanent Address=Siliguri, Communication Address=Kolkata]

Thanks for reading.

Tags:

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on Roy Tutorials | TwitterFacebook Google PlusLinkedin | Reddit

Leave a Reply

Your email address will not be published. Required fields are marked *