easyexcel读写

一、pom.xml依赖

 <!--EasyExcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>1.1.1</version>
        </dependency>

二、读入

 //读取excel文件
    @Test
    public void readExcel() throws Exception {
        ExcelReader excelReader = new ExcelReader(new FileInputStream("g:/excel/my_2007.xlsx"), ExcelTypeEnum.XLSX, null, new ExcelListener());
        excelReader.read(new Sheet(1, 1,User2.class));
    }

测试结果：

 

三、导出

@Test
    public void writeWithoutHead() throws IOException {
        OutputStream out = new FileOutputStream("G:\\excel\\test8.xlsx");

        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true);
        Sheet sheet1 = new Sheet(1, 0);
        Sheet sheet2 = new Sheet(2, 0);

        sheet1.setSheetName("sheet1");
        sheet2.setSheetName("sheet2");

        //导出的数据
        List<List<String>> data = new ArrayList<>();
        for (int i = 0; i < 100; i++) {
            List<String> item = new ArrayList<>();
            item.add("item0" + i);
            item.add("item1" + i);
            item.add("item2" + i);
            data.add(item);
        }

        //设置表头
        List<List<String>> head = new ArrayList<List<String>>();
        List<String> headCoulumn1 = new ArrayList<String>();
        List<String> headCoulumn2 = new ArrayList<String>();
        List<String> headCoulumn3 = new ArrayList<String>();
        headCoulumn1.add("第一列");
        headCoulumn1.add("第一列11");

        headCoulumn2.add("第二列");
        headCoulumn3.add("第三列");
        head.add(headCoulumn1);
        head.add(headCoulumn2);
        head.add(headCoulumn3);
        Table table = new Table(1);
        table.setHead(head);

        //写出到excel的sheet1
        //writer.write0(data, sheet1);
        writer.write0(data, sheet1, table);
        writer.write0(data, sheet2, table);
        writer.finish();
    }

测试结果：

四、使用到的其它类

ExcelListener ：

package com.kf.start;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

public class ExcelListener extends AnalysisEventListener<User2> {

    @Override
    public void invoke(User2 o, AnalysisContext analysisContext) {
        System.out.println("invoke");
        System.out.println(o);

    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("doAfterAllAnalysed");
        int total = analysisContext.getTotalCount();
        System.out.println("总行数："+total);
    }
}

User2:

package com.kf.start;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;

import java.util.Date;

public class User2 extends BaseRowModel {

    @ExcelProperty(value = "姓名", index = 0)
    public String name;

    @ExcelProperty(value = "年龄", index = 1)
    public String age;

    @ExcelProperty(value = "日期", index = 2)
    public Date date;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAge() {
        return age;
    }

    public void setAge(String age) {
        this.age = age;
    }

    public Date getDate() {
        return date;
    }

    public void setDate(Date date) {
        this.date = date;
    }

    @Override
    public String toString() {
        return "User2{" +
                "name='" + name + '\'' +
                ", age='" + age + '\'' +
                ", date=" + date +
                '}';
    }
}

 

五、总结：用poi挺好，没有必要一定要用easyexcel。如果数据量大，如导出数据大于36656条可以考虑使用easyexcel，因为poi导出最大条数有限制。个人建议如果没有遇到OOM就用poi也挺好的。