How to read Excel file with *.xlsx Extension?

Question

I am reading excel file*(2000000 row with 6 column)* with xlsx extension. for reading it using Java Excel API but it throw exception

jxl.read.biff.BiffException: Unable to recognize OLE stream

when try same file with xls extension it read perfectly but it reading only 65536 Row data only remaining rest of row unread.please help me how i am able to read remaining row with xlsx extension.

Thanks

score 6 · Accepted Answer · answered Jun 17 '11 at 08:52

6

try use Apache POI http://poi.apache.org/spreadsheet/index.html

answered Jun 17 '11 at 08:52

Marcin Michalski

1,266
13
17

score 1 · Answer 2 · answered Sep 01 '15 at 05:52

For reading xlsx file we use following jars,

1.dom4j-1.6.1.jar 2.org-apache-commons-codec.jar 3.poi-3.7.jar 4.poi-ooxml-3.5-FINAL.jar 5.poi-ooxml-schemas-3.11-beta2.jar 6.stax-api-1.0.1.jar 7.xmlbeans-2.6.0.jar

This is function through which you can read the the xlsx file.

public static void readXLSX(String path) {
    File file = new File(path);
    String value = "";
    XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(path));
    XSSFSheet sheet = wb.getSheetAt(0);

    Row row;
    Cell cell;

    Iterator<Row> rowIterator = sheet.iterator();

    while (rowIterator.hasNext()) {
        row = rowIterator.next();
        Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
                    cell = cellIterator.next();
                    switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_BOOLEAN:
                            value = "" + cell.getBooleanCellValue();
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            value = "" + cell.getNumericCellValue();
                            break;
                        case Cell.CELL_TYPE_STRING:
                            value = "" + cell.getStringCellValue();
                            break;
                        case Cell.CELL_TYPE_BLANK:
                            value = " ";
                            break;
                        default:
                            break;
                    }
                    System.out.println("Value: "+value);
    }

}

score 0 · Answer 3 · answered Jun 17 '11 at 08:55

0

The problem is that in Office '07 Microsoft changed its document format to something which is more or less a zip file that contains a large amount of XML. The Java Excel API relies on the old file format. To read something from Office '07, use Apache POI.

answered Jun 17 '11 at 08:55

cwallenpoole

79,954
26
128
166

score -1 · Answer 4 · edited May 23 '17 at 12:14

-1

Don't read Microsoft formats. If you want an excel document read a CSV, comma separated list.
Your library doesn't seem to support xlsx
Take a look here Choosing an excel java api

edited May 23 '17 at 12:14

Community

1
1

answered Jun 17 '11 at 08:54

Mikhail

7,749
11
62
136

4

-1: Answers like this aren't very constructive -- I'd guess that Sam has some specific business use case where he needs to support parsing Excel files. As someone who has had to parse WML files, I completely understand why he might have that problem. – cwallenpoole Jun 17 '11 at 08:57
I also had to parse WML. The solution was not to parse WML. Trying to maintain compatibility with with WML is a terrible long term strategy. BUT if he wanted to do so there is #2 and #3. – Mikhail Jun 17 '11 at 22:27

How to read Excel file with *.xlsx Extension?

4 Answers4

Linked