Read & Write Excel using Apache POI

The code snippet below shows how you can use Apache POI to read and write to excel sheet (the example works with both MS Excel and Open Office). This class is designed to be used as Utility class and you should feel free to refine it more based on your need.


package com.dirtyyourhands;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.dirtyyourhands.ReadExcelWithPOI;

public class ReadExcelSheet {
  /**
   * This method helps one read the excel - this method can read both .xls and .xlsx formats.
   * 
   * @param fileName
   *            String - excel sheet file name
   * @throws Exception
   */
  public void readExcelSheet(String fileName) throws Exception {
    Workbook workbook = null;
    Sheet sheet = null;
    InputStream file = null;

    try {
      /**
       * Create a folder named rsrc and put the excel in com/dirtyyourhands folder so we can
       * use load it using ResourceAsStream
       */
      file = ReadExcelWithPOI.class.getClassLoader().getResourceAsStream(fileName);

      /**
       * You need to use XSSFWorkbook in case of .xlsx files else HSSFWorkbook
       */
      if (fileName.endsWith(".xlsx")) {
        // Get the workbook instance for XLS file
        workbook = new XSSFWorkbook(file);

        // Get first sheet from the workbook
        sheet = ((XSSFWorkbook) workbook).getSheetAt(0);
      } else {
        // Get the workbook instance for XLS file
        workbook = new HSSFWorkbook(file);

        // Get first sheet from the workbook
        sheet = ((HSSFWorkbook) workbook).getSheetAt(0);
      }

      // Iterate through each rows from first sheet
      Iterator<Row> rowIterator = sheet.iterator();
      String strCellValue = null;
      while (rowIterator.hasNext()) {
        Row row = rowIterator.next();

        // For each row, iterate through each columns
        Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
          strCellValue = null;
          Cell cell = cellIterator.next();
          switch (cell.getCellType()) {
          case Cell.CELL_TYPE_NUMERIC:
            int i = (int) cell.getNumericCellValue();
            strCellValue = String.valueOf(i);
            break;
          case Cell.CELL_TYPE_STRING:
            strCellValue = cell.getStringCellValue();
            break;

          case Cell.CELL_TYPE_BLANK:
            break;

          case Cell.CELL_TYPE_BOOLEAN:
            System.out.println(cell.getBooleanCellValue());
            break;

          case Cell.CELL_TYPE_FORMULA:
            System.out.println(cell.getCellFormula());
            break;

          case Cell.CELL_TYPE_ERROR:
            System.out.println(cell.getErrorCellValue());
            break;

          }

          /**
           * TODO: Add your code with what you want to do with strCellValue You need to
           * note that we are converting all values to String
           */
          System.out.println(strCellValue);
        }
      }
      file.close();

      /**
       * The below code snippet helps you to write the workbook to excel sheet, this is
       * helpful in cases when you want to do some processing on excel sheet and save that
       * processed data.
       */
      FileOutputStream out = new FileOutputStream(new File("C:\\dirty-your-hands.xls")); // or
                                                // dirty-your-hands.xlsx
      workbook.write(out);
      out.close();
    } catch (FileNotFoundException e) {
      e.printStackTrace();
    } catch (IOException e) {
      e.printStackTrace();
    } finally {
      if (null != file)
        file.close();
    }
  }
}