SpringBoot集成easyExcel实现excel文件上传和下载

easyExcel是阿里巴巴开源的快速、简单避免OOM的java处理Excel工具，话不多说直接上代码。

1 在POM中引入EasyExcel的依赖

<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.6</version></dependency>

2 根据Excel中的数据，写一个对应的VO类

import com.alibaba.excel.annotation.ExcelProperty;import com.alibaba.excel.annotation.write.style.ColumnWidth;import lombok.Data;import java.util.Date;/**
 * @author mazhen
 * @className Student
 * @Description TODO
 * @date 2020/11/17 15:30
 */@DatapublicclassStudent{/*
     * @ExcelProperty(value = "ID") 将列与Excel中的列名进行对应
     * 也可不加此注解，但就要求Excel中列名与类的属性名要一致
     */@ExcelProperty(value="ID")private Integer id;@ExcelProperty(value="姓名")private String name;@ExcelProperty(value="性别")private String gender;@ColumnWidth(20)@ExcelProperty(value="生日")private Date birthday;@ExcelProperty(value="成绩")private Integer score;}

3 返回值封装类

import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;/**
 * @author mazhen
 * @className ResultResponse
 * @Description TODO
 * @date 2020/11/17 15:35
 */@Data@AllArgsConstructor@NoArgsConstructorpublicclassResultResponse{private Integer status;private String msg;private Object data;publicstatic ResultResponsefail(){returnnewResultResponse(201,"业务调用失败",null);}publicstatic ResultResponsefail(String msg){returnnewResultResponse(201,msg,null);}publicstatic ResultResponsesuccess(){returnnewResultResponse(200,"业务调用成功",null);}publicstatic ResultResponsesuccess(Object data){returnnewResultResponse(200,"业务调用成功",data);}publicstatic ResultResponsesuccess(String msg,Object data){returnnewResultResponse(200,msg,data);}}

4 上传和下载excel的controller

import lombok.extern.log4j.Log4j2;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.util.StringUtils;import org.springframework.web.bind.annotation.RequestBody;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;import org.springframework.web.multipart.MultipartFile;import javax.servlet.http.HttpServletResponse;import java.util.List;import java.util.concurrent.CopyOnWriteArrayList;/**
 * @author mazhen
 * @className ExcelController
 * @Description TODO
 * @date 2020/11/17 15:48
 */@Log4j2@RestController@RequestMapping("/excel")publicclassExcelController{@Autowiredprivate ExcelService excelService;private CopyOnWriteArrayList<Student> studentList;public CopyOnWriteArrayList<Student>getStudentList(){return studentList;}publicvoidsetStudentList(CopyOnWriteArrayList<Student> studentList){this.studentList= studentList;}//上传excel@RequestMapping("/upload")public ResultResponseexcelUpload(MultipartFile file){
        ResultResponse response=checkParam(file);if(!(200== response.getStatus())){return response;}//调用service中的uploadExcel()获取excel中的数据
        List<Student> students= excelService.uploadExcel(file,Student.class,newStudentListener());//将得到的excel数据封装后进行返回return ResultResponse.success(students);}private ResultResponsecheckParam(MultipartFile file){if(null== file){return ResultResponse.fail("上传的文件为空");}
        String filename= file.getOriginalFilename();if(StringUtils.isEmpty(filename)){return ResultResponse.fail("文件格式异常");}if(!filename.contains(".xlsx")&&!filename.contains(".XLSX")){return ResultResponse.fail("文件格式错误");}return ResultResponse.success();}//先从前端获取studentList@RequestMapping("/getStudent")publicvoidgetStudent(@RequestBody CopyOnWriteArrayList<Student> students){setStudentList(students);}//实现excel下载功能@RequestMapping("/download")publicvoiddownloadExcel(HttpServletResponse response){try{
            ExcelUtils.writeExcel("学生信息",Student.class,response,getStudentList());}catch(Exception e){
            log.error("导出excel表格失败:", e);}}}

5 上传excel的service及StudentListener

import org.springframework.web.multipart.MultipartFile;import java.util.List;publicinterfaceExcelService{
    List<Student>uploadExcel(MultipartFile file,Classhead, StudentListener listener);}

