In this example we try to obtain the Excel’s cell data type so that we can read the value using the right method. The data to be read is in a file named celltype.xls
. The matrix below depict how the file is.
| COL
ROW | 0 1 2 3 4
----|-------------------------
0 | 1 2 A B TRUE
1 | FALSE X Y Z 10
package org.kodejava.poi;
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;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.util.Iterator;
public class ObtainingCellType {
public static void main(String[] args) throws Exception {
String filename = "celltype.xls";
try (FileInputStream fis = new FileInputStream(filename)) {
HSSFWorkbook workbook = new HSSFWorkbook(fis);
HSSFSheet sheet = workbook.getSheetAt(0);
Iterator<Row> rows = sheet.rowIterator();
while (rows.hasNext()) {
HSSFRow row = (HSSFRow) rows.next();
Iterator<Cell> cells = row.cellIterator();
while (cells.hasNext()) {
HSSFCell cell = (HSSFCell) cells.next();
CellType type = cell.getCellType();
if (type == CellType.STRING) {
System.out.printf("[%d, %d] = STRING; Value = %s%n",
cell.getRowIndex(), cell.getColumnIndex(),
cell.getRichStringCellValue().toString());
} else if (type == CellType.NUMERIC) {
System.out.printf("[%d, %d] = NUMERIC; Value = %f%n",
cell.getRowIndex(), cell.getColumnIndex(),
cell.getNumericCellValue());
} else if (type == CellType.BOOLEAN) {
System.out.printf("[%d, %d] = BOOLEAN; Value = %b%n",
cell.getRowIndex(), cell.getColumnIndex(),
cell.getBooleanCellValue());
} else if (type == CellType.BLANK) {
System.out.printf("[%d, %d] = BLANK CELL%n",
cell.getRowIndex(), cell.getColumnIndex());
}
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
}
}
}
Our program iterates the Excel file rows and cells and produce the following
output:
[0, 0] = NUMERIC; Value = 1.000000
[0, 1] = NUMERIC; Value = 2.000000
[0, 2] = STRING; Value = A
[0, 3] = STRING; Value = B
[0, 4] = BOOLEAN; Value = true
[1, 0] = BOOLEAN; Value = false
[1, 1] = STRING; Value = X
[1, 2] = STRING; Value = Y
[1, 3] = STRING; Value = Z
[1, 4] = NUMERIC; Value = 10.000000
Maven Dependencies
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.5</version>
</dependency>