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

Apache POI 3.7 is quite old, you should try the newest version — Aviram Segal
– Aviram Segal, Commented Dec 21, 2012 at 20:45
Show us some code. And, make sure that the file you are trying to read from Java is 1) present in the right location, and 2) the path for the file is correct in your code. FileNotFoundException is thrown if one of the things above is wrong. — legendofawesomeness
– legendofawesomeness, Commented Dec 21, 2012 at 20:45
this is for example one of the many codes i've tried: public static void main(String[] args) throws Exception, FileNotFoundException, IOException { String filename = "C:\\1.xlsx"; Workbook workbook = WorkbookFactory.create(new FileInputStream(filename)); System.out.println("Number Of Sheets" + workbook.getNumberOfSheets()); Sheet sheet = workbook.getSheetAt(0); System.out.println("Number Of Rows:" + sheet.getLastRowNum()) — user1864229
– user1864229, Commented Dec 21, 2012 at 21:12
Dont create a Workbook, to read a xslx file you need to create a XSSFWorkbook, see my answer below for an example. — Benoit Wickramarachi
– Benoit Wickramarachi, Commented Dec 21, 2012 at 21:20

Gagravarr · Accepted Answer · 2012-12-22 00:48:18Z

5

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

edited Dec 22, 2012 at 0:48

Gagravarr

48.5k11 gold badges114 silver badges158 bronze badges

answered Dec 21, 2012 at 21:19

Benoit Wickramarachi

6,2645 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user1864229 Over a year ago

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

user1864229 Over a year ago

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)

Benoit Wickramarachi Over a year ago

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. Over a year ago

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

jcern · Accepted Answer · 2012-12-21 21:19:34Z

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.

answered Dec 21, 2012 at 21:19

jcern

7,8584 gold badges42 silver badges48 bronze badges

2 Comments

user1864229 Over a year ago

what to write in the inputstream line,is=?

jcern Over a year ago

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.html

Pooja Aggarwal · Accepted Answer · 2018-08-27 07:08:38Z

1

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);

answered Aug 27, 2018 at 7:08

Pooja Aggarwal

1,2037 silver badges14 bronze badges

Comments

ashario · Accepted Answer · 2013-12-09 21:14:03Z

0

I ended up using this modification of AbstractExcelView

https://github.com/hmkcode/Spring-Framework/blob/master/spring-mvc-json-pdf-xls-excel/src/main/java/com/hmkcode/view/abstractview/AbstractExcelView.java

answered Dec 9, 2013 at 21:14

ashario

2,7422 gold badges25 silver badges19 bronze badges

Comments

patidarsnju · Accepted Answer · 2018-08-30 12:34:55Z

Add dependencies in pom.xml -

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

    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.15</version>
    </dependency>

    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>ooxml-schemas</artifactId>
        <version>1.3</version>
    </dependency>

Excel 2007 or later (.xlsx) - sample code -

 //reading data from byte array
            OPCPackage pkg = OPCPackage.open(new ByteArrayInputStream(data));
            Workbook wb = new XSSFWorkbook(pkg);
Sheet sheet = wb.getSheetAt(0);
            Iterator<Row> rows = sheet.rowIterator();

            while (rows.hasNext()) {
                int j = 5;
                Person person= new Person ();
                Row row = rows.next();
                if (row.getRowNum() > 0) {
                    person.setPersonId((int)(row.getCell(0).getNumericCellValue()));
                    person.setFirstName(row.getCell(1).getStringCellValue());
                    person.setLastName(row.getCell(2).getStringCellValue());
                    person.setGroupId((int)(row.getCell(3).getNumericCellValue()));
                    person.setUserName(row.getCell(4).getStringCellValue());
                    person.setCreditId((int)(row.getCell(5).getNumericCellValue()));
                }

            }

2)

        //reading from a file 
        File file = new File(pathxlsx);
        FileInputStream fis = new FileInputStream(file);
        Workbook wb = new XSSFWorkbook(fis);

        Sheet sheet = wb.getSheetAt(0);
        Iterator<Row> rows = sheet.rowIterator();

        while (rows.hasNext()) {
            int j = 5;
            Person person= new Person ();
            Row row = rows.next();
            if (row.getRowNum() > 0) {
                person.setPersonId((int)(row.getCell(0).getNumericCellValue()));
                person.setFirstName(row.getCell(1).getStringCellValue());
                person.setLastName(row.getCell(2).getStringCellValue());
                person.setGroupId((int)(row.getCell(3).getNumericCellValue()));
                person.setUserName(row.getCell(4).getStringCellValue());
                person.setCreditId((int)(row.getCell(5).getNumericCellValue()));
            }

        }

Excel 1998-2003 file (.xls) - you may use HSSF library. just use : Workbook wb = new HSSFWorkbook(pkg);

Collectives™ on Stack Overflow

reading excel file --> xlsx format with java

5 Answers 5

4 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related