做一个通过jsp上传Excel文档,后台进行解析的功能,项目是struts2,一开始,我是这样写的:
jsp:
<div id="dlg2" class="easyui-dialog" style="width:400px;height:180px;padding:10px 20px"
closed="true" buttons="#dlg-buttons2">
<form id="uploadForm" method="post" enctype="multipart/form-data">
<table style="width:320px;height:80px;">
<tr>
<td>下载模版:</td>
<td><a href="javascript:void(0)" class="easyui-linkbutton" onclick="downloadTemplate()">下载模板文件</a></td>
</tr>
<tr>
<td>上传文件:</td>
<td><input type="file" name="userUploadFile" id="selectedExcel"></td>
</tr>
</table>
</form>
</div>
<div id="dlg-buttons2">
<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-ok" onclick="uploadFile()">导入</a>
<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-cancel" onclick="javascript:$('#dlg2').dialog('close')">关闭</a>
</div>
js:
function uploadFile(){
debugger;
var fileName = $('#selectedExcel').val();
if(fileName == ""){
$.messager.alert('提示','请选择上传文件!','info');
return;
}
fileext = fileName.substring(fileName.lastIndexOf(".")+1,fileName.length);
fileext = fileext.toLowerCase();
if (fileext == 'xls' || fileext == 'xlsx'){
$.messager.progress({title:'提示',msg:'正在导入,请稍候...'});
$("#uploadForm").form('submit',{
type : "post",
url : "<%=basePath%>" + "ImportAndExport/DataImport.action?selType=" + selType + "&fileext=" + fileext,
data : "",
dataType : "json",
success : function(result){
$.messager.progress("close");
var data = result.split("\"");
$.messager.alert('提示',data[1],'info');
},
});
}
else{
$.messager.alert('提示','请选择xls/xlsx格式文件!','info');
}
}
后台action接收方法:
public String upload() throws Exception{
try
{
HttpServletRequest request = ServletActionContext.getRequest();
String selType = request.getParameter("selType");
String fileext = request.getParameter("fileext");
ImportServiceImpl isi = new ImportServiceImpl();
resultTip = isi.importExcelByType(userUploadFile, selType, fileext);
} catch (Exception e)
{
resultTip = "导入失败!请参考:" + e.getMessage();
e.printStackTrace();
}
return "importResult";
}
service进行解析:
public String importExcelByType(File f,String type,String ext){
Workbook workbook = null;
int recordNum = 0;//记录插入记录数
try
{
if(XLS.equals(ext)){
workbook = new HSSFWorkbook(new FileInputStream(f));
}
else if(XLSX.equals(ext)){
workbook = new XSSFWorkbook(new FileInputStream(f));
}
Sheet sheet = workbook.getSheetAt(0);
直接用new XSSFWorkbook(new FileInputStream(f))或new HSSFWorkbook方法,获得Workbook对象。
但是,我要解析的Excel文件数据量比较大,一般都是10万条左右,获得Workbook对象的这一步直接内存溢出,拆到一万条一个文件的时候可以跑,但是十分钟很慢,于是上网找了找其他的解析Excel方法,找到了一个号称占用小速度快的,Eclipse跑了有效:
我把这个代码拿过来用,加了个返回值List的readExcel方法,内容就是:
public static List readExcel(String f,int columnNum) throws Exception{
// List list=XLSXCovertCSVReader.readerExcel("d:\a.xlsx", "sheet1", 45);
List list = XLSXCovertCSVReader
.readerExcel(
// "C:\Users\hbkn\Desktop\dnbb亳州导入数据\dnbb亳州\中压基础数据查询结果\中压测试.xlsx",
"d:\a.xlsx",
"Sheet1", 45);
return list;
}
然后我高高兴兴拿过来用,修改action方法如下:
public String upload() throws Exception{
InputStream is=null;
OutputStream os=null;
String temp="d:\a.xlsx";
try {
is = new BufferedInputStream(new FileInputStream(userUploadFile));
os = new BufferedOutputStream(new FileOutputStream(temp));
byte[] buffer = new byte[1024];
int len=0;
while((len=is.read(buffer))>0){
os.write(buffer,0,len);
}
HttpServletRequest request = ServletActionContext.getRequest();
String selType = request.getParameter("selType");
String fileext = request.getParameter("fileext");
ImportServiceImpl isi = new ImportServiceImpl();
resultTip = isi.importExcelByType1(temp, selType, fileext);
}catch(Exception e){
resultTip= "导入失败!请参考:" + e.getMessage();
e.printStackTrace();
}finally{
if(is !=null){is.close();}
if(os!=null){os.close();}
}
return "importResult";
}
修改service方法如下:
public String importExcelByType1(String f,String type,String ext){
try {
String[] colArr = getColumnName(type).split(",");
HashMap colMap = new HashMap();
for(int i = 0;i < colArr.length;i++){
colMap.put(colArr[i].split(":")[0], colArr[i].split(":")[1]);
}
List<String[]> readExcel = XLSXCovertCSVReader.readExcel(f, colArr.length);
System.out.println(readExcel.size());
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return ext;
}
发现报:
org.apache.poi.openxml4j.exceptions.InvalidOperationException: Can't open the specified file: 'd:\a.xlsx'
at org.apache.poi.openxml4j.opc.ZipPackage.(ZipPackage.java:106)
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:221)
at com.ssh.effectiveCheck.util.XLSXCovertCSVReader.readerExcel(XLSXCovertCSVReader.java:451)
at com.ssh.effectiveCheck.util.XLSXCovertCSVReader.readExcel(XLSXCovertCSVReader.java:480)
at com.ssh.effectiveCheck.service.impl.ImportServiceImpl.importExcelByType1(ImportServiceImpl.java:45)
at com.ssh.effectiveCheck.action.ImportAction.upload(ImportAction.java:72)
。。。。。
Caused by: java.util.zip.ZipException: error in opening zip file
at java.util.zip.ZipFile.open(Native Method)
at java.util.zip.ZipFile.(ZipFile.java:219)
at java.util.zip.ZipFile.(ZipFile.java:149)
at java.util.zip.ZipFile.(ZipFile.java:163)
at org.apache.poi.openxml4j.opc.internal.ZipHelper.openZipFile(ZipHelper.java:174)
at org.apache.poi.openxml4j.opc.ZipPackage.(ZipPackage.java:104)
... 75 more
但是我手动修改了的解析类里的需要解析的Excel文件路径,不再让它解析我上传后的Excel,而是解析本地的原本的Excel(tomcat服务安装在本地),不报这个错了,list也成功取到了。
也就是说网上找到的这个解析方法不认我上传之后的Excel文件(明明WPS一样可以打开)。
在这个过程中,我又动了歪脑筋,这个服务是单机版,以后部署也是单机,那我能不能不上传,直接让服务解析本地的Excel呢?
所以,各位高手,我想得到解决方案(任一即可),
1。通过jsp获取一个文件的本机绝对路径(我用var fileName = $('#selectedExcel').val();试了,debug显示是fileName = "C:\fakepath\中压测试1.xlsx",这个并不是真实路径)。
2.求正确的上传Excel文件的方法,能让上面链接里代码可以成功解析的那种。
3.Workbook解析Excel大文件的正确姿势,能不能做到分段,占用内存小,速度快的那种。