easyExcel的使用方法

1、导入依赖

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

使用方法

package com.baitang;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.support.ExcelTypeEnum;

public class Test {
    public static void main(String[] args) {
        //读取文件,创建ExcelReaderBuilder实例
        ExcelReaderBuilder readerBuilder = EasyExcel.read();
        //获取文件对象
        readerBuilder.file("D:\\JavaProjects\\用户数据表.xlsx");
        //指定sheet
        readerBuilder.sheet("用户数据表");
        //自动关闭输入流
        readerBuilder.autoCloseStream(true);
        //设置excel文件格式
        readerBuilder.excelType(ExcelTypeEnum.XLSX);
        //设置监听器进行数据解析
        readerBuilder.registerReadListener(new AnalysisEventListener() {
            public void invoke(Object o, AnalysisContext analysisContext) {
                //一行数据读取完成后的回调
                System.out.println(o);
            }

            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                //通知文件读取完毕
                System.out.println("数据读取完毕");

            }
        });
        //构建一个读取器
        ExcelReader reader=readerBuilder.build();
        //读取数据
        reader.readAll();
        //读取完毕
        reader.finish();
    }
}

代码简化

package com.baitang;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;

import java.util.*;

public class Test2 {
    public static void main(String[] args) {
         final List<Map<Integer,String>> list=new LinkedList<>();
        EasyExcel.read("D:\\JavaProjects\\用户数据表.xlsx")
                .sheet().registerReadListener(new AnalysisEventListener<Map<Integer,String>>() {

            @Override
            public void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) {
                list.add(integerStringMap);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                System.out.println("数据读取完毕");
            }
        }).doRead();
        for (Map<Integer,String> integerStringMap:list){
            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("");
        }
    }
}

映射成对象，需要创建实体类,通过@ExcelProperty注解完成实体类成员变量和excel字段的映射

package com.baitang.entity;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

import java.util.Date;

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

package com.baitang;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.baitang.entity.ExcelData;

import java.util.*;

public class Test2 {
    public static void main(String[] args) {
         final List<ExcelData> list=new LinkedList<>();
        EasyExcel.read("D:\\JavaProjects\\用户数据表.xlsx")
                .head(ExcelData.class)
                .sheet().registerReadListener(new AnalysisEventListener<ExcelData>() {

            @Override
            public void invoke(ExcelData excelData, AnalysisContext analysisContext) {
                list.add(excelData);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                System.out.println("数据读取完毕");
            }
        }).doRead();
        for (ExcelData excelData : list) {
            System.out.println(excelData);
        }
    }
}

2、写数据

package com.baitang;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.baitang.entity.ExcelData;

import java.util.LinkedList;
import java.util.List;

public class Test3 {
    public static void main(String[] args) {
        List<ExcelData> list=parseData();
        //list写入excel文件
        EasyExcel.write("D:\\JavaProjects\\JavaProjects\\easyexcel\\src\\main\\resources\\excel\\用户数据表_副本.xlsx")
                .head(ExcelData.class)
                .excelType(ExcelTypeEnum.XLSX)
                .sheet("用户数据表")
                .doWrite(list);

    }
    public static List<ExcelData> parseData(){
        final List<ExcelData> list=new LinkedList<>();
        EasyExcel.read("D:\\JavaProjects\\JavaProjects\\easyexcel\\src\\main\\resources\\excel\\用户数据表.xlsx")
                .head(ExcelData.class)
                .sheet().registerReadListener(new AnalysisEventListener<ExcelData>() {

            @Override
            public void invoke(ExcelData excelData, AnalysisContext analysisContext) {
                list.add(excelData);
            }

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