reading excel file --> xlsx format with java

Question

I'm trying to read an excel file (xlsx NOT xls) but without any luck. I tried the jexcel api but it doesn't support xlsx extension, then I tried the Apache api which need to work and tried also the example from their web site but with no luck.I can't pass the read file phase and get a filenotfound exception. also used the poi-ooxml-3.6.jar,xmlbeans-2.6.0 and poi-3.7.jar.

can anyone please explain to me what types of api/classes/libraries I need to use and how to use it with eclipse (the external libraries/classes/api is totally new to me)

many thanks in advance

Solution 1

Apache POI is a good librairy to read xsl and xslx format.

To read a file just instanciate a new XSSFWorkbook by passing a new FileInputStream with the path of the Excel file:

XSSFWorkbook workbook = new XSSFWorkbook(OPCPackage.open(new File("foo.xslx")));

Or with an input stream (takes a little more memory than a file):

XSSFWorkbook workbook = new XSSFWorkbook(myInputStream);

After having a XSSFWorkbook, use it to iterate through all the cell (example).

Download Apache POI 3.9 here

Solution 2

Using POI 3.8 and poi-ooxml-3.8, I've had success with something like this (i've not tried older versions):

InputStream is = //Open file, and get inputstream
Workbook workBook = WorkbookFactory.create(is);
int totalSheets = workBook.getNumberOfSheets();
for (int i = 0; i <= totalSheets - 1; i++) {
  Sheet sheet = workBook.getSheetAt(i);
  // Do something with the sheet
}

WorkbookFactory will automatically determine whether the file is the old XLS, or newer XLSX and return the correct version of Workbook. The rest of the code is just a sample of iterating through the sheets contained within.

Solution 3

Add following dependencies in your code.

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

Also to read excel file use the following code, it will work for both .xls as well as .xlsx file.

Workbook workbook = WorkbookFactory.create(inputStream);

Comments

[user1864229]

the Apache poi 3.9 is a library and nor one jar file.how do i use it?

[user1864229]

it gives me the following exception:Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlException at readExcel.main(readExcel.java:25) Caused by: java.lang.ClassNotFoundException: org.apache.xmlbeans.XmlException at java.net.URLClassLoader$1.run(Unknown Source) at java.net.URLClassLoader$1.run(Unknown Source) at java.security.AccessController.doPrivileged(Native Method) at java.net.URLClassLoader.findClass(Unknown Source) at java.lang.ClassLoader.loadClass(Unknown Source) at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)

[user1864229]

what to write in the inputstream line,is=?

[jcern]

You need to supply an InputStream that points to the file you are looking to read, and which one you use depends on how you are looking to get at the file. I would probably start with this: docs.oracle.com/javase/6/docs/api/java/io/FileInputStream.ht‌​ml

[Benoit Wickramarachi]

You need to add also poi-ooxml.jar, you can download it from repo1.maven.org/maven2/org/apache/poi/poi-ooxml/3.9

[O.C.]

Do you know why it takes a little more memory than a file ?