spring boot 整合EasyPoi导入导出，下载模版功能

我不是女神ヾ 2024-03-16 22:52 75阅读 0赞

### 引入依赖 ###

<parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.2.5.RELEASE</version>
            <relativePath/> 
        </parent>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </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>
                <version>1.1.13</version>
            </dependency>
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.6</version>
            </dependency>
    
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-boot-starter</artifactId>
                <version>3.4.0</version>
            </dependency>
            
            <dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-spring-boot-starter</artifactId>
                <version>4.4.0</version>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-data-jpa</artifactId>
            </dependency>
    
            <dependency>
                <groupId>io.springfox</groupId>
                <artifactId>springfox-swagger2</artifactId>
                <version>2.9.2</version>
            </dependency>
            <dependency>
                <groupId>io.springfox</groupId>
                <artifactId>springfox-swagger-ui</artifactId>
                <version>2.9.2</version>
            </dependency>
    
            <dependency>
                <groupId>commons-io</groupId>
                <artifactId>commons-io</artifactId>
                <version>2.11.0</version>
            </dependency>
    
            <dependency>
                <groupId>cn.hutool</groupId>
                <artifactId>hutool-all</artifactId>
                <version>5.7.22</version>
            </dependency>
    
        </dependencies>

### 连接数据库以及其他配置 ###

server:
      port: 9000
      servlet:
        context-path: /
    
    spring:
      datasource:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/my?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8
        username: root
        password: zzybzb
    
    mybatis:
      mapper-locations: classpath:/mappers/*.xml
      configuration:
        log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    
    logging:
      level:
        root: info
        com:
          pzz:
            dao: debug
    
    
    # 开启swagger
    swagger:
      enable: true

### swagger的配置（这里使用swagger测试接口） ###

@Configuration
    @ConditionalOnProperty(prefix = "swagger",value = {
        "enable"},havingValue = "true")
    @EnableSwagger2  //开启swagger注解支持
    public class SwaggerConfiguration {
        
    
    	@Bean
    	public Docket buildDocket() {
        
    		/**
    		 * Docket:摘要对象，通过对象配置描述文件的信息。
    		 * apilnfo:设置描述文件中info。参数类型Apilnfoe'
    		 * select():返回ApiSelectorBuilder对象，通过对象调用build()可以创建Docket对象
    		 * ApilnfoBuilder: Apilnfo构建器。
    		 */
    		return new Docket(DocumentationType.SWAGGER_2)
    				.apiInfo(buildApiInfo())
    				.select()
    				// 要扫描的API(Controller)基础包
    				.apis(RequestHandlerSelectors.basePackage("com.pzz"))
    				.paths(PathSelectors.any())
    				.build();
    	}
    
    	private ApiInfo buildApiInfo() {
        
    		Contact contact = new Contact("pzz","","");
    		return new ApiInfoBuilder()
    				//文档标题
    				.title("导入导出API文档")
    				//文档描述  
    				.description("导入导出api")
    				.contact(contact)
    				//文档版本
    				.version("1.0.0").build();
    	}
    }

### 实体类添加@Excel注解 ###

package com.pzz.entity;
    
    import cn.afterturn.easypoi.excel.annotation.Excel;
    import com.baomidou.mybatisplus.annotation.IdType;
    import com.baomidou.mybatisplus.annotation.TableId;
    import lombok.Data;
    
    import java.util.Date;
    import java.io.Serializable;
    
    /**
     * 系统用户表(TbUser)实体类
     *
     * @author makejava
     * @since 2023-06-21 16:49:12
     */
    @Data
    public class TbUser implements Serializable {
        
        private static final long serialVersionUID = 872169419599604137L;
        /**
         * 主键
         */
        @TableId(value = "id", type = IdType.AUTO)
        private Integer id;
        /**
         * 用户名
         */
        @Excel(name = "姓名",width = 15)
        private String name;
        /**
         * 手机号
         */
        @Excel(name = "手机号",width = 20)
        private String phone;
        /**
         * 邮箱
         */
        @Excel(name = "邮箱",width = 20)
        private String email;
        /**
         * 专业
         */
        @Excel(name = "专业",width = 15)
        private String profession;
        /**
         * 年龄
         */
        @Excel(name = "年龄",width = 15)
        private String age;
        /**
         * 性别 , 1: 男, 2: 女
         */
        @Excel(name = "性别",width = 15,replace = {
        "男_1","女_2"})
        private String gender;
        /**
         * 状态
         */
        @Excel(name = "状态",width = 15)
        private String status;
        /**
         * 创建时间
         */
        @Excel(name = "创建时间",width = 25,format = "yyyy-MM-dd")
        private Date createtime;
    
    }

*  name：Excel中的列名；
 *  width：指定列的宽度；
 *  needMerge：是否需要纵向合并单元格；
 *  format：当属性为时间类型时，设置时间的导出导出格式；
 *  desensitizationRule：数据脱敏处理，3\_4表示只显示字符串的前3位和后4位，其他为\*号；
 *  replace：对属性进行替换；
 *  suffix：对数据添加后缀。

