Question

java导入与导出excel,兼容excel2003以及excel2007

org.apache.poi.POIXMLException: java.lang.reflect.InvocationTargetException
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)

at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at org.apache.poi.xssf.usermodel.XSSFFactory.createDocumentPart(XSSFFactory.java:59)
... 131 more
Caused by: java.lang.ExceptionInInitializerError
at java.lang.reflect.Field.getFieldAccessor(Field.java:899)
at java.lang.reflect.Field.get(Field.java:358)
at org.apache.xmlbeans.XmlBeans.typeSystemForClassLoader(XmlBeans.java:770)
at org.openxmlformats.schemas.drawingml.x2006.main.ThemeDocument.<clinit>(Unknown Source)
at org.openxmlformats.schemas.drawingml.x2006.main.ThemeDocument$Factory.parse(Unknown Source)
at org.apache.poi.xssf.model.ThemesTable.<init>(ThemesTable.java:39)
... 136 more
Caused by: java.lang.RuntimeException: Could not instantiate SchemaTypeSystemImpl (java.lang.reflect.InvocationTargetException): is the version of xbean.jar correct?
at schemaorg_apache_xmlbeans.system.sE130CAA0A01A7CDE5A2B4FEB8B311707.TypeSystemHolder.loadTypeSystem(Unknown Source)
at schemaorg_apache_xmlbeans.system.sE130CAA0A01A7CDE5A2B4FEB8B311707.TypeSystemHolder.<clinit>(Unknown Source)
... 146 more
Caused by: java.lang.reflect.InvocationTargetException
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
... 148 more
Caused by: org.apache.xmlbeans.SchemaTypeLoaderException: XML-BEANS compiled schema: Incompatible minor version - expecting up to 23, got 24 (schemaorg_apache_xmlbeans.system.sE130CAA0A01A7CDE5A2B4FEB8B311707.index) - code 3
at org.apache.xmlbeans.impl.schema.SchemaTypeSystemImpl$XsbReader.<init>(SchemaTypeSystemImpl.java:1522)
at org.apache.xmlbeans.impl.schema.SchemaTypeSystemImpl.initFromHeader(SchemaTypeSystemImpl.java:260)
at org.apache.xmlbeans.impl.schema.SchemaTypeSystemImpl.<init>(SchemaTypeSystemImpl.java:183)
... 152 more

我的报这样的错

Answer 1

版本没有判断
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* xls导入时获取Excel里的内容,兼容Excel2003跟Excel2007版本
*
* @author
*
*/
public class ImportUtil {
private static final int version2003 = 2003;
private static final int version2007 = 2007;
private static int version = version2003;
private static Workbook wb = null;
private static Sheet sheet = null;
private static Cell cell = null;
private static org.apache.poi.ss.usermodel.Row row = null;

public static void main(String[] args) throws FileNotFoundException, IOException{
// ImportUtil i = new ImportUtil();
// String[][] data = i.getData("D:/workspace/jz.xlsx", 0);
// String[][] data = i.getData("D:/workspace/world.xls", 0);
}
/**
* * * 读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行
*
* @param excelFilePath
* 读取数据的源Excel路径
* @return 读出的Excel中数据的内容
* @throws FileNotFoundException
* @throws IOException
*/
public static String[][] getData(String excelFilePath)
throws FileNotFoundException, IOException {
if (excelFilePath.endsWith(".xls"))
version = version2003;
else if (excelFilePath.endsWith(".xlsx"))
version = version2007;
InputStream stream = null;
if (version == version2003) {
stream = new FileInputStream(excelFilePath);
wb = (Workbook) new HSSFWorkbook(stream);
} else if (version == version2007) {
wb = (Workbook) new XSSFWorkbook(excelFilePath);
}
sheet = wb.getSheetAt(0);
// 行数(从0开始,相当于最后一行的索引),列数
int count_row = sheet.getLastRowNum(), count_cell = sheet.getRow(0).getPhysicalNumberOfCells();
String[][] returnArray = new String[count_row][count_cell];
for (int i = 0; i < count_row; i++) {
for (int j = 0; j < count_cell; j++) {
row = sheet.getRow(i + 1);
cell = ((org.apache.poi.ss.usermodel.Row) row).getCell(j);

if(cell == null){
returnArray[i][j] = "";
}else{
int type = cell.getCellType(); // 得到单元格数据类型
String k = "";
switch (type) { // 判断数据类型
case Cell.CELL_TYPE_BLANK:
k = "";
break;
case Cell.CELL_TYPE_BOOLEAN:
k = (cell.getBooleanCellValue() == true ? "Y"
: "N");
break;
case Cell.CELL_TYPE_ERROR:
k = cell.getErrorCellValue() + "";
break;
case Cell.CELL_TYPE_FORMULA:
if (!cell.getStringCellValue().equals("")) {
k = cell.getStringCellValue();
} else {
k = cell.getNumericCellValue() + "";
}
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
if (date != null) {
k = new SimpleDateFormat("yyyy-MM-dd").format(date);
} else {
k = "";
}
} else {
k = new DecimalFormat("0").format(cell
.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_STRING:
k = cell.getStringCellValue();
break;
default:
k = "";
break;
}
returnArray[i][j] = k;
}
}
}
return returnArray;
}
}
这个是我当时用的，你可以参考下

追问

好像是我的包错了，还是起冲突了。我用你的方法照样报我这个错，请问需呀哪些包？

追答

我蛋疼了，还是打包2个jar一起给你试试吧，这2个jar都要导进去的啊

Answer 2

这个好像是你导出的或者是导入的时候，版本判断有问题

追问

public String updateDoFile(File file)throws Exception
{

Workbook workbook = WorkbookFactory.create(new FileInputStream(file));
到最后一步的时候就报错了 我要导入的是Excel2007的

追答

就是应该如果是兼容的话，就是要先判断，判断之后再调用不同的方法