Identify Excel's cell data type

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 :