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

2019-11-26 11:43:07

字体：大中小

来源：转载

供稿：网友

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;@Servicepublic 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表：