In this section, you will learn how you can identify cell's data type and handle it appropriately using Apache POI.

Identify Excel's cell data type

In this section, you will learn how you can identify cell's data type and handle it appropriately using Apache POI.

In Apache POI library, for fetching each type of data, there is separate method. For example, fetching numeric we use getNumericCellValue() and for fetching string we use getRichStringCellValue().

Due to the above reason, we need to identify each type of excel cell correctly to handle it appropriately. In the below example, you can learn how to identify and handle each type of  cell.

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.util.*;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class XLCheckData {
public static void main(String[] args) throws Exception {
String filename = "xls/test.xls";

FileInputStream fis = null;
try {
fis = new FileInputStream(filename);

HSSFWorkbook workbook = new HSSFWorkbook(fis);
HSSFSheet sheet = workbook.getSheetAt(0);

Iterator<?> rows = sheet.rowIterator();
while (rows.hasNext()) {
HSSFRow row = (HSSFRow) rows.next();
Iterator<?> cells = row.cellIterator();
while (cells.hasNext()) {
HSSFCell cell = (HSSFCell) cells.next();

int type = cell.getCellType();
if (type == HSSFCell.CELL_TYPE_STRING) {
System.out.println("[" + cell.getRowIndex() + ", "
+ cell.getColumnIndex() + "] = STRING; Value = "
+ cell.getRichStringCellValue().toString());
} else if (type == HSSFCell.CELL_TYPE_NUMERIC) {
System.out.println("[" + cell.getRowIndex() + ", "
+ cell.getColumnIndex() + "] = NUMERIC; Value = "
+ cell.getNumericCellValue());
} else if (type == HSSFCell.CELL_TYPE_BOOLEAN) {
System.out.println("[" + cell.getRowIndex() + ", "
+ cell.getColumnIndex() + "] = BOOLEAN; Value = "
+ cell.getBooleanCellValue());
} else if (type == HSSFCell.CELL_TYPE_BLANK) {
System.out.println("[" + cell.getRowIndex() + ", "
+ cell.getColumnIndex() + "] = BLANK CELL");
}
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} finally {
if (fis != null) {
fis.close();
}
}
}
}

OUTPUT

In console, you can see the following output :

[0, 0] = STRING; Value = EmpId [0, 1] = STRING; Value = Name [0, 2] = STRING; Value = Designation [1, 0] = NUMERIC; Value = 1.0 [1, 1] = STRING; Value = ABC [1, 2] = STRING; Value = Software Engineer [2, 0] = NUMERIC; Value = 2.0 [2, 1] = STRING; Value = DFG [2, 2] = STRING; Value = Sr Software Engineer [3, 0] = NUMERIC; Value = 3.0 [3, 1] = STRING; Value = LOI [3, 2] = STRING; Value = Team Leader [4, 0] = NUMERIC; Value = 4.0 [4, 1] = STRING; Value = LKJ [4, 2] = STRING; Value = Project Manager

Download Source Code

Related Tags for Identify Excel's cell data type:

More Tutorials from this section

• Create Simple Excel(.xls) document using Apache POI
• Create Excel(.xlsx) document using Apache POI
• Read Simple Excel(.xls) document using Apache POI
• Read Excel(.xlsx) document using Apache POI
• Format Cell of Simple Excel Sheet(.xls)
• Checking Date Value of Excel Cells
• Drawing a Shape in Excel Sheet
• Excel Freeze Pane Feature
• Insert Image into Excel Sheet
• Set Zoom Magnification of Excel Sheet
• Excel Sheet Footer
• Excel Sheet Header
• Excel Splits Pane Feature
• Identify Excel's cell data type
• Apache POI API for Microsoft Doc's Manipulation
• Excel Cell Comment
• Excel Cell Drop Down List
• Excel Data validation
• Excel Prompt User on Focus
• Creating Shapes using Shape Groups