記錄：文件導(dǎo)入導(dǎo)出功能。

前提準(zhǔn)備：
依賴包

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

一、導(dǎo)入功能
導(dǎo)入模板表

image.png

上面的每一列的列名和實(shí)體類對應(yīng)

@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;
}

/**
     * 導(dǎo)入功能
     */
    @RequestMapping("/import1")
    public void import1(MultipartFile file) {
        try {
            //從文件中讀取Excel為ExcelReader
            ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
            //轉(zhuǎn)換成對應(yīng)的實(shí)體類
            List<Book> books = reader.readAll(Book.class);
            //插入到數(shù)據(jù)庫
            books.forEach(book->bookMapper.insert(book));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

測試

image.png

測試結(jié)果

image.png

二、導(dǎo)出功能

/**
     * 導(dǎo)出功能
     * @param response
     */
    @RequestMapping("/export1")
    public void export(HttpServletResponse response) {
        //查詢所有導(dǎo)出數(shù)據(jù)
        List<Book> list=celService.list(new QueryWrapper<Book>());
        // 通過工具類創(chuàng)建writer，默認(rèn)創(chuàng)建xls格式
        ExcelWriter writer = ExcelUtil.getWriter();
        //自定義表格列名 給實(shí)體類對應(yīng)的字段取名字
        writer.addHeaderAlias("id", "編號");
        writer.addHeaderAlias("bookName", "書名");
        writer.addHeaderAlias("bookCounts", "數(shù)量");
        writer.addHeaderAlias("detail", "描述");
        writer.addHeaderAlias("dbSource", "來源");
        // 合并單元格后的標(biāo)題行，使用默認(rèn)標(biāo)題樣式
        writer.merge(4, "書籍表");
        // 一次性寫出內(nèi)容，使用默認(rèn)樣式，強(qiáng)制輸出標(biāo)題
        writer.write(list, true);
        //out為OutputStream，需要寫出到的目標(biāo)流
        //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 {
            // 關(guān)閉writer，釋放內(nèi)存
            writer.close();
        }
        //此處記得關(guān)閉輸出Servlet流
        IoUtil.close(out);
    }

測試
http://localhost:8080/api/excel/export1

輸出結(jié)果

image.png