Java对Excel表格的上传和下载处理方法

excel表格文件的上传和下载，java中涉及到文件肯定会有io流的知识。

而excel文件就要涉及到poi技术，而excel的版本包括：2003-2007和2010两个版本，即excel的后缀名为：xls和xlsx。

这里我是按照正规的项目流程做的案例，所以可能会比网上的一些demo复杂一些。不过文件的上传和下载基本都是一套固定的流程，只是每个人的实现方式不太相同。

数据库我用的是mysql。

下面是我的项目目录：

按照正常的项目做了分层处理，文件上传的业务我放到了service处理，而文件下载业务还在controller层。

对前端请求处理，我分成了两个方法都放在handleexcelcontroller里面，这个类继承了baseexcelcontroller,基本的文件操作处理在baseexcelcontroller里面。

baseexcelcontroller继承了basecontroller，basecontroller类是所有controller的父类，这里用到的不太多，这个类封装了response返回值等的处理等一些方法。

项目中除了springmvc和mybatis的jar包之外还引入了：

上传和下载excel文件：

1、创建需要上传的excel文件，为了简化，我这里只写了四列，即四个字段

2、创建jsp页面

<%@pagelanguage="java"import="java.util.*"pageencoding="utf-8"%>

<%@taglibprefix="c"uri="http://java.sun.com/jsp/jstl/core"%>

<!doctypehtmlpublic"-//w3c//dtdhtml4.01transitional//en">

<html>

<head>

<title>excel文件处理</title>

<scripttype="text/javascript"src="<c:urlvalue='/res/js/jquery.js'/>"></script>

<script>

$(function(){

var$wrap=$(".wrap");

varfind=function(str){

return$wrap.find(str);

}

vargetjname=function(name){

returnfind("input[name='"+name+"']");

}

getjname("upload").click(function(){

varform=newformdata(document.getelementbyid("tf"));

$.ajax({

url:"<c:urlvalue='/file/uploadexcel'/>",

type:"post",

data:form,

datatype:"json",

processdata:false,

contenttype:false,

success:function(data){

//window.clearinterval(timer);

if(data.success=="success"){

alert("提交文件成功,已将数据存入数据库");

}

},

error:function(e){

alert("错误！");

//window.clearinterval(timer);

}

});

})

getjname("download").click(function(){

$.post("<c:urlvalue='/file/downloadexcel'/>",{"id":"3"},function(data){

//alert("下载文件成功");

},"json")

})

})

</script>

</head>

<body>

<divclass="wrap">

<formid="tf">

<p>

<inputtype="file"name="file"value="选择文件"/>

excel文件上传：<inputtype="button"name="upload"value="upload"/>

</p>

<p>

excel文件下载：<inputtype="button"name="download"value="updown"/>

</p>

</form>

</div>

</body>

</html>

3、依次创建controller、service、domain、mapper层，注意它们的依赖关系

1）、controller层的处理，在handleexcelcontroller里面注入baseexcelservice。因为只是做个示范，所欲我这里将泛型固定为students类

baseexcelcontroller代码：

packagecn.wangze.controller;

importjava.io.file;

importjava.io.fileinputstream;

importjava.io.filenotfoundexception;

importjava.io.fileoutputstream;

importjava.io.ioexception;

importjava.io.inputstream;

importjava.lang.reflect.method;

importjava.text.simpledateformat;

importjava.util.collection;

importjava.util.date;

importjava.util.iterator;

importjava.util.list;

importjavax.servlet.http.httpservletresponse;

importorg.apache.commons.lang.stringutils;

importorg.apache.poi.hssf.usermodel.hssfcell;

importorg.apache.poi.hssf.usermodel.hssfcellstyle;

importorg.apache.poi.hssf.usermodel.hssffont;

importorg.apache.poi.hssf.usermodel.hssfpalette;

importorg.apache.poi.hssf.usermodel.hssfrichtextstring;

importorg.apache.poi.hssf.usermodel.hssfrow;

importorg.apache.poi.hssf.usermodel.hssfsheet;

importorg.apache.poi.hssf.usermodel.hssfworkbook;

importorg.apache.poi.hssf.util.cellrangeaddress;

importorg.apache.poi.hssf.util.hssfcolor;

importorg.apache.poi.ss.usermodel.sheet;

importorg.apache.poi.xssf.usermodel.xssfworkbook;

importorg.springframework.web.multipart.multipartfile;

importcn.wangze.domain.students;

publicclassbaseexcelcontrollerextendsbasecontroller{

//获取文件的路径

stringseparator=system.getproperty("file.separator");

//验证元素是否为空

@suppresswarnings("all")

publicbooleanisempty(objectobj){

if(objinstanceofobject[]){

if(((object[])obj).length==0){

returntrue;

}

if(obj==null)returntrue;

if((string.valueof(obj).trim()).length()==0){

returntrue;

}

if(objinstanceoflist){

if(((list)obj)==null||((list)obj).size()==0){

returntrue;

}

}

}

returnfalse;

}

/**

*文件上传部分

**/

//验证文件

protectedbooleancheckpathname(stringfilename,httpservletresponseresponse){

//验证文件是否存在

if(isempty(filename)){

senderror("上传文件不存在",response);

returnfalse;

}

//验证文件是否是以xls或者xlsx做后缀的文件，如果不是就返回错误信息

if(!(stringutils.endswithignorecase(filename,".xls")||stringutils.endswithignorecase(filename,".xlsx"))){

senderror("上传文件类型错误，请核对后重新上传?",response);

}

returntrue;

}

//获取文件的sheet

protectedsheetgetsheet(multipartfilefile,stringpath,stringfilename)throwsillegalstateexception,ioexception{

//找到要存放到项目里面的路径，新建文件

filetargetfile=newfile(path,filename);

targetfile.mkdirs();

if(targetfile.exists()){

targetfile.delete();

file.transferto(targetfile);

}else{

file.transferto(targetfile);

}

//封装输入流,封装sheet里面的内容

inputstreamis=null;

try{

is=newfileinputstream(path+separator+filename);

//判断版本是否为excel加强版

if(stringutils.endswithignorecase(filename,".xls")){

returnnewhssfworkbook(is).getsheetat(0);

}elseif(stringutils.endswithignorecase(filename,".xlsx")){

returnnewxssfworkbook(is).getsheetat(0);

}

returnnull;

}

finally{

if(is!=null){

is.close();

}

}

}

/**

*文件下载部分

**/

//根据传入的sting值，判断生成在excel表的位置

privatehssfcellstylegetpublicstyle(hssfworkbookworkbook,stringkey){

hssffontfont=workbook.createfont();

hssfcellstylestyle=workbook.createcellstyle();

hssfpalettecustompalette=workbook.getcustompalette();

custompalette.setcoloratindex(hssfcolor.teal.index,(byte)64,(byte)148,(byte)160);

custompalette.setcoloratindex(hssfcolor.orange.index,(byte)170,(byte)204,(byte)204);

style.setalignment(hssfcellstyle.align_center);

style.setverticalalignment(hssfcellstyle.vertical_center);

if(key=="head"){

style.setfillpattern(hssfcellstyle.solid_foreground);

font.setfontheightinpoints((short)12);

font.setcolor(hssfcolor.teal.index);

font.setboldweight(hssffont.boldweight_bold);

style.setfont(font);

}

if(key=="title"){

font.setcolor(hssfcolor.white.index);

font.setboldweight(hssffont.boldweight_bold);

style.setborderleft(hssfcellstyle.border_thin);

style.setleftbordercolor(hssfcolor.white.index);

style.setborderright(hssfcellstyle.border_thin);

style.setrightbordercolor(hssfcolor.white.index);

style.setfont(font);

style.setfillpattern(hssfcellstyle.solid_foreground);

style.setfillforegroundcolor(hssfcolor.orange.index);

style.setfillbackgroundcolor(hssfcolor.orange.index);

}

returnstyle;

}

//创建head头信息

privatevoidcreatehead(hssfsheetsheet,hssfcellstylestyle,string[]title){

hssfrowrow1=sheet.createrow(0);

hssfcellcelltitle=row1.createcell(0);

celltitle.setcellvalue(newhssfrichtextstring(title[0]));

sheet.addmergedregion(newcellrangeaddress(0,0,0,title.length-2));

celltitle.setcellstyle(style);

}

//创建title信息

privatevoidcreatetitle(hssfsheetsheet,hssfcellstylestyle,string[]label,intcolumnnum){

hssfrowrow2=sheet.createrow(1);

hssfcellcell1=null;

for(intn=0;n<columnnum;n++){

cell1=row2.createcell(n);

cell1.setcellvalue(label[n+1]);

cell1.setcellstyle(style);

}

}

//创建content数据信息

privatevoidcreatecontent(hssfsheetsheet,hssfcellstylestyle,collection<students>list,intcolumnnum,string[]parameters){

intindex=0;

iterator<students>it=list.iterator();

while(it.hasnext()){

index++;

studentscash=it.next();

intnum2=parameters.length;

hssfrowrow=sheet.createrow(index+1);

initcells(style,num2,cash,parameters,row);

}

}

//验证是否为中文

publicbooleancheckchinese(strings){

intn=0;

booleanflag=false;

for(inti=0;i<s.length();i++){

n=(int)s.charat(i);

flag=(19968<=n&&n<40623)?true:false;

}

returnflag;

}

//将数据设置到excel表格内

publicvoidinitcells(hssfcellstylestyle,intcolumnnum,studentst,

string[]endcontent,hssfrowrow3){

for(intj=0;j<columnnum;j++){

hssfcellcell=row3.createcell(j);

stringfieldname=endcontent[j];

try{

if(fieldname!=""&&!checkchinese(fieldname)){

stringgetmethodname="get"+fieldname.substring(0,1).touppercase()+fieldname.substring(1);

classclazz=t.getclass();

methodgetmethod=clazz.getmethod(getmethodname,newclass[]{});

stringvalue=(string)getmethod.invoke(t,newobject[]{});

cell.setcellvalue(value);

}else{

cell.setcellvalue(fieldname);

}

cell.setcellstyle(style);

}catch(exceptione){

e.printstacktrace();

}

}

}

publicvoidcreateend(hssfsheetsheet,hssfcellstylestyle,intnumtext,intcolumnnum,studentst,string[]endcontent){

hssfrowrow3=sheet.createrow(numtext+2);

initcells(style,columnnum,t,endcontent,row3);

}

//根据service查询到的数据，创建excel表并插入查询的数据信息

protectedstringgetoutputname(list<students>list,stringpath,string[]title,string[]parameters,studentst,string[]endcontent)

throwsioexception{

//根据传入的title数组的第一个值，设置文件名称

stringfilename=title[0]+"_"+newsimpledateformat("yyyymmdd").format(newdate())+".xls";

//输出流放到文件的本地位置

fileoutputstreamfos=newfileoutputstream(path+separator+filename);

//列数，根据title的个数，除去第一个就是每列title的信息

intcolumnnum=title.length-1;

intnumtext=list.size();

hssfworkbookworkbook=newhssfworkbook();

hssfsheetsheet=workbook.createsheet();

sheet.setdefaultcolumnwidth(20);

sheet.setdefaultrowheight((short)400);

hssfcellstylecontentstyle=this.getpublicstyle(workbook,"");

hssfcellstyletitlestyle=this.getpublicstyle(workbook,"title");

hssfcellstyleheaderstyle=this.getpublicstyle(workbook,"head");

createhead(sheet,headerstyle,title);

createtitle(sheet,titlestyle,title,columnnum);

createcontent(sheet,contentstyle,list,columnnum,parameters);

//createend(sheet,contentstyle,numtext,columnnum,t,endcontent);

workbook.write(fos);

fos.flush();

fos.close();

returnfilename;

}

}

