记录：文件导入导出功能。

前提准备：
依赖包

<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.6.3</version>
</dependency>

一、导入功能
导入模板表

image.png

上面的每一列的列名和实体类对应

@Data
@TableName("book")
public class Book implements Serializable {
    @TableId(value = "id", type = IdType.AUTO)
    private Long id;
    @TableField("book_name")
    private String bookName;
    @TableField("book_counts")
    private Integer bookCounts;
    private String detail;
    @TableField("db_source")
    private String dbSource;
}

/**
     * 导入功能
     */
    @RequestMapping("/import1")
    public void import1(MultipartFile file) {
        try {
            //从文件中读取Excel为ExcelReader
            ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
            //转换成对应的实体类
            List<Book> books = reader.readAll(Book.class);
            //插入到数据库
            books.forEach(book->bookMapper.insert(book));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

测试

image.png

测试结果

image.png

二、导出功能

/**
     * 导出功能
     * @param response
     */
    @RequestMapping("/export1")
    public void export(HttpServletResponse response) {
        //查询所有导出数据
        List<Book> list=celService.list(new QueryWrapper<Book>());
        // 通过工具类创建writer，默认创建xls格式
        ExcelWriter writer = ExcelUtil.getWriter();
        //自定义表格列名 给实体类对应的字段取名字
        writer.addHeaderAlias("id", "编号");
        writer.addHeaderAlias("bookName", "书名");
        writer.addHeaderAlias("bookCounts", "数量");
        writer.addHeaderAlias("detail", "描述");
        writer.addHeaderAlias("dbSource", "来源");
        // 合并单元格后的标题行，使用默认标题样式
        writer.merge(4, "书籍表");
        // 一次性写出内容，使用默认样式，强制输出标题
        writer.write(list, true);
        //out为OutputStream，需要写出到的目标流
        //response为HttpServletResponse对象
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        //输出表名编码
        ServletOutputStream out = null;
        try {
            String name = new String("书籍表.xls"
                    .getBytes("UTF-8"), "ISO-8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + name );
            out = response.getOutputStream();
            writer.flush(out, true);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            // 关闭writer，释放内存
            writer.close();
        }
        //此处记得关闭输出Servlet流
        IoUtil.close(out);
    }

测试
http://localhost:8080/api/excel/export1

输出结果

image.png