Java POI API: Convert from *.xlsx to *.xls

Question

I have a small problem. Wanna convert the new excel files (.xlsx) into the old one (.xls) with the POI API on Java.

I think it is a mind problem, but I don't know which fault exist.

I used these code here:

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
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.xssf.usermodel.XSSFWorkbook;

public class XLSX2XLS{
    private String outFn;
    private File inpFn;

    public XLSX2XLS(File inpFn){
        this.outFn = inpFn + ".xls";
        this.inpFn = inpFn;
    }

    public void xlsx2xls_progress() throws InvalidFormatException,IOException {
        InputStream in = new FileInputStream(inpFn);
        try {
            XSSFWorkbook wbIn = new XSSFWorkbook(in);
            File outF = new File(outFn);
            if (outF.exists()) {
                outF.delete();
            }

            Workbook wbOut = new HSSFWorkbook();
            int sheetCnt = wbIn.getNumberOfSheets();
            for (int i = 0; i < sheetCnt; i++) {
                Sheet sIn = wbIn.getSheetAt(0);
                Sheet sOut = wbOut.createSheet(sIn.getSheetName());
                Iterator<Row> rowIt = sIn.rowIterator();
                while (rowIt.hasNext()) {
                    Row rowIn = rowIt.next();
                    Row rowOut = sOut.createRow(rowIn.getRowNum());

                    Iterator<Cell> cellIt = rowIn.cellIterator();
                    while (cellIt.hasNext()) {
                        Cell cellIn = cellIt.next();
                        Cell cellOut = rowOut.createCell(cellIn.getColumnIndex(), cellIn.getCellType());

                        switch (cellIn.getCellType()) {
                        case Cell.CELL_TYPE_BLANK: break;

                        case Cell.CELL_TYPE_BOOLEAN:
                            cellOut.setCellValue(cellIn.getBooleanCellValue());
                            break;

                        case Cell.CELL_TYPE_ERROR:
                            cellOut.setCellValue(cellIn.getErrorCellValue());
                            break;

                        case Cell.CELL_TYPE_FORMULA:
                            cellOut.setCellFormula(cellIn.getCellFormula());
                            break;

                        case Cell.CELL_TYPE_NUMERIC:
                            cellOut.setCellValue(cellIn.getNumericCellValue());
                            break;

                        case Cell.CELL_TYPE_STRING:
                            cellOut.setCellValue(cellIn.getStringCellValue());
                            break;
                        }

                        {
                            CellStyle styleIn = cellIn.getCellStyle();
                            CellStyle styleOut = cellOut.getCellStyle();
                            styleOut.setDataFormat(styleIn.getDataFormat());
                        }cellOut.setCellComment(cellIn.getCellComment());

                        }
                }
            }
            OutputStream out = new BufferedOutputStream(new FileOutputStream(outF));
            try {
                wbOut.write(out);
            } finally {
                out.close();
            }
        } finally {
            in.close();
        }
    }
}

And Java tells me that here:

Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlException
    at XLSX2XLS.xlsx2xls_progress(XLSX2XLS.java:35)
    at Workflow.main(Workflow.java:32)
Caused by: java.lang.ClassNotFoundException: org.apache.xmlbeans.XmlException
    at java.net.URLClassLoader$1.run(Unknown Source)
    at java.net.URLClassLoader$1.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    ... 2 more

I test these class with POI 3.9. and 3.10, on both the same error calls. Java: JDK 7 OS: Win 8.1 x64

I hope get enough information about my problem. Thanks for your helps.

Greetings

Solution

Please be aware that as the new XSSF supported Excel 2007 OOXML (.xlsx) files are XML based.

You need to add extra 2 jars to make POI work on (.xlsx) Excel file.

Please add xmlbeans2.3.0.jar and dom4j-1.6.jar to your classpath. These 2 jars are the dependency jars for handling .xlsx Excel file in POI Library.

If you have download POI source code, You can find these 2 jars under the following folder:

\poi-bin-3.9-20121203\poi-3.9\ooxml-lib\

If not, you can download them from the following site:

xmlBean2.3.0.jar

dom4j-1.6.jar

Comments

[Lyçann H.]

Thanks for your help, it works now (after i include the xml shema into the path too) For the other helps/tipps, they are nice solutions, but i write a java tool, that manage alone, without any external software ;) Thanks at all!