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
 */
@Data
public class Student {
    /*
     * @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
@NoArgsConstructor
public class ResultResponse {
    private Integer status;
    private String msg;
    private Object data;

    public static ResultResponse fail(){

        return new ResultResponse(201,"业务调用失败",null);
    }

    public static ResultResponse fail(String msg){

        return new ResultResponse(201,msg,null);
    }

    public static ResultResponse success(){

        return new ResultResponse(200,"业务调用成功",null);
    }

    public static ResultResponse success(Object data){

        return  new ResultResponse(200,"业务调用成功",data);
    }

    public static ResultResponse success(String msg,Object data){

        return new ResultResponse(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")
public class ExcelController {

    @Autowired
    private ExcelService excelService;

    private CopyOnWriteArrayList<Student> studentList;

    public CopyOnWriteArrayList<Student> getStudentList() {
        return studentList;
    }

    public void setStudentList(CopyOnWriteArrayList<Student> studentList) {
        this.studentList = studentList;
    }

    //上传excel
    @RequestMapping("/upload")
    public ResultResponse excelUpload(MultipartFile file){
        ResultResponse response = checkParam(file);
        if (!(200 == response.getStatus())) {
            return response;
        }
        //调用service中的uploadExcel()获取excel中的数据
        List<Student> students = excelService.uploadExcel(file,Student.class,new StudentListener());
        //将得到的excel数据封装后进行返回
        return ResultResponse.success(students);
    }

    private ResultResponse checkParam(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")
    public void getStudent(@RequestBody CopyOnWriteArrayList<Student> students)  {
        setStudentList(students);
    }

    //实现excel下载功能
    @RequestMapping("/download")
    public void downloadExcel(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;

public interface ExcelService {
    List<Student> uploadExcel(MultipartFile file,Class head, 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
 */
@Service
public class ExcelServiceImpl implements ExcelService {

    //读取上传的excel
    @Override
    public List<Student> uploadExcel(MultipartFile file,Class head, 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();
            throw new RuntimeException(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")
public class StudentListener extends AnalysisEventListener<Student> {
    @Override
    public void invoke(Student student, AnalysisContext analysisContext) {

    }

    @Override
    public void doAfterAllAnalysed(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
 */
@Log4j2
public class ExcelUtils {
    public static void writeExcel(String fileName , Class head,
                                  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 ""
     */
    private static ServletOutputStream getOutputStream(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);
            throw new Exception("导出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文件上传和下载