EasyExcel的数据写入和导出

目录

总体效果:

要上传的excel文件

postman请求服务器上传

写入数据库成功！

1.上传excel代码部分

引入easyexcel依赖

实体类

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

StudentPutReadMapper部分：

 结果：

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

结果 

2.下载数据库导出excel

实体类

代码如下，关注核心部分即可

导出效果

---

总体效果:

要上传的excel文件

postman请求服务器上传

写入数据库成功！

1.上传excel代码部分

引入easyexcel依赖

        <!-- 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导出会有文件名乱码的问题，如果是浏览器下载的话，文件名是没有问题的。

导出效果