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>
Wayan Saryada
Founder at Kode Java Org
I am a programmer, a runner, a recreational diver, currently live in the island of Bali, Indonesia. Mostly programming in Java, Spring Framework, Hibernate / JPA. If these posts help, you can support me, buy me a cup of coffee or tea. Thank you 🥳
Latest posts by Wayan Saryada (see all)
- How do I set the time of java.util.Date instance to 00:00:00? - October 24, 2019
- How to Install Consolas Font in Mac OS X? - March 29, 2019
- How do I clear the current command line in terminal? - February 14, 2019
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.