美文网首页框架优秀软件
SpringBoot整合EasyExcel进行报表导入导出

SpringBoot整合EasyExcel进行报表导入导出

作者: 半晴Miko | 来源:发表于2021-11-19 17:17 被阅读0次

    引入

    为什么使用EasyExcel?

    Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存,poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题,但POI还是有一些缺陷,比如07版Excel解压缩以及解压后存储都是在内存中完成的,内存消耗依然很大。

    EasyExcel重写了poi对07版Excel的解析,一个3M的excel用POI sax解析依然需要100M左右内存,改用EasyExcel可以降低到几M,并且再大的excel也不会出现内存溢出;03版依赖POI的sax模式,在上层做了模型转换的封装,让使用者更加简单方便。

    官网GitHub地址:

    https://github.com/alibaba/easyexcel

    官方文档地址:

    https://www.yuque.com/easyexcel/doc/easyexcel

    实例

    创建初始数据

    SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for user
    -- ----------------------------
    DROP TABLE IF EXISTS `user`;
    CREATE TABLE `user`  (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `user_id` bigint(20) NULL DEFAULT NULL,
      `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `create_time` date NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of user
    -- ----------------------------
    INSERT INTO `user` VALUES (1, 1, 'banq', '13588888888', '2021-11-19');
    
    SET FOREIGN_KEY_CHECKS = 1;
    

    创建SpringBoot项目

    导入EasyExcel依赖

    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.2.10</version>
    </dependency>
    

    User

    @Data
    public class User {
        @ExcelIgnore  //生成报表时忽略
        private Long id;
    
        @ExcelProperty(value = "用户ID",index = 0)  // 定义表头名称和位置,0代表第一列
        private Long userId;
    
        @ExcelProperty(value = "用户名称",index = 1)
        private String username;
    
        @ExcelProperty(value = "电话号码",index = 2)
        private String phone;
    
        @ExcelProperty(value = "创建日期",index = 3)
        @DateTimeFormat("yyyy年MM月dd日")
        private Date createTime;
    }
    

    监听器

    @Slf4j
    public class UserDataListener extends AnalysisEventListener<User> {
    
        private UserService userService;
    
        public UserDataListener(UserService userService) {
            this.userService = userService;
        }
    
        /**
         * 每隔5条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收
         */
        private static final int BATCH_COUNT = 5;
        List<User> list = new ArrayList<User>();
    
        @Override
        public void invoke(User data, AnalysisContext context) {
            log.info("解析到一条数据:{}", JSON.toJSONString(data));
            list.add(data);
            if (list.size() >= BATCH_COUNT) {
                saveData();
                list.clear();
            }
        }
    
        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            saveData();
            log.info("所有数据解析完成!");
        }
    
        /**
         * 加上存储数据库
         */
        private void saveData() {
            log.info("{}条数据,开始存储数据库!", list.size());
            if (!CollectionUtils.isEmpty(list)) {
                userService.saveBatch(list);
            }
            log.info("存储数据库成功!");
        }
    }
    

    Dao层

    @Mapper
    @Repository
    public interface UserMapper {
        void batchInsert(List<User> list);
        List<User> queryAll();
    }
    
    <insert id="batchInsert">
        insert into user (user_id,username,phone,create_time) values
        <foreach collection="list" item="user" separator=",">
            (#{user.userId},#{user.username},#{user.phone},#{user.createTime})
        </foreach>
    </insert>
    <select id="queryAll" resultType="ink.banq.demo.entity.User">
        select id,user_id,username,phone,create_time from user
    </select>
    

    Service层

    public interface UserService {
        void saveBatch(List<User> list);
        List<User> selectAll();
    }
    
    @Service
    public class UserServiceImpl implements UserService {
        @Autowired
        private UserMapper userMapper;
    
        @Override
        public void saveBatch(List<User> list) {
            userMapper.batchInsert(list);
        }
    
        @Override
        public List<User> selectAll() {
            return userMapper.queryAll();
        }
    }
    

    Controller层

    @RestController
    public class OperateExcelController {
        @Autowired
        private UserService userService;
    
        /**
         * 上传文件
         * @param file
         * @return
         * @throws IOException
         */
        @PostMapping("upload")
        @ResponseBody
        public String upload(MultipartFile file) throws IOException {
            EasyExcel.read(file.getInputStream(), User.class, new UserDataListener(userService)).sheet().doRead();
            return "success";
        }
    
        /**
         * 导出文件
         * @param response
         * @throws IOException
         */
        @GetMapping("download")
        public void download(HttpServletResponse response) throws IOException {
            // 设置上下文类型
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            // 设置编码
            response.setCharacterEncoding("utf-8");
            // 防止中文乱码
            String fileName = URLEncoder.encode("文件名称", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream()).sheet("模板").doWrite(userService.selectAll());
        }
    
    }
    

    测试

    (1)测试导入功能,使用Postman测试,如下图所示:

    1.jpg

    (2)测试导出功能,浏览器访问:http://localhost:8080/download

    2.jpg

    相关文章

      网友评论

        本文标题:SpringBoot整合EasyExcel进行报表导入导出

        本文链接:https://www.haomeiwen.com/subject/uwhetrtx.html