### controller接口 ###

省略service和mapper层的代码，导入保存，导出查询，很简单的方法。

#### 下载模版 ####

@Autowired
        private ResourceLoader resourceLoader;
    
        /**
         * 下载模版
         * @param response
         * @throws IOException
         */
        @ApiOperation("下载模版方式一")
        @GetMapping("/downloadTemplate")
        public void downloadTemplate(HttpServletResponse response) throws IOException {
        
            try {
        
                byte[] bytes = IOUtils.toByteArray(resourceLoader.getResource("classpath:templates/用户导入模版.xlsx").getInputStream());
                Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(bytes));
                ByteArrayOutputStream bo = new ByteArrayOutputStream();
                workbook.write(bo);
                bo.flush();
                byte[] array = bo.toByteArray();
                response.setHeader("Content-Type", "application/vnd.ms-excel");
                response.setHeader("Content-Disposition", "attachment;filename=" + new String("用户导入模版.xlsx".getBytes(StandardCharsets.UTF_8), "ISO-8859-1"));
                response.setCharacterEncoding("UTF-8");
                response.getOutputStream().write(array);
                response.setContentLength(array.length);
            } catch (Exception e) {
        
                //log.error("downloadTemplate --> download excel template failed, errorMsg: {}", e.getMessage(), e);
                throw new RuntimeException("获取模板失败");
            } finally {
        
                if (response.getOutputStream() != null) {
        
                    response.getOutputStream().flush();
                }
            }
        }
    
        /**
         * 下载模版
         * @param response
         */
        @ApiOperation("下载模版方式二")
        @GetMapping("getTemplate")
        public void getTemplate(HttpServletResponse response) {
        
            List<TbUser> heroList = new ArrayList<>();
            Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户信息列表", "用户导入"), TbUser.class, heroList);
            downLoadExcel("TBUSER.xlsx", response, workbook);
        }
    
        private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        
            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());
            } catch (IOException e) {
        
                throw new RuntimeException(e.getMessage());
            }
        }

#### 导出 ####

/**
         * 导出功能
         * @param map
         * @param request
         * @param response
         */
        @ApiOperation("用户导出")
        @RequestMapping(value = "/exportMemberList", method = RequestMethod.GET)
        public void exportMemberList(ModelMap map,
                                     HttpServletRequest request,
                                     HttpServletResponse response) {
        
            List<TbUser> tbUserList = tbUserService.getTbUserList();
            ExportParams params = new ExportParams("用户列表", "用户列表", ExcelType.XSSF);
            map.put(NormalExcelConstants.DATA_LIST, tbUserList);
            map.put(NormalExcelConstants.CLASS, TbUser.class);
            map.put(NormalExcelConstants.PARAMS, params);
            map.put(NormalExcelConstants.FILE_NAME, "memberList");
            PoiBaseView.render(map, request, response, NormalExcelConstants.EASYPOI_EXCEL_VIEW);
        }

#### 导入 ####

/**
         * 导入
         * @param file
         * @return
         */
        @ApiOperation("用户导入")
        @RequestMapping(value = "/importMemberList", method = RequestMethod.POST)
        public String importMemberList(@RequestPart("file") MultipartFile file) {
        
            ImportParams params = new ImportParams();
            //params.setTitleRows(1);
            //去掉第一行的表头，从第二行开始
            params.setHeadRows(1);
            try {
        
                List<TbUser> list = ExcelImportUtil.importExcel(
                        file.getInputStream(),
                        TbUser.class, params);
                for (TbUser tbUser : list) {
        
                    tbUserService.insert(tbUser);
                }
                System.out.println(list);
                return "success";
            } catch (Exception e) {
        
                e.printStackTrace();
                return "导入失败！";
            }
        }

### 测试 ###

*  导出  
    ![在这里插入图片描述][b0d3269af9e84af78ee9782c52494978.png]  
    ![在这里插入图片描述][c2057683fec7499db2fdcb6c3ef4ee88.png]
 *  导入  
    ![在这里插入图片描述][385582a58dbc4d31bc53c6bb070d14ab.png]  
    ![在这里插入图片描述][34255ce06e664bb7aabbd153b8a99969.png]
 *  下载模版

![在这里插入图片描述][fade621fcb464d84b8abf53fdc8bbd62.png]

结束！！！！！！！  
hy:16

--------------------

我们必须把学习变成一种习惯.--《终身成长》

[b0d3269af9e84af78ee9782c52494978.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/e8238dfe28784f51a17ee14076981d88.png
[c2057683fec7499db2fdcb6c3ef4ee88.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/1d4d03d33bcd444f803e4e3280c1ec3a.png
[385582a58dbc4d31bc53c6bb070d14ab.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/f4b9c1c32bda487b8d68413dc33b0ad7.png
[34255ce06e664bb7aabbd153b8a99969.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/15c1f310544b4f319778bd4668c77608.png
[fade621fcb464d84b8abf53fdc8bbd62.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/0c2389fc96bb406781488bd79b28b418.png