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. You can support my works by donating here. Thank you ?
Latest posts by Wayan Saryada (see all)
- 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
- How do I generate random alphanumeric strings? - February 7, 2019
Perfect. getCellType() is depricated with POI 3.17, given solution is perfect fit.
Here in the above example the numeric cell returns
floatinstead ofInteger. Value =10but output =10.0. How to fix the issue?Hi Ramesh,
You can convert the
doublevalue 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.NUMERIConly 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?