easyexcel的使用

最新推荐文章于 2024-03-08 16:12:20 发布

脑阔不够用

最新推荐文章于 2024-03-08 16:12:20 发布

阅读量133

点赞数

文章标签： excel java

本文链接：https://blog.csdn.net/weixin_49756100/article/details/130423387

版权

。所需依赖

<dependencies>
    <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.1.1</version>
    </dependency>
      <!--xls  <poi.version>3.17</poi.version>-->
        <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>
</dependencies>

1、写入excel

①添加写入数据的对应实体类

②在实体类的属性上添加注解@ExcelProperty表示excel的表头

@Data
@NoArgsConstructor
@AllArgsConstructor
public class UserDemo {

    @ExcelProperty("用户姓名")
    private String userName;

    @ExcelProperty("用户年龄")
    private Integer age;

    @ExcelProperty("用户性别")
    private String sex;

    @ExcelProperty("用户邮箱")
    private String email;
}

③测试

EasyExcel.write(path,UserDemo.class).sheet(“用户列表”).doWrite(list);

path:磁盘保存路径

UserDemo.class：操作的实体类

sheet：excel底部的sheet名称

list：需要写入的实体类list集合

public class ExcelWrite {
    private static List<UserDemo> list = null;
    static {
        list = new ArrayList<>();
        list.add(new UserDemo("夏紫瞳",18,"女","xzt@qq.com"));
        list.add(new UserDemo("李红梅",19,"女","lhm@qq.com"));
        list.add(new UserDemo("杨雪梨",20,"女","yxl@qq.com"));
    }
    public static void main(String[] args) {
        String path = "F:\\workspace\\workspace-project\\zhxy\\excelwrite\\user.xlsx";
        EasyExcel.write(path,UserDemo.class).sheet("用户列表").doWrite(list);
    }
}

在这里插入图片描述

2、读excel

①创建读的实体类

②在实体类属性上添加注解属性index做标记对应列@ExcelProperty(value = "用户姓名",index = 0)

@Data
@NoArgsConstructor
@AllArgsConstructor
public class UserDemo {

    @ExcelProperty(value = "用户姓名",index = 0)
    private String userName;

    @ExcelProperty(value = "用户年龄",index = 1)
    private Integer age;

    @ExcelProperty(value = "用户性别",index = 2)
    private String sex;

    @ExcelProperty(value = "用户邮箱",index = 3)
    private String email;
}

③编写读excel的监听器

1、继承AnalysisEventListener类

2、重写方法

3、再自己重写一个读文件头方法public void invokeHead(Map<Integer, CellData> headMap, AnalysisContext context)

@Slf4j
public class ReadExcelListener extends AnalysisEventListener<UserDemo> {
    /**
     * 读取内容，一行一行读取
     * @param userDemo
     * @param analysisContext
     */
    @Override
    public void invoke(UserDemo userDemo, AnalysisContext analysisContext) {
        log.info("user:{}",userDemo);
    }

    /**
     * 读取表头
     * @param headMap
     * @param context
     */
    @Override
    public void invokeHead(Map<Integer, CellData> headMap, AnalysisContext context) {
        log.info("excelHead:{}",headMap);
    }

    /**
     * 读取后的操作
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

④测试

1、path读的文件路径

2、UserDemo.class：读文件相应的实体类

3、new ReadExcelListener()：读取execl的监听器

public class ReadExcel {
    public static void main(String[] args) {
        //读取路径
        String path = "F:\\workspace\\workspace-project\\zhxy\\excelwrite\\user.xlsx";
        EasyExcel.read(path,UserDemo.class,new ReadExcelListener()).sheet().doRead();
    }
}