EasyExcel实现文件的导入导出功能

最简单的实现excel导入导出

         首先，关于easyexcl的优点以及背景什么的在此都不再多说，使用easyexcel导入导出excel分为以下几步。

---

1、导入easyexcel依赖，

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

涉及到数据库还需要有mybatis和mysql的依赖，关于实体类操作的lombok依赖，需要下载lombok插件，需要做什么我们就添加怎样的依赖。

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus</artifactId>
    <version>3.5.2</version>
</dependency>

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <scope>runtime</scope>
</dependency>

2、实体类引入注解

@Data
@ToString
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
public class Demo {
        private String id;
        @ExcelProperty(index = 0)
        private String name;
        @ExcelProperty(index = 1)
        private Integer age;
        @ExcelProperty(index = 2)
        private Integer num;

3、写好mapper

@Repository
public interface DemoMapper extends BaseMapper<Demo> {
    void insertDemo(@Param("datalist")List<Demo> demoList);

4、写好service

@Service
@Transactional
public class DemoService {
    @Autowired
    private DemoMapper demoMapper;

    public void add(List<Demo> datalist){
        demoMapper.insertDemo(datalist);
    }

}

5、实现ReadListener接口

public class ExcelReadListener implements ReadListener<Demo> {
    List<Demo> list = new ArrayList<>();

    private DemoService demoService;
    public ExcelReadListener(DemoService demoService){
        this.demoService=demoService;
    }

    @Override
    public void invoke(Demo demo, AnalysisContext analysisContext) {
        System.out.println("读取到： "+demo);
        // 假设读取到满足条件的数据后就结束读取
        /*这加条件判断什么时候结束
        if () {
            // 结束读取
            throw new RuntimeException("ReadDataEnd");
        }*/
        list.add(demo);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("读取完毕。。。");
        //入库
        demoService.add(list);
        System.out.println("向数据库中传送数据完毕。");
    }
}

6、写好controller

@RestController
@RequestMapping
public class DemoController {
    @Autowired
    private DemoService demoService;

    /**
     * 向数据库导入数据
     *
     * @throws FileNotFoundException
     */
    @PostMapping("/upload")
    public void input() throws FileNotFoundException{
        File file = new File("D:\\你的excel路径.xlsx");
        InputStream is = new FileInputStream(file);

        //读取监听器拿过来
        ExcelReadListener listener = new ExcelReadListener(demoService);
        EasyExcel.read(is,
                Demo.class,
                listener).sheet(0) //从零开始
                .headRowNumber(1)
                .doRead();
    }

    /**
     * 导出excel
     */
    public void exportExcel(){
            String filename = "D：\\路径.xlsx";
            // 创建ExcelWriter对象
            ExcelWriter excelWriter = EasyExcel.write(filename, User.class).build();
            // 创建Sheet对象
            WriteSheet writeSheet = EasyExcel.writerSheet("sheet1").build();
            // 向Excel中写入数据
            excelWriter.write(getUserData(), writeSheet);
            // 关闭流
            excelWriter.finish();
    }
}

7、在postman中运行

        地址：http://localhost:8080/upload

8、查看数据库

成功向数据库传入了数据。

导出没有写controller大家可以自己写一个自己测一下，导出直接用writer方法就可以了，有什么疑问欢迎给我留言，有什么难题，项目遇到bug，欢迎与我联系。