欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-12-21 09:04:46
excel表格文件的上传和下载,java中涉及到文件肯定会有io流的知识。 而excel文件就要涉及到poi技术,而excel的版本包括:2003-2007和2010两个...

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

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

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

数据库我用的是mysql。

下面是我的项目目录:

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

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

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

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

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

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

上传和下载excel文件:

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

Java对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表:

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

下载到d:\tomcat\tomcat6.0.32\webapps\excelhandledemo\res\exportexcel文件夹下的excel表:

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

这里有一点不足的地方,我相信你已经发现了,就是下载完excel表格之后,前端还没有和业务对接上,没有相应的提示来告诉操作人执行结果,只能通过代码设置好的路径去查看文件夹下是否有下载的excel文件,

不过这都是细节问题,相信难不倒聪明的各位。

总结

以上所述是小编给大家介绍的java对excel表格的上传和下载处理方法,希望对大家有所帮助

上一篇:

下一篇: