Spring Boot整合EasyPoi

2023-10-16 14:50:22

微信搜索公众号：科技猫，分享编程，软件，科技。

easypoi功能如同名字easy,主打的功能就是容易,让一个没见接触过poi的人员就可以方便的写出Excel导出,Excel模板导出,Excel导入,Word模板导出,通过简单的注解和模板语言(熟悉的表达式语法),完成以前复杂的写法

引入依赖

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

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</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.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.1</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
    <version>4.2.0</version>
</dependency>

Spring Boot配置文件 application.properties

Spring Boot核心配置文件

#定义项目端口
server.port=8888
#定义项目的访问目录
server.servlet.context-path=/easypoi
#数据库驱动
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#数据库连接地址
spring.datasource.url=jdbc:mysql://localhost:3306/easy_poi?useUnicode=true&characterEncoding=utf-8
#数据库用户名
spring.datasource.username=root
#数据库用户密码
spring.datasource.password=123456
#映射文件的位置
mybatis-plus.mapper-locations=classpath:com/springboot/dao/*Dao.xml
#类型别名
mybatis-plus.type-aliases-package=com.springboot.entity

数据库 SQL

数据库文件，直接导入即可，MySQL用的5.7版本

-- MySQL dump 10.13  Distrib 5.7.29, for Win64 (x86_64)
--
-- Host: 127.0.0.1    Database: easy_poi
-- ------------------------------------------------------
-- Server version  5.7.29

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `user`
--

DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `username` varchar(255) DEFAULT NULL COMMENT '用户名',
  `email` varchar(255) DEFAULT NULL COMMENT '邮箱',
  `phone` varchar(255) DEFAULT NULL COMMENT '手机号码',
  `qq` varchar(255) DEFAULT NULL COMMENT 'qq号码',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `user`
--

LOCK TABLES `user` WRITE;
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` VALUES (1,'王明','123456@qq.com','123456','123456','2020-11-22 00:22:48','2020-11-22 00:22:48'),(2,'孙红','123456@qq.com','123456','123456','2020-11-22 00:22:48','2020-11-22 00:22:48'),(3,'张飞','123456@qq.com','123456','123456','2020-11-22 00:22:48','2020-11-22 00:22:48'),(4,'李四','123456@qq.com','123456','123456','2020-11-22 00:22:48','2020-11-22 00:22:48'),(5,'王明','123456@qq.com','123456','123456','2020-11-22 00:50:04','2020-11-22 00:50:04'),(6,'孙红','123456@qq.com','123456','123456','2020-11-22 00:50:04','2020-11-22 00:50:04'),(7,'张飞','123456@qq.com','123456','123456','2020-11-22 00:50:04','2020-11-22 00:50:04'),(8,'李四','123456@qq.com','123456','123456','2020-11-22 00:50:04','2020-11-22 00:50:04');
/*!40000 ALTER TABLE `user` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2020-11-22  0:50:16

Dao

interface

@Mapper
@Repository
public interface UserDao extends BaseMapper<User> {
    List<UserExportVO> select(@Param("startTime") Date startTime, @Param("endTime") Date endTime);

    void insertList(@Param("userExportVO") List<UserExportVO> userExportVOS);
}

mapper

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.springboot.dao.UserDao">
    <resultMap id="userVOMap" type="com.springboot.entity.export.UserExportVO">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="email" column="email"/>
        <result property="phone" column="phone"/>
        <result property="qq" column="qq"/>
        <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>
    </resultMap>
    <select id="select" resultMap="userVOMap">
        select id,username,email,phone,qq,create_time,update_time from user
        <where>
            <if test="startTime != null and endTime != null ">
                create_time between #{startTime} and #{endTime}
            </if>
        </where>
    </select>
    <insert id="insertList">
        insert into user(username,email,phone,qq) values
        <foreach collection="userExportVO" item="item" separator=",">
            (#{item.username},#{item.email},#{item.phone},#{item.qq})
        </foreach>
    </insert>
</mapper>

Service

interface

public interface UserService {
    void userExport(HttpServletResponse response, Date startTime, Date endTime);

    void userImport(MultipartFile file);
}

Impl

@Service
public class UserServiceImpl extends ServiceImpl<UserDao, User> implements UserService {

    @Autowired
    private UserDao userDao;

    @Override
    public void userExport(HttpServletResponse response, Date startTime, Date endTime) {
        List<UserExportVO> userExportVOS = userDao.select(startTime, endTime);
        String title = "用户信息表";
        if (startTime != null && endTime != null) {
            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
            String startTimeStr = format.format(startTime);
            String endTimeStr = format.format(endTime);
            title = startTimeStr + "至" + endTimeStr + "用户信息表";
        }
        Map<String, Object> oneSheet = ExcelUtil.createOneSheet(title, title, UserExportVO.class, userExportVOS);
        List<Map<String, Object>> list = Lists.newArrayList();
        list.add(oneSheet);
        Workbook workbook = ExcelUtil.mutiSheet(list);
        //通过输出流输出文件
        OutputStream os = null;
        try {
            response.setContentType("application/msexcel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename="
                    + new String(title.getBytes(), StandardCharsets.ISO_8859_1) + ".xlsx");
            response.setCharacterEncoding("UTF-8");
            os = response.getOutputStream();
            workbook.write(os);
        } catch (IOException e) {
            // 打印异常
            log.error("导出异常：", e);
        } finally {
            // 关闭资源
            if (os != null) {
                try {
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public void userImport(MultipartFile file) {
        List<UserExportVO> userExportVOS = Lists.newArrayList();
        try {
            ImportParams importParams = new ImportParams();
            importParams.setHeadRows(2);
//            importParams.setTitleRows(0);
            userExportVOS = ExcelImportUtil.importExcel(file.getInputStream(), UserExportVO.class, importParams);
        } catch (Exception e) {
            e.printStackTrace();
        }
        userDao.insertList(userExportVOS);
    }
}

Controller

@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserService userService;


    @GetMapping("/exportExcel")
    public void exportExcel(HttpServletResponse response, @RequestParam(value = "startTime", required = false) Date startTime,
                            @RequestParam(value = "endTime", required = false) Date endTime) {
        userService.userExport(response, startTime, endTime);
    }

    @GetMapping("/importExcel")
    public void importExcel(@RequestParam("file") MultipartFile file) {
        userService.userImport(file);
    }
}

实体类

User

@Data
public class User implements Serializable {
    private static final long serialVersionUID = 1348665906085238496L;
    private Long id;
    private String username;
    private String email;
    private String phone;
    private String qq;
    private Date createTime;
    private Date updateTime;
}

UserExportVO

用于导出时映射数据的实体类

@Data
@ExcelTarget("userExportVO")
public class UserExportVO {
    @Excel(name = "编号")
    private Long id;
    @Excel(name = "用户名", isImportField = "true")
    private String username;
    @Excel(name = "邮箱", isImportField = "true")
    private String email;
    @Excel(name = "电话号码", isImportField = "true")
    private String phone;
    @Excel(name = "qq号码", isImportField = "true")
    private String qq;
    @Excel(name = "创建时间", databaseFormat = "yyyy-MM-dd HH:mm:ss", format = "yyyy-MM-dd HH:mm:ss", width = 40)
    private Date createTime;
    @Excel(name = "更新时间", databaseFormat = "yyyy-MM-dd HH:mm:ss", format = "yyyy-MM-dd HH:mm:ss", width = 40)
    private Date updateTime;
}

工具类 ExcelUtil

public class ExcelUtil {
    /**
     * 创建workbook,
     * 通过maplist填充Excel内容
     * 返回workbook
     * <p>
     * 进一步使用可以写入流,e.g.
     * FileOutputStream fos = new FileOutputStream(file);
     * workbook.write(fos);
     */
    public static Workbook mutiSheet(List<Map<String, Object>> mapListList) {
        return ExcelExportUtil.exportExcel(mapListList, ExcelType.XSSF);
    }

    public static Map<String, Object> createOneSheet(ExportParams exportParams, Class<?> clazz, List<?> data) {
        Map<String, Object> map = new HashMap<>();
        map.put("title", exportParams);
        map.put("entity", clazz);
        map.put("data", data);
        return map;
    }

    /**
     * 创建一个表格并填充内容
     * 返回map供工作簿使用
     */
    public static Map<String, Object> createOneSheet(String sheetName, String title, Class<?> clazz, List<?> data) {
        ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
        return createOneSheet(exportParams, clazz, data);
    }
}

测试

数据库

导出的excel

本文源码地址：https://github.com/jonssonyan/spring-demo/tree/master/springboot-easypoi

码农公寓

引入依赖

Spring Boot配置文件 application.properties

数据库 SQL

Dao

interface

mapper

Service

interface

Impl

Controller

实体类

User

UserExportVO

工具类 ExcelUtil

测试

数据库

导出的excel

相关文章