easyexcel 使用示例

************************

示例

head 类

Person

@Data
public class Person {

    private Integer id;
    private String name;
    private Integer age;
}

Student

@Data
public class Student {

    @ExcelProperty({"student","id"})
    private Integer id;

    @ExcelProperty({"student","姓名"})
    private String name;

    @ExcelProperty({"student","年龄"})
    private Integer age;
}

Book

@Data
public class Book {

    private Integer id;

    @ExcelProperty("名称")
    private String name;

    @ExcelProperty(value = "价格",index = 3)
    private Double price;
}

说明：price写入的列索引为3（第四列，index从0开始计算）

监听器

CustomListener：创建对象读

public class CustomListener extends AnalysisEventListener<Person> {

    private final Logger logger= LoggerFactory.getLogger(CustomListener.class.getName());

    private final Integer size=5;
    private final List<Person> list=new ArrayList<>();

    @Override
    public void invoke(Person person, AnalysisContext analysisContext) {
        if (list.size()<=5){
            list.add(person);
        }else {
            printData();
            list.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        printData();

        logger.info("数据解析完成");
    }

    @Override
    public void invokeHead(Map<Integer, CellData> headMap, AnalysisContext context) {
                          //读取头数据

        System.out.println("invokeHead");
        headMap.forEach((key,value) -> {
            System.out.println(key +" ==> "+value);
        });
    }

    private void printData(){
        list.forEach(System.out::println);
    }
}

CustomListener2：不创建对象读，使用Map<Integer,String>接受数据，key为列的索引，从0开始

public class CustomListener2 extends AnalysisEventListener<Map<Integer,String>> {

    private final Logger logger= LoggerFactory.getLogger(CustomListener2.class.getName());

    private final Integer size=5;
    private final List<Map<Integer,String>> list=new ArrayList<>();

    @Override
    public void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) {
        if (list.size()<=5){
            list.add(integerStringMap);
        }else {
            printData();
            list.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        printData();

        logger.info("数据解析完成");
    }

    private void printData(){
        list.forEach(map -> {
            for (Map.Entry<Integer,String> entry:map.entrySet()){
                System.out.print(entry.getValue()+"  ");
            }

            System.out.println();
        });
    }
}

测试类

Test

public class Test {

    private static final String readPath="e:"+ File.separator+"java"+File.separator+"easyexcel"+File.separator+"read_test.xlsx";

    private static final String writePath="e:"+ File.separator+"java"+File.separator+"easyexcel"+File.separator+"write_test.xlsx";
    private static final String writePath2="e:"+ File.separator+"java"+File.separator+"easyexcel"+File.separator+"write_test2.xlsx";
    private static final String writePath3="e:"+ File.separator+"java"+File.separator+"easyexcel"+File.separator+"write_test3.xlsx";
    private static final String writePath4="e:"+ File.separator+"java"+File.separator+"easyexcel"+File.separator+"write_test4.xlsx";

    public static void read(){
        EasyExcel.read(readPath, Person.class, new CustomListener()).sheet().doRead();
    }

    public static void read2(){
        EasyExcel.read(readPath, Person.class, new CustomListener()).sheet(1).doRead();
    }

    public static void read3(){
        ExcelReader excelReader=null;

        try {
            excelReader=EasyExcel.read(readPath,Person.class,new CustomListener()).build();
            ReadSheet readSheet=EasyExcel.readSheet(0).build();
            ReadSheet readSheet2=EasyExcel.readSheet(1).build();

            excelReader.read(readSheet,readSheet2);
        }finally {
            if (excelReader!=null){
                excelReader.finish();
            }
        }
    }

    public static void read4(){
        EasyExcel.read(readPath,new CustomListener2()).sheet().doRead();
    }

    public static void write(){
        EasyExcel.write(writePath,Person.class).sheet().doWrite(data());
    }

    public static void write2(){
        ExcelWriter excelWriter=null;

        try {
            excelWriter=EasyExcel.write(writePath2).build();

            WriteSheet sheet=EasyExcel.writerSheet(0,"sheet").head(Person.class).build();
            WriteSheet sheet2=EasyExcel.writerSheet(1,"sheet2").head(Student.class).build();

            excelWriter.write(data(),sheet);
            excelWriter.write(data2(),sheet2);
        }finally {
            if (excelWriter!=null){
                excelWriter.finish();
            }
        }
    }

    public static void write3(){
        EasyExcel.write(writePath3).sheet(2,"book").head(Book.class).doWrite(data3());
    }

