解析2007及以上使用XSSFWorkbook,
如果解析类与excel版本不对应,抛出相应的异常,例如HSSFWorkbook解析2007:
org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML.
You are calling the part of POI that deals with OLE2 Office Documents.
You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
XSSF和HSSF虽然在不同的包里,但却都实现了同一接口Workbook,可以先判断excel版本,然后由对应的excel解析类解析,指向同一Workbook变量
程序如下:
/**
* 需要导入的jar包
*
* poi-3.8-beta3-20110606.jar
*
* poi-excelant-3.8-beta3-20110606.jar
*
* poi-examples-3.8-beta3-20110606.jar
*
* poi-ooxml-schemas-3.8-beta3-20110606.jar
*
* poi-ooxml-3.8-beta3-20110606.jar
*
* poi-scratchpad-3.8-beta3-20110606.jar
*
* xmlbeans-2.3.0.jar
*
* dom4j-1.6.1.jar
*
* 所有jar包在poi-bin-3.8-beta3-20110606.zip中,或poi-bin-3.8-20120326.zip
*
*
* 使用3.7版本
*
* poi-3.7-20101029.jar
*
* poi-examples-3.7-20101029.jar
*
* poi-ooxml-3.7-20101029.jar
*
* poi-ooxml-schemas-3.7-20101029.jar
*
* poi-scratchpad-3.7-20101029.jar
*
* geronimo-stax-api_1.0_spec-1.0.jar
*
* xmlbeans-2.3.0.jar
*
* dom4j-1.6.1.jar
* commons-logging-1.1.jar
*
* 所有jar包在poi-bin-3.7-20101029.zip中,
*
* 下载地址:http://download.csdn.net/detail/javaloveiphone/5821279 或 http://download.csdn.net/detail/javaloveiphone/5821291
* http://archive.apache.org/dist/poi/release/bin/ 或 http://www.java2s.com/Code/Jar/p/Downloadpoiexcelant38beta320110606jar.htm
*/
程序一:判断Excel版本,选择对应的excel解析类
package com.read.excel;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
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 ReadExcel {
/** 错误信息 */
private String errorInfo;
/**
* 验证EXCEL文件是否合法
*/
public boolean validateExcel(String filePath){
/**判断文件名是否为空或者文件是否存在 */
if(!CEVUtil.fileExist(filePath)){
errorInfo = "文件不存在";
return false;
}
/**检查文件是否是Excel格式的文件 */
if (!CEVUtil.isExcel(filePath)) {
errorInfo = "文件名不是excel格式";
return false;
}
return true;
}
/**
* @描述:根据文件名读取excel文件
*/
public List<List<String>> read(String filePath){
List<List<String>> dataLst = new ArrayList<List<String>>();
InputStream is = null;
try{
/** 验证文件是否合法 */
if (!validateExcel(filePath)){
System.out.println(errorInfo);
return null;
}
/** 判断文件的类型,是2003还是2007 */
boolean isExcel2003 = true;
if (CEVUtil.isExcel2007(filePath)){
isExcel2003 = false;
}
/** 调用本类提供的根据流读取的方法 */
is = new FileInputStream(new File(filePath));
Workbook wb = null;
if (isExcel2003){
wb = new HSSFWorkbook(is);
}else{
wb = new XSSFWorkbook(is);
}
is.close();
}catch (IOException e){
e.printStackTrace();
}catch (Exception ex){
ex.printStackTrace();
}finally{
if (is != null){
try{
is.close();
}catch (IOException e){
is = null;
e.printStackTrace();
}
}
}
return dataLst;
}
/**
* @描述:读取数据
*/
private List<List<String>> read(Workbook wb){
List<List<String>> dataLst = new ArrayList<List<String>>();
/**得到总的shell */
int sheetAccount = wb.getNumberOfSheets();
/** 得到第一个shell */<