SpringBoot整合easyexcel

SpringBoot整合easyexcel

easyexcel:poi工具类，处理excel

官网：[EasyExcel官方文档 - 基于Java的Excel处理工具 | Easy Excel (alibaba.com)](https://easyexcel.opensource.alibaba.com/docs/current/quickstart/write)

1、基础篇

1、导入依赖

<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.1</version>
</dependency>

2、对象

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;

import java.util.Date;

@Getter
@Setter
@ColumnWidth(12) //设置全部宽度
@EqualsAndHashCode
public class DemoData {
    @ColumnWidth(20) //单独设置宽度
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;
    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String ignore;
}

3、工具类

public static List<DemoData> data() {
        List<DemoData> list =new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + i);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        return list;
    }

4、测试

 public static void main(String[] args) {
        // 注意 simpleWrite在数据量不大的情况下可以使用（5000以内，具体也要看实际情况），数据量大参照 重复多次写入

        // 写法1 JDK8+
        // since: 3.0.0-beta1
        String fileName =  "E:\\IDE项目\\ide项目\\SpringBoot\\springboot-easyexcel\\src\\main"+ ".xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        EasyExcel.write(fileName, DemoData.class)
                .sheet("模板")
                .doWrite(data());
    }

2、进阶篇

1、vue

    //导入excel
    exportexcel(){
      console.log(this.multipleSelection)
      this.axios.post("http://localhost:8001/tMeeting/exportexcel",
        {meetingmap: this.multipleSelection},
        {responseType:"blob"}).then(response => {
        let blob = new Blob([response.data], { type: 'application/vnd.ms-excel;charset=utf-8' })
        let downloadElement = document.createElement('a');
        let href = window.URL.createObjectURL(blob); //创建下载的链接
        downloadElement.href = href;
        downloadElement.download = '会议通知.xlsx';     //下载后文件名
        document.body.appendChild(downloadElement);
        downloadElement.click();                     //点击下载
        document.body.removeChild(downloadElement);  //下载完成移除元素
        window.URL.revokeObjectURL(href);            //释放掉blob对象
        this.$refs.multipleTable.clearSelection();//清空表格所选中的选项
      })

    }

2、工具类

package edu.hunnan.net.util;
/**
 * 导入会议Excel
 */
@Component
public class TMeetingExcel {
    private List<TMeeting> data(List<TMeeting> meetings) {
        List<TMeeting> list = ListUtils.newArrayList();
        for (TMeeting meeting : meetings) {
            if(meeting.getIsStatus().equals("0")){
                meeting.setIsStatus("审批中");
            }else{
                meeting.setIsStatus("已审批");
            }
            if (meeting.getIsMessage().equals("0")) {
                meeting.setIsMessage("暂未通知");
            }else{
                meeting.setIsMessage("已通知");
            }
            list.add(meeting);
        }
        return list;
    }

    public void simpleWrite(HttpServletResponse response,List<TMeeting> meetings) throws IOException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), TMeeting.class).sheet("会议通知").doWrite(data(meetings));

    }

}

3、controller

 /**
     * 会议通知导入Excel
     * @param meetingmap
     */
    @PostMapping("/exportexcel")
    public void exportExcel(@RequestBody Map<String,List<TMeeting>> meetingmap, HttpServletResponse response) throws IOException {
        for (Map.Entry<String, List<TMeeting>> listEntry : meetingmap.entrySet()) {
            for (TMeeting tMeeting : listEntry.getValue()) {
                log.info("会议是->{}",tMeeting);
            }
            tMeetingExcel.simpleWrite(response,listEntry.getValue());
        }
    }

3、实战

1、导入

1.1、controller

   /**
     * 学生基本信息导入
     * @param file
     * @return
     */
    @PostMapping("/studentInfoImportExcel")
    public ResponseEntity studentImportExcel(@RequestParam("file") MultipartFile file){
        int i = studentAchievementService.insertBatchStudentInfo(file);
        if(i == 1){
            return ResponseEntity.ok("请求成功");
        }else if(i == 2){
            return ResponseEntity.status(500).body("格式错误");
        }

        return ResponseEntity.status(500).body("导入失败");
    }

