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

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>

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

  

  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>