handleexcelcontroller用来处理前端请求，代码如下：

packagecn.wangze.controller;

importjava.io.file;

importjava.util.list;

importjavax.servlet.servletoutputstream;

importjavax.servlet.http.httpservletresponse;

importjavax.servlet.http.httpsession;

importorg.apache.commons.io.fileutils;

importorg.springframework.beans.factory.annotation.autowired;

importorg.springframework.stereotype.controller;

importorg.springframework.web.bind.annotation.requestmapping;

importorg.springframework.web.multipart.multipartfile;

importcn.wangze.domain.students;

importcn.wangze.service.baseexcelservice;

@controller

@requestmapping("/file")

publicclasshandleexcelcontrollerextendsbaseexcelcontroller{

@autowired

privatebaseexcelservicebaseexcelservice;

@requestmapping("/uploadexcel")

publicvoiduploadexcel(multipartfilefile,httpsessionsession,httpservletresponseresponse)throwsexception{

//如果上传的文件不存在，抛出异常

if(file==null){

thrownewexception("文件不存在");

}

//获取文件名

stringfilename=file.getoriginalfilename();

//选择上传的文件存放到项目的路径

stringpath=session.getservletcontext().getrealpath(separator+"res"+separator+"upload");

if(!checkpathname(filename,response))return;

stringmsg=baseexcelservice.loadexcel(getsheet(file,path,filename));

sendmsg(true,msg,response);

}

@requestmapping("/downloadexcel")

publicvoidupdownexcel(studentsstudent,httpservletresponseres,httpsessionsession,httpservletresponseresponse)

throwsexception{

list<students>stus=baseexcelservice.querylist(student);

if(stus.size()==0){

res.sendredirect("/index.jsp");

return;

}

//下载的excel文件存放的本地路径

stringpath=session.getservletcontext().getrealpath(separator+"res"+separator+"exportexcel"+separator);

servletoutputstreamos=res.getoutputstream();

studentst=baseexcelservice.querytotal(student);

//标题文字，数值中的第一个值+当前日期为文件名称，以后的每个元素为每列的标题

string[]title={"studets04","id","名字","年龄","性别"};//标题文字

//对应实体类的属性值

string[]parameters={"id","name","age","sex"};

string[]endcontent={"","","",""};

//调用父类的处理方法，生成excel文件

stringfilename=getoutputname(stus,path,title,parameters,t,endcontent);

try{

res.reset();

res.setcharacterencoding("utf8");

res.setcontenttype("application/vnd.ms-excel;charset=utf8");

res.setheader("content-disposition","attachment;filename="

+newstring(filename.getbytes("utf-8"),"iso-8859-1"));

os.write(fileutils.readfiletobytearray(newfile(path+separator+filename)));

sendresult(true,response);

os.flush();

}finally{

if(os!=null){

os.close();

}

}

}

}

2）、service层的处理，把studentsmapper注入到baseexcelservice

baseexcelservice代码：

packagecn.wangze.service;

importjava.util.arraylist;

importjava.util.hashmap;

importjava.util.list;

importjava.util.map;

importjavax.servlet.servletoutputstream;

importjavax.servlet.http.httpsession;

importorg.apache.poi.ss.usermodel.cell;

importorg.apache.poi.ss.usermodel.row;

importorg.apache.poi.ss.usermodel.sheet;

importorg.springframework.beans.factory.annotation.autowired;

importorg.springframework.stereotype.service;

importcn.wangze.domain.students;

importcn.wangze.mapper.studentsmapper;

@service

publicclassbaseexcelservice{

@autowired

privatestudentsmapper<students>studentsmapper;

//判断字符串是否为空

publicbooleanisempty(stringstr){

returnstr==null||str.length()==0;

}

//获取单个表格(字段)存放的信息

privatestringgetvalue(cellcell,stringcelllable,map<string,string>errmap){

cell.setcelltype(cell.cell_type_string);

stringvalue=cell.getstringcellvalue().trim();

returnvalue;

}

//通过这个方法将excel表的每行的数据放到info对象里面

privatestringaddinfo(rowrow,studentsinfo){

map<string,string>errmap=newhashmap<string,string>();

stringid=getvalue(row.getcell(0),"id",errmap);

stringusername=getvalue(row.getcell(1),"姓名",errmap);

stringage=getvalue(row.getcell(2),"年龄",errmap);

stringsex=getvalue(row.getcell(3),"性别",errmap);

stringerrmsg=errmap.get("errmsg");

if(!isempty(errmsg)){

returnerrmsg;

}

info.setid(id);

info.setname(username);

info.setage(age);

info.setsex(sex);

returnnull;

}

publicstringloadexcel(sheetsheet)throwsexception{

//新建一个list集合，用来存放所有行信息，即每行为单条实体信息

list<students>infos=newarraylist<students>();

//获取到数据行数，第一行是title，不需要存入数据库，所以rownum从1开始

for(intrownum=1;rownum<=sheet.getlastrownum();rownum++){

studentsinfo=newstudents();

stringerrmsg2=addinfo(sheet.getrow(rownum),info);

if(errmsg2!=null)returnerrmsg2;

infos.add(info);

}

if(infos.isempty()){

return"没有解析到学生数据，请查验excel文件";

}

//通过studentsmapper的insertsheetdata方法，将实体类存放的数据插入到数据库

intresult=studentsmapper.insertsheetdata(infos);

//若插入成功会返回大于1的整数，返回success

if(result>=1){

return"success";

}

return"error";

}

//查询所有数据库存放的学生信息

publiclist<students>querylist(studentsstudents){

returnstudentsmapper.querylist(students);

}

//获取到的学生实体信息

publicstudentsquerytotal(studentsstudents){

returnstudentsmapper.querytotal(students);

}

publicvoiddownexcel(httpsessionsession,stringseparator){

}

}

