How to use both 2003 (xls) and 2007 (xlsx) formats in Apache POI

How to use both 2003 (xls) and 2007 (xlsx) formats in Apache POI

Apache POI is the major way to handle Excel in Java.

Download Apache POI from here.

If the version of Apache POI is up to about 3.4 (I checked 3.2), the libraries handled by pre-2003 Excel and 2007 Excel are different.

2003（org.apache.poi.hssf.～）
2007（org.apache.poi.xssf.～）

Starting with Apache POI version 3.5, libraries are available to handle both 2003 and 2007.

2003 or 2007（org.apache.poi.ss.～）

org.apache.poi.ss.usermodel.Cellの定数

The Cell class has the following static constants

CELL_TYPE_NUMERIC
CELL_TYPE_STRING
CELL_TYPE_FORMULA
CELL_TYPE_BLANK
CELL_TYPE_BOOLEAN
CELL_TYPE_ERROR

Below is an example of its use.

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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.ss.usermodel.WorkbookFactory;

public class ApachePoi {

  /**
    * @param args
    * @throws IOException
    * @throws FileNotFoundException
    * @throws InvalidFormatException
  */
  public static void main(String[] args) throws FileNotFoundException, IOException, InvalidFormatException {

    Workbook wb = WorkbookFactory.create(new FileInputStream("D:\\sample.xlsx"));
    Sheet sheet = wb.getSheet("Sheet1");

    for (Row row : sheet) {
      for (Cell cell : row) {
        System.out.println(getCellValue(cell, "yyyy/MM/dd"));
      }
    }
  }

  public static Object getCellValue(Cell cell,String date) {
    switch (cell.getCellType()) {

      case Cell.CELL_TYPE_STRING:
        return cell.getRichStringCellValue().getString();

      case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
          Date dateValue = cell.getDateCellValue();
          DateFormat dateFormat = new SimpleDateFormat(date);
          return dateFormat.format(dateValue);
        } else {
          return cell.getNumericCellValue();
        }

      case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue();

      case Cell.CELL_TYPE_FORMULA:
        return cell.getCellFormula();

      default:
        return null;
    }
  }
}

Libraries required by Apache POI

Download poi-bin-3.16-20170419.zip, some jars are not needed.

The required jars are listed below.

poi-3.16.jar
poi-ooxml-3.16.jar
poi-ooxml-schemas-3.16.jar
ooxml-libフォルダ配下に存在するxmlbeans-2.6.0.jar

高橋