
        <!-- easyexcel 依赖 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.7</version>
        </dependency>

实体类

上传代码,此处为核心代码,每五条插入一次数据库：


 @ApiOperation(value = "上传Excel")
    @GetMapping("put")
    public ResponseResult putExcel(@RequestParam("file") MultipartFile file) {
 
        List<StudentPutFileRead> studentPutFileReadList = new ArrayList<>();
        InputStream inputStream = null;
        try {
            inputStream = file.getInputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
 
        EasyExcel.read(inputStream, StudentPutFileRead.class, new AnalysisEventListener<StudentPutFileRead>() {
            @Override
            public void invoke(StudentPutFileRead studentPutFileRead, AnalysisContext analysisContext) {
                studentPutFileReadList.add(studentPutFileRead);
                if (studentPutFileReadList.size() == 5){
                    //批量插入数据库
                    studentPutReadMapper.batchInsert(studentPutFileReadList);
                }
                // 清空集合
                studentPutFileReadList.clear();
            }
 
            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            }
        }).sheet().doRead();
        return new ResponseResult(200, "插入数据库成功");
    }

StudentPutReadMapper部分：


/**
 * @author yt
 * @create 2022/5/12 15:07
 */
@Mapper
public interface StudentPutReadMapper extends BaseMapper<StudentPutFileRead> {
 
    @Insert({
            "<script>",
            "insert into student_read(name,salary,birthday) values ",
            "<foreach collection='insertList' item='item' separator=','>",
            "(#{item.name}, #{item.salary}, #{item.birthday})",
            "</foreach>",
            "</script>"
    })
    void batchInsert(@Param("insertList") List<StudentPutFileRead> studentPutFileReadList);
}

结果：

1.2.自定义Listener实现拓展业务

自定义StudentListener 继承 AnalysisEventListener<StudentRead>


/**
 * @author yt
 * @create 2022/10/27 16:50
 */
public class StudentListener extends AnalysisEventListener<StudentRead> {
 
 
    private StudentReadMapper studentReadMapper = MyApplicationContext.getBean(StudentReadMapper.class);
 
 
 
    //集合数量
    private Integer NUM = 5;
    //读取第几条
    private static int READ_COUNT = 0;
 
    private ArrayList<StudentRead> studentReadList = new ArrayList<>();
 
 
 
 
    @Override
    public void invoke(StudentRead studentRead, AnalysisContext analysisContext) {
        READ_COUNT++;
        System.out.println("读取数据行数 = " + READ_COUNT);
        studentReadList.add(studentRead);
        if (studentReadList.size() == NUM) {
            for (StudentRead read : studentReadList) {
                studentReadMapper.insert(read);
            }
            studentReadList.clear();
        }
        System.out.println(studentReadList.size());
    }
 
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("共导入 = " + READ_COUNT + "条数据");
    }
}

在自定义的监听器中，无法实现类的注入，需要从ApplicationContext中获取，具体说明在我的另一篇博客中

在EasyExcel.read中即可使用自定义的监听器


 @GetMapping("test3")
    public ResponseResult userTest3(@RequestParam("file") MultipartFile file) {
 
        List<StudentRead> list = new ArrayList<>();
        InputStream inputStream = null;
        try {
            inputStream = file.getInputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
        //使用自定义的监听器
        EasyExcel.read(inputStream, StudentRead.class, new StudentListener()).sheet().doRead();
        return new ResponseResult(200, "上传成功", list);
    }

结果

2.下载数据库导出excel

实体类


@Data
@TableName("student_read")
public class StudentRead implements Serializable {
 
    @ExcelProperty(value = {"学生信息","学生编号"})
    private Integer id;
 
    @ExcelProperty(value = {"学生信息","学生姓名"})
    private String name;
 
    @ExcelProperty(value = {"学生信息","学生薪水"})
    private Double salary;
 
    @ExcelProperty(value = {"学生信息","学生生日"})
    private Date birthday;
}

代码如下，关注核心部分即可（此为window指定用户保存路径的写法）


    /**
     * 下载excel
     *
     * @param
     * @return
     */
    @ApiOperation(value = "下载Excel")
    @GetMapping("down")
    public ResponseResult downExcel() {
        Random random = new Random(8);
        // 下载的地址以及名称
        String PATH = "F:\\文件\\excel下载\\学生测试";
        // 查询数据库
        List<StudentRead> list = studentReadMapper.selectList(null);
        // 核心代码
        EasyExcel.write(PATH + ".xlsx", StudentRead.class)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .sheet().doWrite(list);
        return new ResponseResult(200, "下载成功");
    }

可以用导出流的形式导出excel，代码如下


 
    @GetMapping("down")
    public ApiResponse downloadExcel(HttpServletResponse response) throws IOException {
        List<OrderDO> orderDOList = orderService.findAll();
        String fileName = URLEncoder.encode("名字", "UTF-8");
 
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setContentType("application/force-download");
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
        response.setHeader("Content-Type","application/octet-stream;charset=utf-8");
        /**导入结果流*/
        EasyExcel.write(response.getOutputStream(), DownloadExcel.class).sheet("名字").
                registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).doWrite(orderDOList);
 
        return ApiResponse.success("下载成功");
    }

此方法postman导出会有文件名乱码的问题，如果是浏览器下载的话，文件名是没有问题的。

导出效果

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/tf789/article/detail/61106