3）、实体层的处理，字段要对应excel表的字段

packagecn.wangze.domain;

publicclassstudents{

stringid;

stringname;

stringage;

stringsex;

publicstringgetid(){

returnid;

}

publicvoidsetid(stringid){

this.id=id;

}

publicstringgetname(){

returnname;

}

publicvoidsetname(stringname){

this.name=name;

}

publicstringgetage(){

returnage;

}

publicvoidsetage(stringage){

this.age=age;

}

publicstringgetsex(){

returnsex;

}

publicvoidsetsex(stringsex){

this.sex=sex;

}

}

4）、dao层处理：studentsmapper.java是一个接口，业务到数据库需要执行的方法在这里声明，studentsmapper.xml相当于接口的实现类，用来连接java和数据库的操作。

studentsmapper.java代码：

packagecn.wangze.mapper;

importjava.util.list;

publicinterfacestudentsmapper<t>{

publicintinsertsheetdata(list<t>list);

publiclist<t>querylist(tt);

publictquerytotal(tt);

}

studentsmapper.xml代码：

<?xmlversion="1.0"encoding="utf-8"?>

<!doctypemapperpublic"-//mybatis.org//dtdmapper3.0//en""http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mappernamespace="cn.wangze.mapper.studentsmapper">

<sqlid="columnlist">

id,name,age,sex

</sql>

<sqlid="columnlist_t">

t.id,t.name,t.age,t.sex

</sql>

<sqlid="valuelist">

#{id},#{name},#{age},#{sex}

</sql>

<sqlid="whereclause">

where1=1

<iftest="id!=nullandid!=''">andid=#{id}</if>

<iftest="name!=nullandname!=''">andname=#{name}</if>

<iftest="age!=nullandage!=''">andage=#{age}</if>

<iftest="sex!=nullandsex!=''">andsex=#{sex}</if>

</sql>

<sqlid="whereclause_pager">

where1=1

<iftest="t.id!=nullandt.id!=''">andid=#{t.id}</if>

<iftest="t.name!=nullandt.name!=''">andname=#{t.name}</if>

<iftest="t.age!=null">andage=#{t.age}</if>

<iftest="t.sex!=nullandt.sex!=''">andsex=#{t.sex}</if>

</sql>

<sqlid="setclause">

set

<trimsuffixoverrides=",">

<iftest="id!=null">id=#{id},</if>

<iftest="name!=null">name=#{name},</if>

<iftest="pid!=null">age=#{age},</if>

<iftest="url!=null">sex=#{sex},</if>

</trim>

</sql>

<selectid="querylist"resulttype="students">

select<includerefid="columnlist"/>fromstudents

</select>

<selectid="querytotal"parametertype="students"resulttype="students">

select<includerefid="columnlist"/>fromstudents<includerefid="whereclause"/>

<!–(select<includerefid="columnlist"/>fromt_account_casht

<includerefid="whereclausequery"/>groupbyto_char(t.add_time,'yyyy-mm-dd'),t.account_id)a–>

</select>

<insertid="insertsheetdata"usegeneratedkeys="true"parametertype="java.util.list">

<!–<selectkeyresulttype="long"keyproperty="id"order="after">

select

last_insert_id()

</selectkey>–>

insertintostudents(id,name,age,sex)

values

<foreachcollection="list"item="item"index="index"separator=",">

(#{item.id},#{item.name},#{item.age},#{item.sex})

</foreach>

</insert>

</mapper>