Working with excel files using apache poi

Question

Is there any way to read or write both excel 2003 and 2007 format using apache poi.I know that we can use HSSF workbook for 2003 format and XSSF for 2007(correct me if am wrong).But is there any way to read both the format using any single workbook but not using separately.

Answer 1

Yes, you can do it. In fact, it's fairly widely documented on the Apache POI website!

If you already have code that uses HSSF, then you should follow the HSSF to SS converting guide for help on updating your code to be general across the two formats.

If you don't have any code yet, then follow the User API guide to get started - all the code in that is general for both formats. You can also look at the Quick Guide for some specific problems and how to solve them in the general way.

Answer 2

Use

WorkbookFactory.create(in);

Based on the javadoc, it

Creates the appropriate HSSFWorkbook / XSSFWorkbook from the given InputStream.

Answer 3

Try Workbook wb = WorkbookFactory.create(OPCPackage pkg);. It should work. However, if the XSSF is too big you will get an OutOfMemoryException and therefore you should use the event user model to read your file. In that case you should read your path and check the extension of your file, like following:

private boolean isXLS(String inputPath) {
    String tmp = inputPath.substring(inputPath.length() - 3,
            inputPath.length());
    if (tmp.equalsIgnoreCase("XLS"))
        return true;
    else
        return false;
}

Read the How-to for more information about the event user model.