Home Tutorials Poi Identify Excel's cell data type
Questions:Ask|Latest

 
 

Share on Google+Share on Google+

Identify Excel's cell data type

Advertisement
In this section, you will learn how you can identify cell's data type and handle it appropriately using Apache POI.

Identify Excel's cell data type

In this section, you will learn how you can identify cell's data type and handle it appropriately using Apache POI.

In Apache POI library, for fetching each type of data, there is separate method. For example, fetching numeric we use getNumericCellValue() and for fetching string we use getRichStringCellValue().

Due to the above reason, we need to identify each type of excel cell correctly to handle it appropriately. In the below example, you can learn how to identify and handle each type of  cell.

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.util.*;
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;

public class XLCheckData {
public static void main(String[] args) throws Exception {
String filename = "xls/test.xls";

FileInputStream fis = null;
try {
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();

int type = cell.getCellType();
if (type == HSSFCell.CELL_TYPE_STRING) {
System.out.println("[" + cell.getRowIndex() + ", "
+ cell.getColumnIndex() + "] = STRING; Value = "
+ cell.getRichStringCellValue().toString());
} else if (type == HSSFCell.CELL_TYPE_NUMERIC) {
System.out.println("[" + cell.getRowIndex() + ", "
+ cell.getColumnIndex() + "] = NUMERIC; Value = "
+ cell.getNumericCellValue());
} else if (type == HSSFCell.CELL_TYPE_BOOLEAN) {
System.out.println("[" + cell.getRowIndex() + ", "
+ cell.getColumnIndex() + "] = BOOLEAN; Value = "
+ cell.getBooleanCellValue());
} else if (type == HSSFCell.CELL_TYPE_BLANK) {
System.out.println("[" + cell.getRowIndex() + ", "
+ cell.getColumnIndex() + "] = BLANK CELL");
}
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} finally {
if (fis != null) {
fis.close();
}
}
}
}

OUTPUT

In console, you can see the following output :

[0, 0] = STRING; Value = EmpId
[0, 1] = STRING; Value = Name
[0, 2] = STRING; Value = Designation
[1, 0] = NUMERIC; Value = 1.0
[1, 1] = STRING; Value = ABC
[1, 2] = STRING; Value = Software Engineer
[2, 0] = NUMERIC; Value = 2.0
[2, 1] = STRING; Value = DFG
[2, 2] = STRING; Value = Sr Software Engineer
[3, 0] = NUMERIC; Value = 3.0
[3, 1] = STRING; Value = LOI
[3, 2] = STRING; Value = Team Leader
[4, 0] = NUMERIC; Value = 4.0
[4, 1] = STRING; Value = LKJ
[4, 2] = STRING; Value = Project Manager

Download Source Code

Advertisements

If you enjoyed this post then why not add us on Google+? Add us to your Circles



Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: August 12, 2011

Related Tutorials

Ask Questions?    Discuss: Identify Excel's cell data type  

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments
DMCA.com