import com.alibaba.excel.EasyExcel;import com.alibaba.excel.read.builder.ExcelReaderBuilder;import com.alibaba.excel.read.builder.ExcelReaderSheetBuilder;import org.springframework.stereotype.Service;import org.springframework.web.multipart.MultipartFile;import java.io.IOException;import java.util.List;/**
 * @author mazhen
 * @className ExcelServiceImpl
 * @Description TODO
 * @date 2020/11/17 15:43
 */@ServicepublicclassExcelServiceImplimplementsExcelService{//读取上传的excel@Overridepublic List<Student>uploadExcel(MultipartFile file,Classhead, StudentListener listener){try{//1.获取工作簿
            ExcelReaderBuilder readBook= EasyExcel.read(file.getInputStream(), head, listener);//2.获取sheet
            ExcelReaderSheetBuilder sheet= readBook.sheet();//3.获取Excel中的数据
            List<Student> students= sheet.doReadSync();//4.返回数据return students;}catch(IOException e){
            e.printStackTrace();thrownewRuntimeException(e);}}}

package com.cloudpath.iam.gatewayservice.excel;import com.alibaba.excel.context.AnalysisContext;import com.alibaba.excel.event.AnalysisEventListener;import org.springframework.context.annotation.Scope;import org.springframework.stereotype.Component;/**
 * @author mazhen
 * @className StudentListener
 * @Description TODO
 * @date 2020/11/17 15:39
 */@Component@Scope("prototype")publicclassStudentListenerextendsAnalysisEventListener<Student>{@Overridepublicvoidinvoke(Student student, AnalysisContext analysisContext){}@OverridepublicvoiddoAfterAllAnalysed(AnalysisContext analysisContext){}}

6 下载Excel的工具类

import com.alibaba.excel.EasyExcel;import com.alibaba.excel.write.builder.ExcelWriterBuilder;import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;import lombok.extern.log4j.Log4j2;import org.springframework.http.MediaType;import javax.servlet.ServletOutputStream;import javax.servlet.http.HttpServletResponse;import java.io.IOException;import java.net.URLEncoder;import java.util.concurrent.CopyOnWriteArrayList;/**
 * @author mazhen
 * @className ExcelUtils
 * @Description TODO
 * @date 2020/11/17 20:41
 */@Log4j2publicclassExcelUtils{publicstaticvoidwriteExcel(String fileName, Classhead,
                                  HttpServletResponse response, CopyOnWriteArrayList list){try{
            ServletOutputStream outputStream=getOutputStream(fileName,response);
            ExcelWriterBuilder writeBook= EasyExcel.write(outputStream, head);
            ExcelWriterSheetBuilder sheet= writeBook.sheet(fileName);
            sheet.doWrite(list);}catch(Exception e){
            log.error("导出excel表格失败:", e);}}/**
     * 导出文件时为Writer生成OutputStream.
     *
     * @param fileName 文件名
     * @param response response
     * @return ""
     */privatestatic ServletOutputStreamgetOutputStream(String fileName,
                                                       HttpServletResponse response)throws Exception{try{
            fileName= URLEncoder.encode(fileName,"UTF-8");//设置响应的类型
            response.setContentType(MediaType.MULTIPART_FORM_DATA_VALUE);//设置响应的编码格式
            response.setCharacterEncoding("utf8");//设置响应头
            response.setHeader("Content-Disposition","attachment;filename="+ fileName+".xlsx");
            response.setHeader("Pragma","public");
            response.setHeader("Cache-Control","no-store");
            response.addHeader("Cache-Control","max-age=0");return response.getOutputStream();}catch(IOException e){
            log.error("导出excel表格失败:", e);thrownewException("导出excel表格失败!", e);}}}

7 测试

7.1 上传Excel测试

7.2 下载excel测试

先在postman中执行http://localhost:8099/excel/getStudent获取要写入excel的数据：

然后在浏览器中执行http://localhost:8099/excel/download

参考：使用easyexcel完成复杂表头及标题的导出功能
EasyExcel实现上传和下载Excel数据
easyExcel实现excel文件上传和下载