Alibaba-EasyExcel

导入pom

<!-- EasyExcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.1.6</version>
</dependency>
<!-- lombok 优雅编程 -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.10</version>
</dependency>
<!-- junit -->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
</dependency>

读操作

public static void main(String[] args) {
    //读取文件
    //创建ExcelReaderBuilder实例
    ExcelReaderBuilder readerBuilder = EasyExcel.read();
    //获取文件对象
    readerBuilder.file("F:\bottomCode\EasyExcel\用户数据表.xls");
    //指定sheet
    readerBuilder.sheet("用户数据表");
    //自动关闭输入流
    readerBuilder.autoCloseStream(true);
    //设置Excel文件格式
    readerBuilder.excelType(ExcelTypeEnum.XLS);
    //注册监听器进行数据的解析
    readerBuilder.registerReadListener(new AnalysisEventListener<Map<Integer, String>>() {
        //一行数据读取后回调函数
        @Override
        public void invoke(Map<Integer, String> integerStringMap, AnalysisContext context) {
            Set<Integer> keySet = integerStringMap.keySet();
            Iterator<Integer> iterator = keySet.iterator();
            while (iterator.hasNext()) {
                Integer key = iterator.next();
                System.out.print(key + ":" + integerStringMap.get(key) + ",");
            }
            System.out.println("");
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            //通知文件读取完毕
            System.out.println("文件读取完毕");
        }
    });
    //构建读取器
    ExcelReader reader = readerBuilder.build();
    //读取数据
    reader.readAll();
    //读取完毕
    reader.finish();
}

写操作

@Data
public class ExcelData {
    @ExcelProperty("ID")
    private String id;
    @ExcelProperty("用户名")
    private String name;
    @ExcelProperty("邮箱")
    private String email;
    @ExcelProperty("性别")
    private String gender;
    @ExcelProperty("积分")
    private Integer score;
    @ExcelProperty("IP")
    private String ip;
    @ExcelProperty("登入次数")
    private Integer count;
    @ExcelProperty("加入时间")
    private Date date;

}

public class Test01 {
    public static void main(String[] args) {
        List<ExcelData> excelData = pareData();
        //list写入Excel
        EasyExcel.write("F:\bottomCode\EasyExcel\用户数据表2.xls")
                .head(ExcelData.class)
                .excelType(ExcelTypeEnum.XLS)
                .sheet("用户数据表")
                .doWrite(excelData);
    }

    public static List<ExcelData> pareData() {
        List<ExcelData> list = new LinkedList<>();
        EasyExcel.read("F:\bottomCode\EasyExcel\用户数据表.xls")
                .head(ExcelData.class)
                .sheet()
                .registerReadListener(new AnalysisEventListener<ExcelData>() {
                    @Override
                    public void invoke(ExcelData data, AnalysisContext context) {
                        list.add(data);
                    }

                    @Override
                    public void doAfterAllAnalysed(AnalysisContext context) {
                        System.out.println("数据读取完毕");
                    }
                }).doRead();
        return list;
    }

}