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.3</version>
</dependency>
Latest posts by Wayan (see all)
- How do I create a string of repeated characters? - September 1, 2023
- How do I convert datetime string with optional part to a date object? - August 28, 2023
- How do I split large excel file into multiple smaller files? - April 15, 2023
Perfect. getCellType() is depricated with POI 3.17, given solution is perfect fit.
Here in the above example the numeric cell returns
float
instead ofInteger
. Value =10
but output =10.0
. How to fix the issue?Hi Ramesh,
You can convert the
double
value returned by thecell.getNumericCellValue()
method using the following snippet:or
Hi 🙂
you can also use the following snipped:
// (int) converts Double into Integer
Greetings Emrah Dogan
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.
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:This will give you an integer value of the cell.
Where can I get the packages?
And if I want to insert this data into my database, what would be the code please?
Hi Rayene,
You can check some example on JDBC section in this blog, here is the link https://kodejava.org/category/java/jdbc-api/
I want to change from different date format into standard date format in excel sheet.
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.
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.