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

系统管理员 2022-12-22 04:52 245阅读 0赞

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测试 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21hbWVuZzE5ODg_size_16_color_FFFFFF_t_70_pic_center]

### 7.2 下载excel测试 ###

先在postman中执行http://localhost:8099/excel/getStudent获取要写入excel的数据：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21hbWVuZzE5ODg_size_16_color_FFFFFF_t_70_pic_center 1]  
然后在浏览器中执行http://localhost:8099/excel/download  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21hbWVuZzE5ODg_size_16_color_FFFFFF_t_70_pic_center 2]

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21hbWVuZzE5ODg_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/65e8ebddc5114679b07626753692b2ac.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21hbWVuZzE5ODg_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221120/f42778ecfa7c45a0b058f9044ea2d7f6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21hbWVuZzE5ODg_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221120/4d342b3f8ca142faa18eec594637ff6d.png
[easyexcel]: https://blog.csdn.net/qq_41514643/article/details/106993760
[EasyExcel_Excel]: https://blog.csdn.net/v_ccaili/article/details/107475837
[easyExcel_excel]: https://blog.csdn.net/so_geili/article/details/91621575