reading excel file --> xlsx format with java

2
votes

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

5
javaapacheexcelapache-poixlsx
Apache POI 3.7 is quite old, you should try the newest version - Aviram Segal
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
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
Dont create a Workbook, to read a xslx file you need to create a XSSFWorkbook, see my answer below for an example. - Benoit Wickramarachi

5 Answers

5
votes

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

2
votes

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.

1
votes

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

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