Open xls and xlsx with Apache POI

Question

I want to open my excel file with Apache POI.

I don’t know if a file is xls or xlsx. I have only something like this:

InputStream myExcelFile = new ByteArrayInputStream(file.getData());

This way I can open xls file:

HSSFWorkbook hsf = new HSSFWorkbook(myxls);

And this way I can open xlsx file:

XSSFWorkbook xsf = new XSSFWorkbook(myxls);

How can I open both types of files if I don’t know format?

Try using either the extension of the file, or if that is not sufficient then determine based on the MIME type http://stackoverflow.com/questions/4212861/what-is-a-correct-mime-type-for-docx-pptx-etc using http://stackoverflow.com/questions/51438/getting-a-files-mime-type-in-java — EpicPandaForce, Feb 09 '15 at 12:56

score 6 · Answer 1 · answered Feb 09 '15 at 13:44

Workbook wb = WorkbookFactory.create(myExcelFile);
if (wb instanceof HSSFWorkbook) {
    // do whatever
} else if (wb instanceof SXSSFWorkbook) {
    // do whatever
} else if (wb instanceof XSSFWorkbook) {
    // do whatever
}

The aforementioned approach will work.

But incase you are dealing with large files, there is a possibility that you will get File as ByteArayInputStream. In this case, the bellow approach shall work.

ByteArayInputStream bais = new ByteArrayInputStream(file.getData());

if(bais != null && POIFSFileSystem.hasPOIFSHeader(bais)) {
    System.out.println(".xls extention excel file");
    //do whatever
}
else if(bais != null && POIXMLDocument.hasOOXMLHeader(bais)) {
    System.out.println(".xlsx extention excel file");
    //do whatever
}
else {
    //wrong file format. throw exception.
}

Paco Abato · Accepted Answer · 2015-02-09T13:05:19.740

4

This will do the work:

Workbook wb = WorkbookFactory.create(myExcelFile);

Then you can check the exact type created by the factory:

if (wb instanceof HSSFWorkbook) {
    // do whatever
} else if (wb instanceof SXSSFWorkbook) {
    // do whatever
} else if (wb instanceof XSSFWorkbook) {
    // do whatever
}

Anyway if you want to know the real type of the file before using it you can use Apache Tika.

edited Feb 09 '15 at 13:05

answered Feb 09 '15 at 12:57

Paco Abato

3,920
4
31
54

Which version are you using? – Paco Abato Feb 09 '15 at 13:10
2

Take a look at [this question](http://stackoverflow.com/questions/14067983/apache-poi-3-9-workbookfactory-method-not-found) – Paco Abato Feb 09 '15 at 13:13

score 3 · Answer 3 · answered Feb 09 '15 at 12:58

You can let POI do the job for you, by using WorkbookFactory to automatically have the file extension detected opened for you.

Workbook w = WorkbookFactory.create(new File("hello.xls"));

Of course, you can use Apache Tika to detect the file format for you!

score 1 · Answer 4 · answered Feb 09 '15 at 12:58

1

I woul recommend to use this FilenameUtils.getExtension from Apache Commons IO :

String ext = FilenameUtils.getExtension("path to the file");

answered Feb 09 '15 at 12:58

anquegi

11,125
4
51
67

Open xls and xlsx with Apache POI

4 Answers4