Apache POI, convert xls (excel 2003) to xlsx (excel 2007)

Question

I would like to convert a xls to a xlsx file. Since I have only one tab, is it best to just iterate the rows and cells and copy them from one sheet to the other? Should the API provide a why to set entire rows or sheets instead of copying all of the information one-by-one with getters and setters?

This is what I'm having to do:

    InputStream in = new BufferedInputStream(new FileInputStream(inpFn));
    try {
        Workbook wbIn = new HSSFWorkbook(in);
        OPCPackage outPkg = OPCPackage.open(outFn);
        try {
            Workbook wbOut = new XSSFWorkbook(outPkg);
            int sheetCnt = wbIn.getNumberOfSheets();
            for (int i = 0; i < sheetCnt; i++) {
                Sheet s = wbIn.getSheetAt(0);
                Sheet s2 = wbOut.createSheet(s.getSheetName());
                Iterator<Row> rowIt = s.iterator();
                while (rowIt.hasNext()) {
                    Row row = rowIt.next();
                    // ....
                }
            }

        } finally {
            outPkg.close();
        }
    } finally {
        in.close();
    }

If you point me in the right direction, I'll make sure to update the post with the final code. I have not found any good code examples for this yet.

That looks about right. Don't forget to copy over styles and formats — Gagravarr, May 06 '13 at 16:26
I get an exception when I do that. It says `HSSFCellStyle cannot be cast to XSSFCellStyle` .. Looking at the code, it appears Apache POI do not dig into the spreadsheets style formats too much, just manipulate what is there. I don't see any way to convert it. — jcalfee314, May 07 '13 at 16:11
You can't cast it, no! That'll never work! You'll have to create a new one of the target type, and copy over the bits you care about (format strings, fonts, colours etc) — Gagravarr, May 08 '13 at 09:17

Apache POI, convert xls (excel 2003) to xlsx (excel 2007)

0 Answers0