How do I obtain Excel’s cell data type?

By Wayan Saryada in Apache POI 10 Comments

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.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 org.apache.poi.ss.usermodel.CellType;

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 rows = sheet.rowIterator();
            while (rows.hasNext()) {
                HSSFRow row = (HSSFRow) rows.next();
                Iterator cells = row.cellIterator();
                while (cells.hasNext()) {
                    HSSFCell cell = (HSSFCell) cells.next();

                    CellType type = cell.getCellType();
                    if (type == CellType.STRING) {
                        System.out.println("[" + cell.getRowIndex() + ", "
                            + cell.getColumnIndex() + "] = STRING; Value = "
                            + cell.getRichStringCellValue().toString());
                    } else if (type == CellType.NUMERIC) {
                        System.out.println("[" + cell.getRowIndex() + ", "
                            + cell.getColumnIndex() + "] = NUMERIC; Value = "
                            + cell.getNumericCellValue());
                    } else if (type == CellType.BOOLEAN) {
                        System.out.println("[" + cell.getRowIndex() + ", "
                            + cell.getColumnIndex() + "] = BOOLEAN; Value = "
                            + cell.getBooleanCellValue());
                    } else if (type == CellType.BLANK) {
                        System.out.println("[" + cell.getRowIndex() + ", "
                            + cell.getColumnIndex() + "] = BLANK CELL");
                    }
                }
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
    }
}

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

Maven Dependencies

<!-- https://search.maven.org/remotecontent?filepath=org/apache/poi/poi/4.1.0/poi-4.1.0.jar -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.0</version>
</dependency>

Maven Central

Latest posts by Wayan Saryada (see all)
Views: 42,749

10 Comments

  2. Here in the above example the numeric cell returns float instead of Integer. Value = 10 but output = 10.0. How to fix the issue?

    Reply

    • Hi Ramesh,

      You can convert the double value returned by the cell.getNumericCellValue() method using the following snippet:

      int value = new Double(cell.getNumericCellValue()).intValue();

      or

      int value = new BigDecimal(cell.getNumericCellValue()).setScale(0, RoundingMode.HALF_UP).intValue();
      Reply

    • Hi 🙂

      you can also use the following snipped:

      Integer i = (int) sheet.getRow(2).getCell(2).getNumericCellValue();

      // (int) converts Double into Integer

      Greetings Emrah Dogan

      Reply

  3. Thank you for this solution. But every value can’t be int, some values were float. Please provide the condition statement for int. If it is int then I can convert using the above snippet. If not int I should use the normal statement.

    I am not able to get whether it is int or float.

    Reply

    • Hi Ramesh,

      I think it is the job of the programmer to decide whether to convert the cell value to either int or float using a conversion or casting. The CellType.NUMERIC only tells that the cell could be a whole numbers, fractional numbers or dates. So you can do something like:

      int value = new Double(cell.getNumericCellValue()).intValue();

      This will give you an integer value of the cell.

      Reply

Leave a Reply to Ramesh Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.