Shiro Emiya Shiro Emiya - 23 days ago 7
Java Question

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

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

Answer

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