Saturday, June 13, 2009

How to read Excel file in Java using HSSF Jakarta POI

There are many way to read excel files in java

1. Using jexcelapi API that explained in previous post. (Only for .xls extenstion)
2. Using HSSF Jakarta POI, that will explain in this posting. (Only for .xls extenstion)

STEP1: Download poi-3.0-FINAL.jar from jakarta site or here.
SETP2: Write one Excel file say Binod2.xls
SETP3: Write ReadExcelFile.java
Both excel and java file should be in the same folder. Put poi-3.0-FINAL.jar in workspace.

ReadExcelFile.java

import org.apache.poi.hssf.usermodel.*;
import java.io.*;
import java.util.*;

public class ReadExcelFile {

public static void main(String[] args) throws Exception {
readWorkbook("Binod2.xls");
}

private static void readWorkbook(String filename) throws Exception {
InputStream input = new FileInputStream(filename);
HSSFWorkbook wb = new HSSFWorkbook(input);
for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) { HSSFSheet sheet = wb.getSheetAt(sheetIndex);
Iterator rowIter = sheet.rowIterator();
while (rowIter.hasNext()) {
HSSFRow row = (HSSFRow) rowIter.next();
Iterator cellIter = row.cellIterator();
while (cellIter.hasNext()) {
HSSFCell cell = (HSSFCell) cellIter.next();
printCellValue(cell); }
}
} input.close();
}

private static void printCellValue(HSSFCell c) {
int cellType = c.getCellType();
if (cellType == HSSFCell.CELL_TYPE_BOOLEAN) { System.out.println(c.getBooleanCellValue()); }
else if (cellType == HSSFCell.CELL_TYPE_STRING) { System.out.println(c.getRichStringCellValue().getString()); }
else if (cellType == HSSFCell.CELL_TYPE_FORMULA) { System.out.println(c.getCellFormula()); } else if (cellType == HSSFCell.CELL_TYPE_NUMERIC)
{ System.out.println(c.getNumericCellValue()); }
else if (cellType == HSSFCell.CELL_TYPE_ERROR)
{ System.out.println(c.getErrorCellValue()); }
}
}

5 comments:

  1. Is any way to read .xlsx file using java? Please share with us. Thanks in advance.

    ReplyDelete
  2. Hey can u give me solution to add multiple xls file in one xls but having multiple sheet.
    Like i have two xls 1.xls and 2. xls i want to combine this two in 3.xls as a sheet 1 and shhet 2.

    ReplyDelete
  3. You can. Use XSSF instead of HSSF for xlsx files.

    ReplyDelete
  4. Read each of the sheets from each Workbook. Then write them in a 3rd sheet. There are objects called HSSFSheet and HSSFWorkbook - google them and you should find the way.

    ReplyDelete
  5. You can ignore fist rows by using row numbers. If your excel file has header, just ignore first row, which has number zero. Here is another example of reading/writing Excel file in Java

    ReplyDelete

You can put your comments here (Either feedback or your Question related to blog)