EasyExcel的使用

EasyExcel的使用

1、导入依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.1.1</version>
</dependency>
    <!--xls-->
    <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
    </dependency>
    <!--xlsx-->
    <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
    </dependency>

2、使用

1、创建实体类

@Data
public class DemoData {


    /**
     * @ExcelProperty   设置Excel表头名称
     *
     * value 表头名称
     *
     * index  第几列
     */
    @ExcelProperty(value = "学生编码",index = 0)
    private Integer sno;

    @ExcelProperty(value = "学生姓名",index = 1)
    private String sname;
}

2、创建读监控器

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

import java.util.Map;


public class ExcelListener extends AnalysisEventListener<DemoData> {

    /**
     * 一行一行读取excel的内容
     * @param demoData
     * @param analysisContext
     */
    @Override
    public void invoke(DemoData demoData, AnalysisContext analysisContext) {
        System.out.println("**** "  +  demoData);
    }

    /**
     * 读取完成之后
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
    }

    /**
     * 读取表头内容
     * @param headMap
     * @param context
     */
    public void invokeHead(Map<Integer, CellData> headMap, AnalysisContext context) {
        System.out.println("表头：" + headMap);
    }

}

3、测试

import com.alibaba.excel.EasyExcel;
import com.google.common.collect.Lists;
import org.junit.Test;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.List;

public class TestEasyExcel {

    @Test
    public void write(){

        /**
         * 实现Excel写的操作
         * 1.设置写入文件夹地址和excel文件地址
         */

        String fileName = "C:\\Users\\Lenovo\\Desktop\\test.xls";

        /**
         * 2.调用esayExcel里面的方法实现写操作
         *
         * write 方法两个参数，第一个参数文件路径名称，第二个参数实体类class
         */

        EasyExcel.write(fileName,DemoData.class).sheet("学生列表").doWrite(data());
    }


    @Test
    public void read(){
        String fileName = "C:\\Users\\Lenovo\\Desktop\\test.xls";

        EasyExcel.read(fileName,DemoData.class,new ExcelListener()).sheet().doRead();
    }

//    web读用MultipartFile
    public void webRead(MultipartFile file) throws IOException {
        EasyExcel.read(file.getInputStream(),DemoData.class,new ExcelListener()).sheet().doRead();
    }


    private static List<DemoData> data(){
        List<DemoData> list = Lists.newArrayList();

        for (int i = 0; i < 10; i++) {
            DemoData data = new DemoData();

            data.setSno(i);
            data.setSname("Tom" + i);
            list.add(data);
        }

        return list;
    }
}