Java examples on org.apache.poi

How do I obtain Excel's cell data type?

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 contained in a file named celltype.xls. The below matrix depict how to file is look like.

    |   COL
ROW |   0       1   2   3   4
----|-------------------------
0   |   1       2   A   B   TRUE
1   |   FALSE   X   Y   Z   10


package org.kodejava.example.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 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";

        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();
            }
        }
    }
}

Our program iterates the Excel file rows and cells and produce the following output:

[0, 0] = NUMERIC; Value = 1.0
[0, 1] = NUMERIC; Value = 2.0
[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.0