How do I obtain Excel’s cell data type?

By Wayan in Apache POI 12 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.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

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

Maven Central

Latest posts by Wayan (see all)
Views: 68,798

12 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

  7. Hi Wayan,

    Am facing a scenario as the cell value is 0.00 and cell format is text, while checking with cell.cell.getCellType() == CellType.STRING it is true. So the cell format decides the value as string.

    Reply

    • Hi Praveen,

      I think it is the correct behavior of the API, but if you believe that the value in the cell is a numeric value, you can try to parse or convert the value from string to numeric. For example you can use the Double.parseDouble() method.

      Reply

Leave a Reply

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