EasyExcel 导入导出

旧城等待， 2024-04-06 13:41 16阅读 0赞

#### EasyExcel 导入导出 ####

*  一、引入pom
 *   *  1.引入库
     *  2.导入导出代码

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

## 一、引入pom ##

### 1.引入库 ###

代码如下（示例）：

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

`提示：easyexcel会自带依赖于4.12的poi，所以如果项目其它地方引入了其它版本的poi，就会导致easyExcel导出失败等一系列问题，主要原因是因为poi版本冲突导致的。`

<dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>3.0.5</version>
                
                <exclusions>
                    <exclusion>
                        <groupId>javax.servlet</groupId>
                        <artifactId>servlet-api</artifactId>
                    </exclusion>
                    <exclusion>
                        <groupId>org.apache.poi</groupId>
                        <artifactId>poi</artifactId>
                    </exclusion>
                    <exclusion>
                        <groupId>org.apache.poi</groupId>
                        <artifactId>poi-ooxml</artifactId>
                    </exclusion>
                    <exclusion>
                        <groupId>org.apache.poi</groupId>
                        <artifactId>poi-ooxml-schemas</artifactId>
                    </exclusion>
                </exclusions>
             </dependency>

### 2.导入导出代码 ###

代码如下（示例）：

@ApiOperation("EasyExcel导入")
        @PostMapping("import")
        public R importJobs(@RequestParam MultipartFile file, HttpServletRequest request) throws IOException {
        
            InputStream inputStream = file.getInputStream();
    
            long start = System.currentTimeMillis();
            EasyExcel.read(inputStream, User.class, new PageReadListener<User>(dataList -> {
        
                for (User user : dataList) {
        
                    System.out.println(user);
                }
            })).headRowNumber(1).sheet().doRead();
            System.out.println("总耗时:" + (System.currentTimeMillis() - start) / 1000 + " s");
    
    
            return R.ok();
        }
    
        @ApiOperation("EasyExcel导出")
        @PostMapping("export")
        public void export(HttpServletResponse response) {
        
    
            String fileName = "用户报名管理.xlsx";
            try {
        
    
                List<User> userList = new ArrayList<>();
                User user = new User();
                user.setUserName("lisi");
                user.setId("1");
                user.setAddress("xxx");
                userList.add(user);
    
                setResponseHeader(response, fileName);
                EasyExcel.write(response.getOutputStream(), User.class).sheet("用户表").doWrite(userList);
            } catch (IOException e) {
        
                e.printStackTrace();
            }
    
        }
    
        public static void setResponseHeader(HttpServletResponse response, String realFileName) {
        
            String percentEncodedFileName = URLEncodeUtil.encode(realFileName);
            StringBuilder contentDispositionValue = new StringBuilder();
            contentDispositionValue.append("attachment; filename=")
                    .append(percentEncodedFileName)
                    .append(";")
                    .append("filename*=")
                    .append("utf-8''")
                    .append(percentEncodedFileName);
            response.addHeader("Access-Control-Allow-Origin", "*");
            response.addHeader("Access-Control-Expose-Headers", "Content-Disposition,download-filename");
            response.setHeader("Content-disposition", contentDispositionValue.toString());
            response.setHeader("download-filename", percentEncodedFileName);
        }

实体类（示例）：

> ExcelProperty注解标注excel里的字段名称。

@Data
    @ToString
    public class User {
        
    
        @ExcelProperty("id")
        private String id;
    
        @ExcelProperty("用户名")
        private String userName;
    
        @ExcelProperty("地址")
        private String address;
    
    }

![在这里插入图片描述][cb46dd7ef7454331a02a4f68a259d6c9.jpeg_pic_center]

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

End

[cb46dd7ef7454331a02a4f68a259d6c9.jpeg_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/fb7013467fe34a83ba081eca3d188167.jpeg