0

i have a file .XLS extension but save as XMl spreadsheet 2003 , want to read the file and convert it into .XLS Extension with java code my code is below -

public class ExcelImport {

public boolean readStream(InputStream stream) throws InvalidFormatException {
    boolean success;

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

        Workbook workbook = WorkbookFactory.create(wrappedStream);
        //XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(wrappedStream.toString()));
        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();
    }
    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();
}

public static void main(String[] args) throws InvalidFormatException, IOException {

    ExcelImport excelImport = new ExcelImport();
    InputStream is = new FileInputStream("C:/Users/Desktop/Test.xls");
    excelImport.readStream(is);

} }

but when i am runnign it it gives error like below -

Exception in thread "main" java.lang.IllegalArgumentException: Your InputStream was neither an OLE2 stream, nor an OOXML stream at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:75)

Puneet Srivastava
  • 1
  • 2
  • 1
    This is simply not what `apache poi` is made for. It is able reading `*.xls` files in binary `Excel` `BIFF` format (aka `OLE2`) or `*.xlsx` files in `Office Open XML` (`OOXML`) format. But it is **not** able reading the `Microsoft Office Excel 2002 and Excel 2003 XML` format. – Axel Richter Oct 17 '19 at 07:07
  • Hi Alex is there any other api available in java by which i can read and convert it into proper .xls format . – Puneet Srivastava Oct 17 '19 at 07:10
  • 1
    See https://stackoverflow.com/questions/7089039/how-to-load-old-microsoft-office-xml-file-excel-using-java. – Axel Richter Oct 17 '19 at 07:15
  • Hi alex thanx a ton for helping me please let me know if there is any example to convert spreadsheet to .xls format with the help of xylem API. – Puneet Srivastava Oct 17 '19 at 09:07
  • i have a file having .xls format but internal its showing like a spreadsheet having XML structure . please give me a solution that how can i check the file format because when i am checking its saying simply .xls format . but how to get a file format that differentiate between normal .xls file and .xls file contains XML structure . please help me out to find the solution . – Puneet Srivastava Oct 30 '19 at 13:07
  • This is a new question. So please ask a new question. The question here is: "How to convert a file with .xls extension, but saved as XML Spreadsheet 2003, to .xls file format?" Now your question is "How to check the file format of an .xls file which possibly really is an XML Spreadsheet 2003 file?" But if you ask new, then also mention what you have tried and what checking tool is saying simply .xls format when you check. – Axel Richter Oct 30 '19 at 13:53
  • Btw.: `apche poi` provides [FileMagic](https://poi.apache.org/apidocs/dev/org/apache/poi/poifs/filesystem/FileMagic.html) which could be used to check. Of course not simply by file name but by `File` or `InputStream`. – Axel Richter Oct 30 '19 at 13:53

1 Answers1

0

You can try to run file path| hexdump -n 8 -e to make sure the file is actually what it says is. From the exception it seems that POI is not able to understand the file format.

Amol Londhe
  • 58
  • 2