"Your InputStream was neither an OLE2 stream, nor an OOXML stream" while reading .xslx File

Question

I want to read Excel files which I get passed over REST as InputStream. For this I have the class ExcelImport:

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;

public class ExcelImport {
  public boolean readStream(InputStream stream) {
        boolean success;

        try {
            byte[] bytes = getBytes(stream);
            InputStream wrappedStream = new ByteArrayInputStream(bytes);

            Workbook workbook = WorkbookFactory.create(wrappedStream);

            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet sheet = workbook.getSheetAt(i);
                for (Row row : sheet) {
                    IterateThroughRow(row);
                }
            }
            success = true;
        } catch (FileNotFoundException e) {
            success = false;
            e.printStackTrace();
        } catch (IOException e) {
            success = false;
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            success = false;
            e.printStackTrace();
        }
        return success;
    }

    private void IterateThroughRow(Row row) {
        Iterator<Cell> cellIterator = row.cellIterator();

        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();

            switch (cell.getCellType()) {
                //do something with the content...
                case Cell.CELL_TYPE_STRING:
                    cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    cell.getNumericCellValue();
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    cell.getBooleanCellValue();
                    break;
                default:
            }
        }
    }

    public static byte[] getBytes(InputStream is) throws IOException {
        ByteArrayOutputStream buffer = new ByteArrayOutputStream();

        int len;
        byte[] data = new byte[100000];
        while ((len = is.read(data, 0, data.length)) != -1) {
            buffer.write(data, 0, len);
        }

        buffer.flush();
        return buffer.toByteArray();
    }
}

If I run this with this:

ExcelImport excelImport = new ExcelImport();
InputStream is = new FileInputStream("/path/to/file.xlsx");
excelImport.readStream(is);

It works fine. But if I use this class with a InputStream from a PUT over REST I get this Exception: java.lang.IllegalArgumentException: Your InputStream was neither an OLE2 stream, nor an OOXML stream

I wrap the stream because if I don't do it, I get this Exception: Package should contain a content type part. I got this from here.

Isn't it possible to read Excel files using Apache POI with a Stream from REST? Or am I doing something else wrong?

How are you sending the file to your code? If you have your code save the sent file to disk, are the two the same, or has something corrupted it in the process? — Gagravarr, Apr 27 '15 at 19:00
I checked the `InputStream` I get when I want to send it with PUT and this stream does not contain any data. Thanks for your hint, to check if something is currupted while sending. — Peter, Apr 28 '15 at 08:18
@Gagravarr, if you like, you can post your comment as answer and I'll accept it because your hint that I have to check if the stream is corrupted solved it for me. — Peter, May 04 '15 at 08:01

score 2 · Accepted Answer · answered May 04 '15 at 09:20

As a general guide for this sort of problem, you should try saving the file received by your server to disk. Having done that, check that the source and received file sizes match, and have the same checksum (eg md5). Surprisingly often, the problem turns out to be the transfer, eg loosing the first bit of the file, or the last bit, or corrupting certain characters when uploading as ascii not binary, or something like that

For your specific case of a bug meaning no data is sent, there's good news. As of r1677562, a more helpful EmptyFileException will be thrown in place of the more general Your InputStream was neither an OLE2 stream, nor an OOXML stream exception given for all other invalid types. This should hopefully make it easier to spot the cause of this style of bug in your code. That exception will be in POI 3.12 final (and later.

This helped me figure out my problem, it was corrupted data due to using two different Base64 encoding libraries on accident rather than just using one library to encode and decode. — Toofy, Jul 23 '18 at 21:50

score 0 · Answer 2 · answered Jul 28 '19 at 14:45

0

I had the same problem and I simply renamed and resaved as .xlsx the spreadsheetand it worked.

answered Jul 28 '19 at 14:45

Djandli

131
1
5

"Your InputStream was neither an OLE2 stream, nor an OOXML stream" while reading .xslx File

2 Answers2