Sunday, December 4, 2011

Read Contents of an Excel File using Java and POI

To get the content from an Excel file in Java we can use Apache's POI library. First a file is imported from disk passing a FileInputStream object to a org.apache.poi.hssf.usermodel.HSSFWorkbook object. In this example only the first sheet was parsed. To access the cell content of the entire sheet, each row is iterated followed by iterating over each cell in the row. For each cell, the content is simply read using the getStringCellValue() method from the HSSFCell object. The following sample code was successfully tested with POI versions 3.6 and 3.7.

Here is a screenshot of the test file I used for this example:





import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;

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;

// The following example code demonstrates how to parse the contents
// of all cells in the first sheet of an Excel spreadsheet file

public class ReadExcelFile {

    public static void main(String[] args) {

        FileInputStream fileInputStream = null;
        try {
            fileInputStream = new FileInputStream("/Temp/Test1.xls");

            HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);

            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();

                    System.out.println(cell.getStringCellValue());
                }
            }

        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (fileInputStream != null) {
                try {
                    fileInputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

Here is the output of the program:
Hello
There
Piece of cake!!!

1 comment:

sareen said...

How to read the content of remaining sheets...?