# tlgen-springboot-easyexcel

**Repository Path**: tlgen_1/tlgen-springboot-easyexcel

## Basic Information

- **Project Name**: tlgen-springboot-easyexcel
- **Description**: springboot整合easyexcel导出导入
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 5
- **Forks**: 1
- **Created**: 2020-11-15
- **Last Updated**: 2023-11-07

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# tlgen-springboot-easyexcel

springboot整合easyexcel导出导入


全部依赖

   
```
<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!-- easyexcel相关依赖 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>1.1.2-beta5</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.20</version>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

```


实体类


```
@Data
public class User extends BaseRowModel {

    @ExcelProperty(value = "ID", index = 0)
    private Long id;
    @ExcelProperty(value = "姓名", index = 1)
    private String name;
    @ExcelProperty(value = "年龄", index = 2)
    private String age;
}
```



数据库访问层


```
@Mapper
public interface UserMapper {

    @Select("select * from t_user")
    public List<User> getAllUsers();

    @Insert("insert into t_user(name, age) values (#{name}, #{age})")
    void addUser(User user);
}
```



业务逻辑层


```
@Service
public class UserService {

    @Autowired
    private UserMapper userMapper;

    /**
     * 导出excel
     * @param response
     * @throws IOException
     */
    public void excelExport(HttpServletResponse response) throws IOException {
        List<User> list = userMapper.getAllUsers();
        String fileName = "response";
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + new String( fileName.getBytes("gb2312"), "ISO8859-1" ) + ".xls");
        ServletOutputStream out = response.getOutputStream();
        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLS,true);
        Sheet sheet = new Sheet(1,0,User.class);
        //设置自适应宽度
        sheet.setAutoWidth(Boolean.TRUE);
        sheet.setSheetName("用户名单");
        writer.write(list,sheet);
        writer.finish();
        out.flush();
        response.getOutputStream().close();
        out.close();
    }

    /**
     * 导入excel
     * @param file
     * @throws IOException
     */
    public void excelImport(MultipartFile file) throws IOException {
        if(!file.getOriginalFilename().equals("response.xls") && !file.getOriginalFilename().equals("response.xlsx") ){
            return;
        }
        InputStream inputStream = new BufferedInputStream(file.getInputStream());
        //实例化实现了AnalysisEventListener接口的类
        ExcelListener excelListener = new ExcelListener(userMapper);
        ExcelReader reader = new ExcelReader(inputStream,null,excelListener);
        //读取信息
        reader.read(new Sheet(1,1,User.class));
    }
}

```


ExcelListener


```
public class ExcelListener extends AnalysisEventListener<User> {

    private List<User> datas = new ArrayList<>();
    private static final int BATCH_COUNT = 3000;
    private UserMapper userMapper;

    public ExcelListener(UserMapper userMapper){
        this.userMapper = userMapper;
    }

    @Override
    public void invoke(User user, AnalysisContext analysisContext) {
        //数据存储到datas，供批量处理，或后续自己业务逻辑处理。
        datas.add(user);
        //达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if(datas.size() >= BATCH_COUNT){
            saveData();
            // 存储完成清理datas
            datas.clear();
        }
    }

    private void saveData() {
        for(User user : datas){
            userMapper.addUser(user);
        }
    }

    public List<User> getDatas() {
        return datas;
    }

    public void setDatas(List<User> datas) {
        this.datas = datas;
    }

    /**
     * 所有数据解析完成了 都会来调用
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();//确保所有数据都能入库
    }
```

}


控制层


```
@RestController
public class UserController {

    @Autowired
    private UserService userService;

    /**
     * 导出excel
     * @param response
     * @throws IOException
     */
    @GetMapping("/user/excel")
    public void excelExport(HttpServletResponse response) throws IOException {
        userService.excelExport(response);
    }

    /**
     * 导入excel
     * @param file
     * @return
     * @throws IOException
     */
    @PostMapping("/user/excel")
    public String excelImport(@RequestParam("file") MultipartFile file) throws IOException {
        userService.excelImport(file);
        return "success";
    }
}

```



导出

![输入图片说明](https://images.gitee.com/uploads/images/2020/1115/211110_4ac2f9f1_5371587.png "屏幕截图.png")

导入

![输入图片说明](https://images.gitee.com/uploads/images/2020/1115/211129_9b822d50_5371587.png "屏幕截图.png")

#### 参与贡献

1.  Fork 本仓库
2.  新建 Feat_xxx 分支
3.  提交代码
4.  新建 Pull Request


#### 特技

1.  使用 Readme\_XXX.md 来支持不同的语言，例如 Readme\_en.md, Readme\_zh.md
2.  Gitee 官方博客 [blog.gitee.com](https://blog.gitee.com)
3.  你可以 [https://gitee.com/explore](https://gitee.com/explore) 这个地址来了解 Gitee 上的优秀开源项目
4.  [GVP](https://gitee.com/gvp) 全称是 Gitee 最有价值开源项目，是综合评定出的优秀开源项目
5.  Gitee 官方提供的使用手册 [https://gitee.com/help](https://gitee.com/help)
6.  Gitee 封面人物是一档用来展示 Gitee 会员风采的栏目 [https://gitee.com/gitee-stars/](https://gitee.com/gitee-stars/)