    public static void write4(){
        EasyExcel.write(writePath4).sheet().head(head()).doWrite(data4());
    }

    private static List<Person> data(){
        List<Person> list=new ArrayList<>();

        for (int i=0;i<=5;i++){
            Person person=new Person();
            person.setId(i);
            person.setName("瓜田李下"+i);
            person.setAge(20+i);

            list.add(person);
        }

        return list;
    }

    private static List<Student> data2(){
        List<Student>list=new ArrayList<>();

        for (int i=0;i<=5;i++){
            Student student=new Student();
            student.setId(i);
            student.setName("海贼王"+i);
            student.setAge(20+i);

            list.add(student);
        }

        return list;
    }

    private static List<Book> data3(){
        List<Book> list=new ArrayList<>();

        for (int i=0;i<=5;i++){
            Book book=new Book();

            book.setId(i);
            book.setName("火影忍者"+i);
            book.setPrice((double) (20 + i));

            list.add(book);
        }

        return list;
    }

    private static List<List<String>> head(){
        List<List<String>> head=new ArrayList<>();

        List<String> list=new ArrayList<>();
        list.add("id");

        List<String> list2=new ArrayList<>();
        list2.add("name");

        List<String> list3=new ArrayList<>();
        list3.add("age");

        head.add(list);
        head.add(list2);
        head.add(list3);

        return head;
    }

    private static List<List<Object>> data4(){
        List<List<Object>> lists=new ArrayList<>();

        for (int i=0;i<=5;i++){
            List<Object> list=new ArrayList<>();

            list.add(i);
            list.add("火影忍者"+i);
            list.add(20+i);

            lists.add(list);
        }

        return lists;
    }

    public static void main(String[] args){
        write4();
    }
}

************************

测试数据

read_test.sheet

read_test.sheet2

************************

使用测试

read

invokeHead
0 ==> id
1 ==> name
2 ==> age
Person(id=1, name=瓜田李下, age=20)
Person(id=2, name=瓜田李下2, age=21)
Person(id=3, name=瓜田李下3, age=22)
Person(id=4, name=瓜田李下4, age=23)
Person(id=5, name=瓜田李下5, age=24)
Person(id=6, name=瓜田李下6, age=25)
21:33:54.052 [main] INFO com.example.demo.listener.CustomListener - 数据解析完成

read2

invokeHead
0 ==> id
1 ==> name
2 ==> age
Person(id=1, name=海贼王, age=20)
Person(id=2, name=海贼王2, age=21)
Person(id=3, name=海贼王3, age=22)
Person(id=4, name=海贼王4, age=23)
Person(id=5, name=海贼王5, age=24)
Person(id=6, name=海贼王6, age=25)
21:33:54.102 [main] INFO com.example.demo.listener.CustomListener - 数据解析完成

read3

invokeHead
0 ==> id
1 ==> name
2 ==> age
Person(id=1, name=瓜田李下, age=20)
Person(id=2, name=瓜田李下2, age=21)
Person(id=3, name=瓜田李下3, age=22)
Person(id=4, name=瓜田李下4, age=23)
Person(id=5, name=瓜田李下5, age=24)
Person(id=6, name=瓜田李下6, age=25)
21:33:54.137 [main] INFO com.example.demo.listener.CustomListener - 数据解析完成
21:33:54.137 [main] DEBUG com.alibaba.excel.metadata.property.ExcelHeadProperty - The initialization sheet/table 'ExcelHeadProperty' is complete , head kind is CLASS
21:33:54.137 [main] DEBUG com.alibaba.excel.context.AnalysisContextImpl - Began to read：ReadSheetHolder{sheetNo=1, sheetName='Sheet2'} com.alibaba.excel.read.metadata.holder.xlsx.XlsxReadSheetHolder@2dde1bff
invokeHead
0 ==> id
1 ==> name
2 ==> age
Person(id=1, name=瓜田李下, age=20)
Person(id=2, name=瓜田李下2, age=21)
Person(id=3, name=瓜田李下3, age=22)
Person(id=4, name=瓜田李下4, age=23)
Person(id=5, name=瓜田李下5, age=24)
Person(id=6, name=瓜田李下6, age=25)
Person(id=2, name=海贼王2, age=21)
Person(id=3, name=海贼王3, age=22)
Person(id=4, name=海贼王4, age=23)
Person(id=5, name=海贼王5, age=24)
Person(id=6, name=海贼王6, age=25)
21:33:54.145 [main] INFO com.example.demo.listener.CustomListener - 数据解析完成

read4

write

write2

write3

write4