1.2、service

  //解析Excel
	public List<StudentInfoExcel insertBatchStudentInfo(MultipartFile file){
        return studentInfoExcelList = studentInfoExcel(file.getInputStream());
    }
     

 /**
     * 解析学生基本信息模板Excel
     *
     * @param inputStream
     * @return
     */
    public List<StudentInfoExcel> studentInfoExcel(InputStream inputStream) {
        List<StudentInfoExcel> list = new ArrayList<>();

        EasyExcel.read(inputStream).head(StudentInfoExcel.class).sheet("大学生人才信息表") //表格名称一致否则找不到
                //通过监听器去解析表格
                .registerReadListener(new AnalysisEventListener<StudentInfoExcel>() {
                    @Override
                    //一行一行的进行解析
                    public void invoke(StudentInfoExcel studentInfoExcel, AnalysisContext analysisContext) {
                        list.add(studentInfoExcel);
                    }

                    @Override
                    //解析完成之后执行此回调
                    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                        System.out.println("解析完成");
                    }
                }).doRead();//doRead开始执行
        return list;
    }

1.3、vue

//学生基本信息Excel导入
    studentInfoImportExcel(item){
      this.loading = true;
      let fileObj = item.file;
      console.log(fileObj);
      const form = new FormData();
      form.append("file", fileObj);
      // form.append("type", type);
      this.$http({
        url: "/proxyApi/admin/studentTraceability/studentInfoImportExcel",
        method: "POST",
        headers: { "Content-type": "multipart/form-data" },
        data: form
      }).then(res => {
        this.loading = false;
        console.log(res);
        this.$refs.upload.clearFiles(); //清空上传组件
        if (res.status == 200) {
          this.$message({
            message: "导入成功",
            type: "success"
          });
        }
      });
    },

2、下载

2.1、controller

   /**
     * 成绩以及学生信息下载
     * @param response
     * @param list
     * @return
     */
    @PostMapping("/downloadExcel")
    public void downloadExcel(HttpServletResponse response,@RequestBody List<StudentInfoDownloadExcel> list) {
        studentTracingInfoService.downloadExcel(response,list);
    }

2.2、service

 @Override
    public void downloadExcel(HttpServletResponse response, List<StudentInfoDownloadExcel> list) {
        if(list.size() == 0){
            try {
                throw new Exception("请至少选择一个");
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }
        List list1 = new ArrayList();
        


        String fileName = null;
        try {
            fileName = URLEncoder.encode( "成绩及基本信息下载" + ".xlsx", "utf-8");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName);
            EasyExcel.write(response.getOutputStream(), StudentInfoDownloadExcel.class)
                    .sheet("成绩及基本信息")
                    .doWrite(list);
        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

    }

2.3、vue

        
        
         //成绩下载
    download() {
      if (this.multipleSelection.length == 0) {
        this.$message({
          message: "请至少选择一个下载",
          type: "warning"
        });
        return;
      }
      console.log(this.multipleSelection);
      this.loading = true;
      this.$http({
        url: "/proxyApi/admin/studentTraceability/downloadExcel",
        method: "post",
        data: this.multipleSelection,
        responseType: "blob"
      }).then(res => {
        console.log("res data = " + res.data);
        let blob = new Blob([res.data], {
          type: "application/vnd.ms-excel;charset=utf-8"
        }); // 文件类型
        console.log(res.headers["content-disposition"]); // 从response的headers中获取filename, 后端response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx") 设置的文件名;
        //以=分割取数组[1]元素为文件名
        let filename = window.decodeURI(
          res.headers["content-disposition"].split("=")[1]
        );
        let url = window.URL.createObjectURL(blob); // 创建下载链接
        let aLink = document.createElement("a"); // 赋值给a标签的href属性
        aLink.style.display = "none";
        aLink.href = url;
        aLink.setAttribute("download", filename);
        document.body.appendChild(aLink); // 将a标签挂载上去
        aLink.click(); // a标签click事件
        document.body.removeChild(aLink); // 移除a标签
        window.URL.revokeObjectURL(url); // 销毁下载链接
        this.loading = false;
        this.$message({
          message: "下载成功",
          type: "success"
        });
      });
    },