SpringBoot整合Easypoi导入导出excel操作

此处为SpringBoot整合Easypoi导入导出excel文件演示

导入的excel文件

依赖

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.0</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>

        <!--easypoi相关依赖-->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.2.0</version>
        </dependency>
    </dependencies>

application.yml配置文件

server:
  port: 8989
  #servlet:
  #  context-path: easypoi   项目名
spring:
  application:
    name: easypoi    #模块名
  thymeleaf:
    cache: false
  datasource:     #数据库相关配置
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8
    username: root
    password: root
  #resources:     #将储存文件的地址定义为静态地址,可以方便直接加载
  #  static-locations: classpath:/static,file:${upload.dir}
upload:  #图片储存的路径
  dir: D:\mine\easypoi_springboot\src\main\resources\static\image
mybatis:
  type-aliases-package: com.test.entity   #包映射
  mapper-locations: com.test.mapper/*.xml  #xml与mapper的映射
  configuration:
    map-underscore-to-camel-case: true   #驼峰

**数据库建表 **

CREATE TABLE `easypoi` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `bir` datetime DEFAULT NULL,
  `habby` varchar(200) DEFAULT NULL,
  `num` varbinary(30) DEFAULT NULL,
  `address` varchar(300) DEFAULT NULL,
  `photo` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8;

实体类

@Data
@ExcelTarget("user")
public class User {
    @Excel(name = "编号")
    private String id;
    @Excel(name = "姓名", width = 30)
    private String name;
    @Excel(name = "生日",format = "yyyy年MM月dd日", width = 20)
    private Date bir;
    @Excel(name = "爱好", width = 30)
    private String habby;
    @Excel(name = "身份证号", width = 30)
    private String num;
    @Excel(name = "家庭住址", width = 20)
    private String address;
    //savaPath 图片储存的地址  type = 2 表示导入的问图片类型     1 为文本
    @Excel(name = "照片",type = 2, savePath = "D:\\mine\\easypoi_springboot\\src\\main\\resources\\static\\image")
    private String photo;
}

mapper接口

@Component
public interface UserMapper {
    //查询所有excel数据
    List<User> findAll();

    //导入excel数据到数据库
    void saveAll(User user);
}

mapper.xml映射

<mapper namespace="com.test.mapper.UserMapper">
    <!--查询所有excel数据-->
    <select id="findAll" resultType="User">
        select id,name,bir,habby,num,address,photo
        from easypoi
    </select>

    <!--导入excel数据到数据库-->
    <!--useGeneratedKeys使用数据库自增,keyProperty自增字段-->
    <insert id="saveAll" parameterType="User" useGeneratedKeys="true" keyProperty="id">
     insert into easypoi values (#{id}, #{name}, #{bir}, #{habby}, #{num}, #{address}, #{photo})
    </insert>
</mapper>

service接口

public interface UserService {
    //查询所有excel数据
    List<User> findAll();

    //导入excel文件到数据库
    void saveAll(MultipartFile excelFile) throws Exception;
}

serviceImpl实现类

@Service
public class UserServiceImpl implements UserService {
    @Autowired
    private UserMapper userMapper;

    /**
     * 查询所有的excel数据
     *
     * @return
     */
    @Override
    public List<User> findAll() {
        return userMapper.findAll();
    }

    /**
     * 导入excel数据到数据库
     *
     * @param excelFile 表单传递的文件
     */
    @Override
    public void saveAll(MultipartFile excelFile) throws Exception {
        //参数1:文件输入流 参数2:实体类类型 参数3:导入的配置信息
        ImportParams params = new ImportParams();
        params.setTitleRows(1);  //文件标题所占行数
        params.setHeadRows(1);  //文件的列名所占的行数
        List<User> users = ExcelImportUtil.importExcel(excelFile.getInputStream(), User.class, params);
        for (User user : users) {
            user.setId(null);  //使用数据库自增id
            //System.out.println(user);
            String fileName = user.getPhoto().substring(user.getPhoto().lastIndexOf("\\") + 1);
            System.out.println("图片名称 : " +  fileName);
            user.setPhoto(fileName);
            userMapper.saveAll(user);
        }
    }

}

controller

@Controller
@RequestMapping("/user")
public class UserController {
    @Autowired
    private UserService userService;

    /**
     * 查询所有excel列表
     *
     * @return
     */
    @RequestMapping("/findAll")
    public String findAll(Model model) {
        List<User> list = userService.findAll();
        model.addAttribute("list", list);
        return "index";
    }

    /**
     * 导入excel文件
     *
     * @param excelFile
     * @return
     * @throws Exception
     */
    @RequestMapping("/import")
    public String excelImport(MultipartFile excelFile) throws Exception {
        userService.saveAll(excelFile);
        return "redirect:/user/findAll";
    }

    /**
     * excel文件导出
     */
    @Value("${upload.dir}")
    private String realPath;
    @RequestMapping("/export")
    public void exportExcel(HttpServletResponse response) throws IOException {
        //先查询到要导出的excel数据
        List<User> userList = userService.findAll();
        //数据库保存的图片名,并不是图片路径,需要获取路径凭借图片名
        userList.forEach(user->{
            user.setPhoto(realPath + "/" +user.getPhoto());
        });
        ExportParams exportParams = new ExportParams();  //导出的excel相关配置
        exportParams.setTitle("用户信息表");  //导出excel的第一行标题名字
        exportParams.setSheetName("用户信息sheet");  //导出的excel的sheet名称
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, User.class, userList);
        response.setHeader("content-disposition", "attachment;fileName=" + URLEncoder.encode("用户列表.xlsx", "utf-8"));
        ServletOutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        //关闭流
        outputStream.close();
        workbook.close();
    }
}

简单的页面

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">  <!--引入 thymeleaf-->
<head>
    <meta charset="UTF-8">
    <title>导入excel的主页面</title>
</head>
<body>
<h3>选择Excel文件导入到数据库</h3>
<form th:action="@{/user/import}" method="post" enctype="multipart/form-data" class="form-inline">
        <input type="file" name="excelFile">
        <input type="submit"  value="导入数据">
</form>
<h3>显示导入数据列表</h3>
<table  border="1px">
    <tr>
        <th>编号</th>
        <th>姓名</th>
        <th>生日</th>
        <th>爱好</th>
        <th>身份证号</th>
        <th>家庭住址</th>
        <th>照片</th>
    </tr>
    <tr  th:each="user : ${list}">
        <td th:text="${user.id}"></td>
        <td th:text="${user.name}"><img src="" alt=""></td>
        <td th:text="${user.bir}"></td>
        <td th:text="${user.habby}">td>
        <td th:text="${user.num}"></td>
        <td th:text="${user.address}"></td>
        <td ><img th:src="${'/' + user.photo}" th:height="40px" alt="头像信息"></td>
    </tr>
</table>
<br/>
<a th:href="@{/user/export}">导出excel</a>
</body>
</html>