poi 解析excel 03与07不兼容问题

 刚开始使用new HSSFWorkbook(new FileInputStream(excelFile))来读取Workbook，对Excel2003以前（包括2003）的版本没有问题，但读取Excel2007时发生如下异常：
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)
        该错误意思是说，文件中的数据是用Office2007+XML保存的，而现在却调用OLE2 Office文档处理，应该使用POI不同的部分来处理这些数据，比如使用XSSF来代替HSSF。
        于是按提示使用XSSF代替HSSF，用new XSSFWorkbook(excelFile)来读取Workbook，对Excel2007没有问题了，可是在读取Excel2003以前（包括2003）的版本时却发生了如下新异常：
org.apache.poi.openxml4j.exceptions.InvalidOperationException: Can't open the specified file: '*.xls'
        该错误是说，操作无效，不能打开指定的xls文件。
        下载POI的源码后进行单步调试，发现刚开始的时候还是对的，但到ZipFile类后就找不到文件了，到网上查了下，原来是XSSF不能读取Excel2003以前（包括2003）的版本，这样的话，就需要在读取前判断文件是2003前的版本还是2007的版本，然后对应调用HSSF或XSSF来读取。
        这是初步的想法，但这种做法比较麻烦，看了下API，发现XSSF和HSSF虽然在不同的包里，但却引用了同一接口Workbook，于是想到了这样的读取方法：
        Workbook book = null;
        try {
            book = new XSSFWorkbook(excelFile);
        } catch (Exception ex) {
            book = new HSSFWorkbook(new FileInputStream(excelFile));
        }
        在各版本的Excel中测试，没有发生异常，问题解决。

评论

14 楼 u013810758 2014-10-10  

[flash=200,200][url][img][list]

[*]

引用
[u][i][b][b][/b][/b][/i][/u]

[/list][/img][/url][/flash]

13 楼 u013810758 2014-10-10  

我也是这么做的  但是还是有问题啊 感觉07版的或03版的内容不能户拷 

// Excel批量导入
public String importExcle() {
int error = 0;
TEmployee employee = (TEmployee) request.getSession().getAttribute(
"employee");
SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
// 声明数据流
InputStream is = null;
if (employee != null) {
if (excel != null) {
// 如果出错记住导入到的行和列
int hang = 0;
int lie = 0;
try {
// 解析excel 2007 版本文件
is = new FileInputStream(excel);
XSSFWorkbook work = new XSSFWorkbook(is);
XSSFSheet sheet = work.getSheetAt(0);
int rows = sheet.getPhysicalNumberOfRows();

if (sheet != null) {
Map<TImportantCustomerAndTNextFollowDetail, List<TCustomerFollowDetail>> map = new LinkedHashMap<TImportantCustomerAndTNextFollowDetail, List<TCustomerFollowDetail>>();
System.out.println("解析excel 2007版本文件");
// 导入啊 2007版
/*--------------------------------------------------------------------------------------------------------------------------*/
// 循环表的行，从第二行开始
long st07 = System.currentTimeMillis();
for (int i = 1; i < rows; i++) {
TImportantCustomerAndTNextFollowDetail tt = new TImportantCustomerAndTNextFollowDetail();
hang = i + 1;
XSSFRow row = sheet.getRow(i);
if (row != null) {// 判断是否为空


/*--------重要客户信息表------------------------------*/

TImportantCustomer ic = new TImportantCustomer();
if (row.getCell((short) (0)) != null) {
lie = 1;
String v = Util.getXssfCellValue((row.getCell((short) 0)));
ic.setCompanyName(v);
}
if (row.getCell((short) (1)) != null) {
lie = 2;
String v = Util.getXssfCellValue((row.getCell((short) 1)));
ic.setLinkMan(v);
}
if (row.getCell((short) (2)) != null) {
lie = 3;
String v = Util.getXssfCellValue((row.getCell((short) 2)));
ic.setTell(v);
}
if (row.getCell((short) (3)) != null) {
lie = 4;
String v = Util.getXssfCellValue((row.getCell((short) 3)));
ic.setTelephone(v);
}
if (row.getCell((short) (4)) != null) {
lie = 5;
String v = Util.getXssfCellValue((row.getCell((short) 4)));
ic.setEmail(v);
}
if (row.getCell((short) (5)) != null) {
lie = 6;
String v = Util.getXssfCellValue((row.getCell((short) 5)));
ic.setQq(v);
}
if (row.getCell((short) (6)) != null) {
lie = 7;
String v = Util.getXssfCellValue((row.getCell((short) 6)));
ic.setCompanyDetail(v);
}
//并发数
if (row.getCell((short) (7)) != null) {
lie = 8;
String v = Util.getXssfCellValue((row.getCell((short) 7)));
if (StringUtils.hasText(v)) {
ic.setConcurrent(Integer.parseInt(v));
}
}
//行业
if (row.getCell((short) (8)) != null) {
lie = 9;
String v = Util.getXssfCellValue((row.getCell((short)));
if (StringUtils.hasText(v)) {
ic.setIndustry(v);
}
}
//职位
if (row.getCell((short) (9)) != null) {
lie = 10;
String v = Util.getXssfCellValue((row.getCell((short) 9)));
if (StringUtils.hasText(v)) {
ic.setPosition(v);
}
}
//主要负责人
if (row.getCell((short) (10)) != null) {
lie = 11;
String v = Util.getXssfCellValue((row.getCell((short) 10)));
if (StringUtils.hasText(v)) {
ic.setHead(v);
}
}
//合作方式
if (row.getCell((short) (11)) != null) {
lie = 12;
String v = Util.getXssfCellValue((row.getCell((short) 11)));
if (StringUtils.hasText(v)) {
ic.setCooperation(v);
}
}
//公司规模
if (row.getCell((short) (12)) != null) {
lie = 13;
String v = Util.getXssfCellValue((row.getCell((short) 12)));
if (StringUtils.hasText(v)) {
ic.setCompanySize(v);
}
}
//客户级别
if (row.getCell((short) (13)) != null) {
lie = 14;
String v = Util.getXssfCellValue((row.getCell((short) 13)));
if (StringUtils.hasText(v)) {
ic.setCustomerLevel(Integer.parseInt(v));
}
}
//不购买原因
if (row.getCell((short) (14)) != null) {
lie = 15;
String v = Util.getXssfCellValue((row.getCell((short) 14)));
if (StringUtils.hasText(v)) {
ic.setWhyNotBuy(v);
}
}
//需求简介
if (row.getCell((short) (15)) != null) {
lie = 16;
String v = Util.getXssfCellValue((row.getCell((short) 15)));
if (StringUtils.hasText(v)) {
ic.setRequireIntroduction(v);
}
}



/*--------下次跟进表------------------------------*/
TNextFollowDetail nf = new TNextFollowDetail();
if (row.getCell((short) (16)) != null) {
lie = 17;
String v = Util.getXssfCellValue((row.getCell((short) 16)));
nf.setMainRequire(v);
}
if (row.getCell((short) (17)) != null) {
lie = 18;
String v = Util.getXssfCellValue((row.getCell((short) 17)));
nf.setNextFollowDetail(v);
}
if (row.getCell((short) (18)) != null) {
lie = 19;
String v = Util.getXssfCellValue((row.getCell((short) 18)));
if (StringUtils.hasText(v)) {
nf.setNextFollowDate(sf.parse(v));
}
}
if (row.getCell((short) (19)) != null) {
lie = 20;
String v = Util.getXssfCellValue((row.getCell((short) 19)));
nf.setNextFollowMan(v);
}

// 重要客户信息保存
ic.setCreateDate(new Date());
tt.setIc(ic);
// 下次跟进保存
tt.setNf(nf);


/*--------跟进历史记录表------------------------------*/
// 计算循环次数
int k = (row.getLastCellNum() - 19) / 3;
List<TCustomerFollowDetail> nfd = new ArrayList<TCustomerFollowDetail>();
TCustomerFollowDetail icf = null;
for (int j = 0; j < k; j++) {
icf = new TCustomerFollowDetail();
if (row.getCell((short) (3 * j + 20)) != null) {
lie = 3 * j + 21;
String v = Util.getXssfCellValue((row.getCell((short) (3 * j + 20))));
icf.setFollowMan(v);
}
if (row.getCell((short) (3 * j + 21)) != null) {
lie = 3 * j + 22;
String v = Util.getXssfCellValue((row.getCell((short) (3 * j + 21))));
if (StringUtils.hasText(v)) {
icf.setFollowDate(sf.parse(v));
}
}
if (row.getCell((short) (3 * j + 22)) != null) {
lie = 3 * j + 23;
String v = Util.getXssfCellValue((row.getCell((short) (3 * j + 22))));
icf.setFollowContent(v);
}
if (StringUtils.hasText(icf.getFollowMan())|| StringUtils.hasText(icf.getFollowContent())|| icf.getFollowDate() != null) {
// 保存历史跟进
nfd.add(icf);
}
}
map.put(tt, nfd);
}
}
try {
//保存  错误则返回错误行
error = importantCustomerBiz.addExcelInfo(map);
} catch (Exception e) {

}
long ed07 = System.currentTimeMillis();
if (error <= 0) {
request.setAttribute("message",
"excel 2007 导入成功(=^_^=),累计耗时"+ (ed07 - st07) + "毫秒");
} else {
request.setAttribute("message", "从   <<" + error+ "行>>  之后导入失败,累计耗时" + (ed07 - st07) + "毫秒");
}
}

} catch (Exception e) {
/*--------------------------------------------------------------------------------------------------------------------------*/
// 导入啊 2003版
/*--------------------------------------------------------------------------------------------------------------------------*/
try {
// 解析excel 2003 版本文件
is = new FileInputStream(excel);
HSSFWorkbook work = new HSSFWorkbook(is);
HSSFSheet sheet = work.getSheetAt(0);
if (sheet != null) {

Map<TImportantCustomerAndTNextFollowDetail, List<TCustomerFollowDetail>> map = new LinkedHashMap<TImportantCustomerAndTNextFollowDetail, List<TCustomerFollowDetail>>();
System.out.println("解析excel 2003 版本文件");
int rows = sheet.getPhysicalNumberOfRows();
// 循环表的行，从第二行开始
long st03 = System.currentTimeMillis();
for (int i = 1; i < rows; i++) {
TImportantCustomerAndTNextFollowDetail tt = new TImportantCustomerAndTNextFollowDetail();
hang = i + 1;
HSSFRow row = sheet.getRow(i);
if (row != null) {// 判断是否为空


/*--------重要客户信息表------------------------------*/

TImportantCustomer ic = new TImportantCustomer();
if (row.getCell((short) (0)) != null) {
lie = 1;
String v = Util.getHssfCellValue((row.getCell((short) 0)));
ic.setCompanyName(v);
}
if (row.getCell((short) (1)) != null) {
lie = 2;
String v = Util.getHssfCellValue((row.getCell((short) 1)));
ic.setLinkMan(v);
}
if (row.getCell((short) (2)) != null) {
lie = 3;
String v = Util.getHssfCellValue((row.getCell((short) 2)));
ic.setTell(v);
}
if (row.getCell((short) (3)) != null) {
lie = 4;
String v = Util.getHssfCellValue((row.getCell((short) 3)));
ic.setTelephone(v);
}
if (row.getCell((short) (4)) != null) {
lie = 5;
String v = Util.getHssfCellValue((row.getCell((short) 4)));
ic.setEmail(v);
}
if (row.getCell((short) (5)) != null) {
lie = 6;
String v = Util.getHssfCellValue((row.getCell((short) 5)));
ic.setQq(v);
}
if (row.getCell((short) (6)) != null) {
lie = 7;
String v = Util.getHssfCellValue((row.getCell((short) 6)));
ic.setCompanyDetail(v);
}
//并发数
if (row.getCell((short) (7)) != null) {
lie = 8;
String v = Util.getHssfCellValue((row.getCell((short) 7)));
if (StringUtils.hasText(v)) {
ic.setConcurrent(Integer.parseInt(v));
}
}
//行业
if (row.getCell((short) (8)) != null) {
lie = 9;
String v = Util.getHssfCellValue((row.getCell((short)));
if (StringUtils.hasText(v)) {
ic.setIndustry(v);
}
}
//职位
if (row.getCell((short) (9)) != null) {
lie = 10;
String v = Util.getHssfCellValue((row.getCell((short) 9)));
if (StringUtils.hasText(v)) {
ic.setPosition(v);
}
}
//主要负责人
if (row.getCell((short) (10)) != null) {
lie = 11;
String v = Util.getHssfCellValue((row.getCell((short) 10)));
if (StringUtils.hasText(v)) {
ic.setHead(v);
}
}
//合作方式
if (row.getCell((short) (11)) != null) {
lie = 12;
String v = Util.getHssfCellValue((row.getCell((short) 11)));
if (StringUtils.hasText(v)) {
ic.setCooperation(v);
}
}
//公司规模
if (row.getCell((short) (12)) != null) {
lie = 13;
String v = Util.getHssfCellValue((row.getCell((short) 12)));
if (StringUtils.hasText(v)) {
ic.setCompanySize(v);
}
}
//客户级别
if (row.getCell((short) (13)) != null) {
lie = 14;
String v = Util.getHssfCellValue((row.getCell((short) 13)));
if (StringUtils.hasText(v)) {
ic.setCustomerLevel(Integer.parseInt(v));
}
}
//不购买原因
if (row.getCell((short) (14)) != null) {
lie = 15;
String v = Util.getHssfCellValue((row.getCell((short) 14)));
if (StringUtils.hasText(v)) {
ic.setWhyNotBuy(v);
}
}
//需求简介
if (row.getCell((short) (15)) != null) {
lie = 16;
String v = Util.getHssfCellValue((row.getCell((short) 15)));
if (StringUtils.hasText(v)) {
ic.setRequireIntroduction(v);
}
}



/*--------下次跟进表------------------------------*/
TNextFollowDetail nf = new TNextFollowDetail();
if (row.getCell((short) (16)) != null) {
lie = 17;
String v = Util.getHssfCellValue((row.getCell((short) 16)));
nf.setMainRequire(v);
}
if (row.getCell((short) (17)) != null) {
lie = 18;
String v = Util.getHssfCellValue((row.getCell((short) 17)));
nf.setNextFollowDetail(v);
}
if (row.getCell((short) (18)) != null) {
lie = 19;
String v = Util.getHssfCellValue((row.getCell((short) 18)));
if (StringUtils.hasText(v)) {
nf.setNextFollowDate(sf.parse(v));
}
}
if (row.getCell((short) (19)) != null) {
lie = 20;
String v = Util.getHssfCellValue((row.getCell((short) 19)));
nf.setNextFollowMan(v);
}

// 重要客户信息保存
ic.setCreateDate(new Date());
tt.setIc(ic);
// 下次跟进保存
tt.setNf(nf);


/*--------跟进历史记录表------------------------------*/
// 计算循环次数
int k = (row.getLastCellNum() - 19) / 3;
List<TCustomerFollowDetail> nfd = new ArrayList<TCustomerFollowDetail>();
TCustomerFollowDetail icf = null;
for (int j = 0; j < k; j++) {
icf = new TCustomerFollowDetail();
if (row.getCell((short) (3 * j + 20)) != null) {
lie = 3 * j + 21;
String v = Util.getHssfCellValue((row.getCell((short) (3 * j + 20))));
icf.setFollowMan(v);
}
if (row.getCell((short) (3 * j + 21)) != null) {
lie = 3 * j + 22;
String v = Util.getHssfCellValue((row.getCell((short) (3 * j + 21))));
if (StringUtils.hasText(v)) {
icf.setFollowDate(sf.parse(v));
}
}
if (row.getCell((short) (3 * j + 22)) != null) {
lie = 3 * j + 23;
String v = Util.getHssfCellValue((row.getCell((short) (3 * j + 22))));
icf.setFollowContent(v);
}
if (StringUtils.hasText(icf.getFollowMan())|| StringUtils.hasText(icf.getFollowContent())|| icf.getFollowDate() != null) {
// 保存历史跟进
nfd.add(icf);
}
}
map.put(tt, nfd);
}
}
try {
error = importantCustomerBiz.addExcelInfo(map);
} catch (Exception e2) {
e2.printStackTrace();
}
long ed03 = System.currentTimeMillis();
if (error <= 0) {
request.setAttribute("message","excel 2007 导入成功(=^_^=),累计耗时"+ (ed03 - st03) + "毫秒");
} else {
request.setAttribute("message", "从   <<"+ error + "行>>  之后导入失败,累计耗时"+ (ed03 - st03) + "毫秒");
}
}
} catch (Exception e1) {
request.setAttribute("message", "从第" + hang + "行,第"+ lie + "列读取有误！，请检查");
e1.printStackTrace();
}
}
}
} else {
request.setAttribute("message", "登录超时！");
}
// 查询时对象中含有值对查询有误
if (icustomer != null) {
icustomer = null;
}
return icustomerList();
}

12 楼 sezi915 2014-03-12  

我觉得如果用异常来判断Excel版本的话 用org.apache.poi.poifs.filesystem.OfficeXmlFileException这个异常比较好
我在new HSSFWorkbook时new2007的会报这个错 直接用Exception有点太笼统了吧 因为我还知道一个org.apache.poi.hssf.OldExcelFormatException的错 这是弄Ecxel5.0/95出的错 这个用XSSF也处理不了

11 楼 他大姨妈 2013-10-29  

思路很好，楼主脑子比较好使

10 楼 zhenlong_qu 2013-10-18  

谢谢博主的这篇文章，不得不说，博主的思路真不错，按照这个思路，我解决了兼容excel版本的问题。。。十分感谢

9 楼 qiujiangbing 2013-09-03  

求全部相关代码？邮箱[e-mail]

8 楼 月亮不懂夜的黑 2013-08-13  

//创建相关的文件流对象
in = new FileInputStream(excelFile);
//声明相关的工作薄对象
Workbook wb =null;
//声明相关的excel抽取对象
ExcelExtractor extractor=null;
String fileName = excelFile.getName();
String hz = fileName.substring(fileName.lastIndexOf("."),fileName.length());
		    
if(hz.equals(".xls"))//针对2003版本
{
    //创建excel2003的文件文本抽取对象
    wb=new HSSFWorkbook(new POIFSFileSystem(in));
    extractor =new org.apache.poi.hssf.extractor.ExcelExtractor((HSSFWorkbook)wb);
}else{ //针对2007版本
     wb = new  XSSFWorkbook(in);
     //创建excel2007的文件文本抽取对象
     extractor =new XSSFExcelExtractor((XSSFWorkbook)wb);
}

这样应该更合适一点

7 楼 hzywy 2013-06-26  

您好，我照着你的方法改了 但还是不行，请问你能发一份demo给我吗？？？ [e-mail]  万分感激了

6 楼 zhang_shuai870717 2013-05-22  

求指教！289270219

5 楼 lishumingwm163.com 2013-03-14  

qq672076266 写道

兄弟，不得不佩服你解决问题的能力

呵呵，谢谢夸奖

4 楼 qq672076266 2013-03-11  

兄弟，不得不佩服你解决问题的能力

3 楼 shenwei83 2012-06-10  

java.lang.ClassCastException:org.apache.poi.hssf.usermodel.HSSFWorkbook cannot be cast to org.apache.poi.ss.usermodel.Workbook

if (excelFileFileName.toLowerCase().endsWith("xls")) {
    return   new HSSFWorkbook(is); //这行报错！ }
if (excelFileFileName.toLowerCase().endsWith("xlsx")) {
return new XSSFWorkbook(is);
}
return null;

2 楼 huajuande 2012-03-29  

谢谢~~~

1 楼 rcy2008 2011-11-30  

thanks a lot!