【Java】基于easy-poi实现Excel表导入与导出

宫野琦

已于 2022-09-08 20:07:05 修改

阅读量1.2k

点赞数

文章标签： java 开发语言 hibernate

于 2022-09-07 20:42:52 首次发布

本文链接：https://blog.csdn.net/weixin_47201411/article/details/126753303

版权

实现功能所需依赖如下

        <!--Excel导入导出-->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-validator</artifactId>
            <version>6.1.3.Final</version>
        </dependency>

一、实现上传Excel表，并将表中数据对应成实体类集合

1、建立数据库表
在这里插入图片描述
2、编写实体类与数据库表对应，要注意@Excel注解中的name属性，对应着生成Excel表格的表头信息

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;

@Data
public class Book {
	// 这里@Excel注解中的name属性制定了之后导出Excel表的第一行表头
    @Excel(name = "编号")
    private Integer id;

    @Excel(name = "书名")
    private String name;

    @Excel(name = "介绍")
    private String description;

    @Excel(name = "价格")
    private double price;
}

3、Service层实现类中的方法

  // 上传表格
    @Override
    public List<Book> uploadFile(MultipartFile multipartFile) {
        // 新建导入对象
        ImportParams importParams = new ImportParams();

        List<Book> bookList = null;
        try {
            // 将表格数据转换为对应实体类集合
            bookList = ExcelImportUtil.importExcel(multipartFile.getInputStream(), Book.class, importParams);
        } catch (Exception e) {
            e.printStackTrace();
        }

        // 添加实体类集合中的实体类数据到数据库表中
        if (bookList != null) {
            for (Book book : bookList) {
                bookMapper.insert(book);
                log.info(book + "");
            }
        }
        return bookList;
    }

4、Controller层实现类

    // 实现上传
    @RequestMapping("/upload")
    public String uploadFile(@RequestParam("file") MultipartFile multipartFile, Model model){
        List<Book> bookList = testService.uploadFile(multipartFile);

        model.addAttribute("bookList", bookList);
        return "book";
    }

5、前端页面

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>

<form action="upload" method="post" enctype="multipart/form-data">
    <input type="file" name="file">
    <input type="submit" value="上传">
</form>

</body>
</html>

二、实现导出Excel表，将实体类集合转换成Excel表

1、controller层代码

 // 导出表格
    @RequestMapping("/download")
    public void download(HttpServletResponse response){

        Workbook workbook = testService.download();

        // 命名表格
        String fileName = "book.xlsx";

        try{
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition","attachment;filename=" + URLEncoder.encode(fileName,"UTF-8"));

            // 输出流
            workbook.write(response.getOutputStream());

            // 清除response
            response.flushBuffer();
        }catch (Exception e){
            log.error(e.getMessage());
        }
    }

2、service层代码

    // 下载
    @Override
    public Workbook download() {
        QueryWrapper<Book> wrapper = new QueryWrapper<>();
        // 查询出后台实体类集合
        List<Book> bookList = bookMapper.selectList(wrapper);

        ExportParams exportParams = new ExportParams();
        exportParams.setType(ExcelType.XSSF); // 对应xlsx

        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, Book.class, bookList);

        return workbook;
    }