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页面
<%@ page language="java" import="java.util.*" pageencoding="utf-8"%> <%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <!doctype html public "-//w3c//dtd html 4.01 transitional//en"> <html> <head> <title>excel文件处理</title> <script type="text/javascript" src="<c:url value='/res/js/jquery.js'/>"></script> <script> $(function(){ var $wrap = $(".wrap"); var find = function(str){ return $wrap.find(str); } var getjname = function(name){ return find("input[name='"+name+"']"); } getjname("upload").click(function(){ var form = new formdata(document.getelementbyid("tf")); $.ajax({ url:"<c:url value='/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:url value='/file/downloadexcel'/>",{"id":"3"},function(data){ //alert("下载文件成功"); },"json") }) }) </script> </head> <body> <div class="wrap"> <form id="tf"> <p> <input type="file" name="file" value="选择文件"/> excel文件上传:<input type="button" name="upload" value="upload"/> </p> <p> excel文件下载:<input type="button" name="download" value="updown"/> </p> </form> </div> </body> </html>
3、依次创建controller、service、domain、mapper层,注意它们的依赖关系
1)、controller层的处理,在handleexcelcontroller里面注入baseexcelservice。因为只是做个示范,所欲我这里将泛型固定为students类
baseexcelcontroller代码:
package cn.wangze.controller; import java.io.file; import java.io.fileinputstream; import java.io.filenotfoundexception; import java.io.fileoutputstream; import java.io.ioexception; import java.io.inputstream; import java.lang.reflect.method; import java.text.simpledateformat; import java.util.collection; import java.util.date; import java.util.iterator; import java.util.list; import javax.servlet.http.httpservletresponse; import org.apache.commons.lang.stringutils; import org.apache.poi.hssf.usermodel.hssfcell; import org.apache.poi.hssf.usermodel.hssfcellstyle; import org.apache.poi.hssf.usermodel.hssffont; import org.apache.poi.hssf.usermodel.hssfpalette; import org.apache.poi.hssf.usermodel.hssfrichtextstring; import org.apache.poi.hssf.usermodel.hssfrow; import org.apache.poi.hssf.usermodel.hssfsheet; import org.apache.poi.hssf.usermodel.hssfworkbook; import org.apache.poi.hssf.util.cellrangeaddress; import org.apache.poi.hssf.util.hssfcolor; import org.apache.poi.ss.usermodel.sheet; import org.apache.poi.xssf.usermodel.xssfworkbook; import org.springframework.web.multipart.multipartfile; import cn.wangze.domain.students; public class baseexcelcontroller extends basecontroller{ //获取文件的路径 string separator = system.getproperty("file.separator"); //验证元素是否为空 @suppresswarnings("all") public boolean isempty(object obj){ if(obj instanceof object[]){ if(((object[]) obj).length==0){ return true; } if(obj == null) return true; if((string.valueof(obj).trim()).length() == 0){ return true; } if(obj instanceof list){ if(((list) obj) == null || ((list)obj).size() == 0){ return true; } } } return false; } /** * 文件上传部分 * */ //验证文件 protected boolean checkpathname(string filename,httpservletresponse response){ //验证文件是否存在 if(isempty(filename)){ senderror("上传文件不存在",response); return false; } //验证文件是否是以xls或者xlsx做后缀的文件,如果不是就返回错误信息 if(!(stringutils.endswithignorecase(filename,".xls")||stringutils.endswithignorecase(filename, ".xlsx"))){ senderror("上传文件类型错误,请核对后重新上传?",response); } return true; } //获取文件的sheet protected sheet getsheet(multipartfile file,string path,string filename) throws illegalstateexception, ioexception{ //找到要存放到项目里面的路径,新建文件 file targetfile = new file(path, filename); targetfile.mkdirs(); if (targetfile.exists()) { targetfile.delete(); file.transferto(targetfile); } else { file.transferto(targetfile); } //封装输入流,封装sheet里面的内容 inputstream is = null; try{ is = new fileinputstream(path+separator+filename); //判断版本是否为excel加强版 if(stringutils.endswithignorecase(filename, ".xls")){ return new hssfworkbook(is).getsheetat(0); }else if(stringutils.endswithignorecase(filename, ".xlsx")){ return new xssfworkbook(is).getsheetat(0); } return null; } finally{ if(is != null){ is.close(); } } } /** * 文件下载部分 * */ //根据传入的sting值,判断生成在excel表的位置 private hssfcellstyle getpublicstyle(hssfworkbook workbook,string key){ hssffont font = workbook.createfont(); hssfcellstyle style = workbook.createcellstyle(); hssfpalette custompalette = 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); } return style; } //创建head头信息 private void createhead(hssfsheet sheet,hssfcellstyle style,string[] title){ hssfrow row1 = sheet.createrow(0); hssfcell celltitle = row1.createcell(0); celltitle.setcellvalue(new hssfrichtextstring(title[0])); sheet.addmergedregion(new cellrangeaddress(0,0,0,title.length-2)); celltitle.setcellstyle(style); } //创建title信息 private void createtitle(hssfsheet sheet,hssfcellstyle style,string[] label,int columnnum){ hssfrow row2 = sheet.createrow(1); hssfcell cell1 = null; for(int n=0;n<columnnum;n++){ cell1 = row2.createcell(n); cell1.setcellvalue(label[n+1]); cell1.setcellstyle(style); } } //创建content数据信息 private void createcontent(hssfsheet sheet,hssfcellstyle style,collection<students> list,int columnnum,string[] parameters){ int index= 0; iterator<students> it = list.iterator(); while(it.hasnext()){ index++; students cash = it.next(); int num2 = parameters.length; hssfrow row = sheet.createrow(index+1); initcells(style, num2,cash, parameters,row); } } //验证是否为中文 public boolean checkchinese(string s){ int n=0; boolean flag =false; for(int i=0; i<s.length(); i++) { n = (int)s.charat(i); flag=(19968 <= n && n <40623)?true:false; } return flag; } //将数据设置到excel表格内 public void initcells(hssfcellstyle style, int columnnum, students t, string[] endcontent, hssfrow row3) { for(int j=0;j<columnnum;j++){ hssfcell cell = row3.createcell(j); string fieldname = endcontent[j]; try{ if(fieldname!="" && !checkchinese(fieldname)){ string getmethodname = "get" +fieldname.substring(0,1).touppercase()+fieldname.substring(1); class clazz = t.getclass(); method getmethod = clazz.getmethod(getmethodname, new class[]{}); string value = (string)getmethod.invoke(t, new object[]{}); cell.setcellvalue(value); }else{ cell.setcellvalue(fieldname); } cell.setcellstyle(style); }catch(exception e){ e.printstacktrace(); } } } public void createend(hssfsheet sheet,hssfcellstyle style,int numtext,int columnnum,students t,string[] endcontent){ hssfrow row3 = sheet.createrow(numtext+2); initcells(style, columnnum, t, endcontent, row3); } //根据service查询到的数据,创建excel表并插入查询的数据信息 protected string getoutputname(list<students> list, string path, string[] title, string[] parameters, students t, string[] endcontent) throws ioexception{ //根据传入的title数组的第一个值,设置文件名称 string filename = title[0]+"_"+ new simpledateformat("yyyymmdd").format(new date())+".xls"; //输出流放到文件的本地位置 fileoutputstream fos = new fileoutputstream(path+separator+filename); //列数,根据title的个数,除去第一个就是每列title的信息 int columnnum = title.length-1; int numtext = list.size(); hssfworkbook workbook = new hssfworkbook(); hssfsheet sheet = workbook.createsheet(); sheet.setdefaultcolumnwidth (20); sheet.setdefaultrowheight((short)400); hssfcellstyle contentstyle = this.getpublicstyle(workbook,""); hssfcellstyle titlestyle = this.getpublicstyle(workbook,"title"); hssfcellstyle headerstyle = 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(); return filename; } }
handleexcelcontroller用来处理前端请求,代码如下:
package cn.wangze.controller; import java.io.file; import java.util.list; import javax.servlet.servletoutputstream; import javax.servlet.http.httpservletresponse; import javax.servlet.http.httpsession; import org.apache.commons.io.fileutils; import org.springframework.beans.factory.annotation.autowired; import org.springframework.stereotype.controller; import org.springframework.web.bind.annotation.requestmapping; import org.springframework.web.multipart.multipartfile; import cn.wangze.domain.students; import cn.wangze.service.baseexcelservice; @controller @requestmapping("/file") public class handleexcelcontroller extends baseexcelcontroller{ @autowired private baseexcelservice baseexcelservice; @requestmapping("/uploadexcel") public void uploadexcel(multipartfile file,httpsession session,httpservletresponse response) throws exception{ //如果上传的文件不存在,抛出异常 if(file == null){ throw new exception("文件不存在"); } //获取文件名 string filename = file.getoriginalfilename(); //选择上传的文件存放到项目的路径 string path = session.getservletcontext().getrealpath(separator+"res"+separator+"upload"); if(!checkpathname(filename,response)) return ; string msg = baseexcelservice.loadexcel(getsheet(file, path, filename)); sendmsg(true,msg,response); } @requestmapping("/downloadexcel") public void updownexcel(students student,httpservletresponse res,httpsession session,httpservletresponse response) throws exception{ list<students> stus = baseexcelservice.querylist(student); if(stus.size()==0){ res.sendredirect("/index.jsp"); return; } //下载的excel文件存放的本地路径 string path = session.getservletcontext().getrealpath(separator+"res"+separator+"exportexcel"+separator); servletoutputstream os = res.getoutputstream(); students t = baseexcelservice.querytotal(student); //标题文字,数值中的第一个值+当前日期为文件名称,以后的每个元素为每列的标题 string[] title={"studets04","id","名字","年龄","性别"};//标题文字 //对应实体类的属性值 string[] parameters ={"id","name","age","sex"}; string[] endcontent = {"","","",""}; //调用父类的处理方法,生成excel文件 string filename = 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=" +new string(filename.getbytes("utf-8"),"iso-8859-1")); os.write(fileutils.readfiletobytearray(new file(path+separator+filename))); sendresult(true,response); os.flush(); } finally { if (os != null) { os.close(); } } } }
2)、service层的处理,把studentsmapper注入到baseexcelservice
baseexcelservice代码:
package cn.wangze.service; import java.util.arraylist; import java.util.hashmap; import java.util.list; import java.util.map; import javax.servlet.servletoutputstream; import javax.servlet.http.httpsession; import org.apache.poi.ss.usermodel.cell; import org.apache.poi.ss.usermodel.row; import org.apache.poi.ss.usermodel.sheet; import org.springframework.beans.factory.annotation.autowired; import org.springframework.stereotype.service; import cn.wangze.domain.students; import cn.wangze.mapper.studentsmapper; @service public class baseexcelservice { @autowired private studentsmapper<students> studentsmapper; //判断字符串是否为空 public boolean isempty(string str) { return str == null || str.length() == 0; } //获取单个表格(字段)存放的信息 private string getvalue(cell cell,string celllable,map<string,string> errmap){ cell.setcelltype(cell.cell_type_string); string value = cell.getstringcellvalue().trim(); return value; } //通过这个方法将excel表的每行的数据放到info对象里面 private string addinfo(row row,students info){ map<string,string> errmap = new hashmap<string,string>(); string id = getvalue(row.getcell(0),"id",errmap); string username = getvalue(row.getcell(1),"姓名",errmap); string age = getvalue(row.getcell(2),"年龄",errmap); string sex = getvalue(row.getcell(3),"性别",errmap); string errmsg = errmap.get("errmsg"); if(!isempty(errmsg)){ return errmsg; } info.setid(id); info.setname(username); info.setage(age); info.setsex(sex); return null; } public string loadexcel(sheet sheet) throws exception{ //新建一个list集合,用来存放所有行信息,即每行为单条实体信息 list<students> infos = new arraylist<students>(); //获取到数据行数,第一行是title,不需要存入数据库,所以rownum从1开始 for (int rownum = 1; rownum <= sheet.getlastrownum(); rownum++) { students info = new students(); string errmsg2 = addinfo(sheet.getrow(rownum),info); if(errmsg2 != null) return errmsg2; infos.add(info); } if(infos.isempty()){ return "没有解析到学生数据,请查验excel文件"; } //通过studentsmapper的insertsheetdata方法,将实体类存放的数据插入到数据库 int result = studentsmapper.insertsheetdata(infos); //若插入成功会返回大于1的整数,返回success if(result >= 1){ return "success"; } return "error"; } //查询所有数据库存放的学生信息 public list<students> querylist(students students){ return studentsmapper.querylist(students); } //获取到的学生实体信息 public students querytotal(students students){ return studentsmapper.querytotal(students); } public void downexcel(httpsession session,string separator){ } }
3)、实体层的处理,字段要对应excel表的字段
package cn.wangze.domain; public class students { string id; string name; string age; string sex; public string getid() { return id; } public void setid(string id) { this.id = id; } public string getname() { return name; } public void setname(string name) { this.name = name; } public string getage() { return age; } public void setage(string age) { this.age = age; } public string getsex() { return sex; } public void setsex(string sex) { this.sex = sex; } }
4)、dao层处理:studentsmapper.java是一个接口,业务到数据库需要执行的方法在这里声明,studentsmapper.xml相当于接口的实现类,用来连接java和数据库的操作。
studentsmapper.java代码:
package cn.wangze.mapper; import java.util.list; public interface studentsmapper<t> { public int insertsheetdata(list<t> list); public list<t> querylist(t t); public t querytotal(t t); }
studentsmapper.xml代码:
<?xml version="1.0" encoding="utf-8"?> <!doctype mapper public "-//mybatis.org//dtd mapper 3.0//en" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="cn.wangze.mapper.studentsmapper"> <sql id="columnlist"> id,name,age,sex </sql> <sql id="columnlist_t" > t.id,t.name,t.age,t.sex </sql> <sql id="valuelist"> #{id},#{name},#{age},#{sex} </sql> <sql id="whereclause"> where 1=1 <if test="id!=null and id!=''">and id=#{id}</if> <if test="name!=null and name!=''">and name=#{name}</if> <if test="age!=null and age!=''">and age=#{age}</if> <if test="sex!=null and sex!=''">and sex=#{sex}</if> </sql> <sql id="whereclause_pager" > where 1=1 <if test="t.id!=null and t.id!=''">and id=#{t.id}</if> <if test="t.name!=null and t.name!=''">and name=#{t.name}</if> <if test="t.age!=null">and age=#{t.age}</if> <if test="t.sex!=null and t.sex!=''">and sex=#{t.sex}</if> </sql> <sql id="setclause" > set <trim suffixoverrides="," > <if test="id!=null">id=#{id},</if> <if test="name!=null">name=#{name},</if> <if test="pid!=null">age=#{age},</if> <if test="url!=null">sex=#{sex},</if> </trim> </sql> <select id="querylist" resulttype="students"> select <include refid="columnlist"/> from students </select> <select id="querytotal" parametertype="students" resulttype="students"> select <include refid="columnlist" /> from students <include refid="whereclause"/> <!-- (select <include refid="columnlist"/> from t_account_cash t <include refid="whereclausequery"/> group by to_char(t.add_time,'yyyy-mm-dd'),t.account_id) a --> </select> <insert id="insertsheetdata" usegeneratedkeys="true" parametertype="java.util.list"> <!-- <selectkey resulttype="long" keyproperty="id" order="after"> select last_insert_id() </selectkey> --> insert into students (id,name,age,sex) values <foreach collection="list" item="item" index="index" separator="," > (#{item.id},#{item.name},#{item.age},#{item.sex}) </foreach> </insert> </mapper>
所有的代码就是这些了,操作的时候需要注意的多是路径的问题。最复杂的就是baseexcelcontroller的操作,它做的事情就是解析上传和创建下载excel文件。
执行完之后的结果图是这样:
在数据库查看上传的excel表:
下载到d:\tomcat\tomcat6.0.32\webapps\excelhandledemo\res\exportexcel文件夹下的excel表:
这里有一点不足的地方,我相信你已经发现了,就是下载完excel表格之后,前端还没有和业务对接上,没有相应的提示来告诉操作人执行结果,只能通过代码设置好的路径去查看文件夹下是否有下载的excel文件,
不过这都是细节问题,相信难不倒聪明的各位。
总结
以上所述是小编给大家介绍的java对excel表格的上传和下载处理方法,希望对大家有所帮助