# 使用EasyExcel 实现操作Excel（读写）Excel工具类

引入EasyExcel依赖

<!-- EasyExcel依赖 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.0.5</version>
</dependency>

<!-- fastJson -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.58</version>
</dependency>

模板类（Excel对象）

public class DemoData {

    private String string1;
    private String string2;
    private String string3;
    private String string4;
    private String string5;
    private String string6;
    private String string7;
    private String string8;
    private String string9;
    private String string10;
    private String string11;
    private String string12;
    private String string13;
    private String string14;
    private String string15;
    private String string16;
    private String string17;
    private String string18;
    
    /**
     * 省略getset()
     */
}

EasyExcelUtils工具类

public class EasyExcelUtils {

    private static final Logger logger= LoggerFactory.getLogger(EasyExcelUtils.class);


    /**
     * <p>获得Excel中页标签的信息（编号、名称等）
     *
     * @author LiDong
     * @date 2020/11/10
     * @param filePath：需要解析的excel路径
     * @param clazz：解析Excel得到的对象类
     * @param analysisEventListener：继承AnalysisEventListener<Object>的类
     * @return java.util.List<com.alibaba.excel.read.metadata.ReadSheet>
     */
    public static List<ReadSheet> listSheet(String filePath, Class<?> clazz, AnalysisEventListener<?> analysisEventListener){
        try{
            logger.info("======================开始解析Excel的sheet！");
            ExcelReader excelReader = EasyExcel.read(filePath, clazz, analysisEventListener).build();
            List<ReadSheet> readSheets = excelReader.excelExecutor().sheetList();
            logger.info("======================解析Excel的sheet完成！");
            return readSheets;
        }catch (Exception e){
            return new ArrayList<>();
        }
    }


    /**
     * 读Excel中的结果为List：结果的具体处理在Listener中进行处理
     *
     * @author LiDong
     * @date 2020/11/9
     * @param '[filePath, clazz, readListener, sheetCount]' 文件地址、结果类、结果操作类、表页签
     * @return java.util.List<java.util.Map<java.lang.String,java.lang.String>>
     */
    public static void readList(String filePath, Class<?> clazz, ReadListener<?> readListener , int sheetCount,int headRowNumber){
        try{
            if(Objects.isNull(filePath)){
                logger.info("文件地址出错！");
            }
            for (int i = 0; i < sheetCount; i++) {
                // 结果具体处理在Listener类中实现
                EasyExcel.read(filePath, clazz,readListener).sheet().sheetNo(i).headRowNumber(headRowNumber).doRead();
            }
        }catch (Exception e){
            logger.error(e.getMessage());
        }
    }

    public static void writeList(String filePath,Class<?> clazz,String  sheetName,List<?> data){
        try {
            EasyExcel.write(filePath,clazz).sheet(sheetName).doWrite(data);
            logger.info("写入数据到"+filePath+"成功！");
        }catch (Exception e){
            logger.error(e.getMessage());
        }
    }

}

EasyExcel 中Excel操作类

/**
 * @author LiDong
 * @version 1.0.0
 * @description TODO
 * @createTime 2020年11月09日 17:08:00
 */
public class DemoDataListener extends AnalysisEventListener<Object> {

    private static final Logger logger= LoggerFactory.getLogger(DemoDataListener.class);

    List<Object> list=new ArrayList<>();

    /**
     * 每一条数据解析的时候调用
     *
     * @author LiDong
     * @date 2020/11/9
     * @param '[o, analysisContext]'
     * @return void
     */
    @Override
    public void invoke(Object o, AnalysisContext analysisContext) {
        Integer sheetNo = analysisContext.readSheetHolder().getSheetNo();
        logger.info("第"+sheetNo+"个标签页，解析到一条数据：{}", JSON.toJSONString(o));
        list.add(o);
    }

    /**
     * 所有数据解析完成之后调用此方法
     *
     * @author LiDong
     * @date 2020/11/9
     * @param '[analysisContext]'
     * @return void
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        logger.info("JSONString:======"+JSON.toJSONString(list));
    }
}

测试类

public class EasyExcelTest {

    private static final Logger logger= LoggerFactory.getLogger(DemoDataListener.class);

    @Test
    public void test2(){
        try {
            String file = "D:\\帮助文档\\元数据导入模板理赔承保服务.xlsx";
            EasyExcelUtils.readList(file, DemoData.class,new DemoDataListener(),6,0);
            // 读取部分sheet
        }catch (Exception e){
            logger.error(e.getMessage());
            e.printStackTrace();
        }
    }

    @Test
    public void test3(){
        String file = "D:\\帮助文档\\元数据导入模板理赔承保服务.xlsx";
        List<ReadSheet> readSheets =EasyExcelUtils.listSheet(file,DemoData.class, new DemoDataListener());
        readSheets.forEach(x->{
            System.out.println(x.getSheetNo());
            System.out.println(x.getSheetName());
        });
    }

    @Test
    public void wirteList(){
        String file="D:\\one.xlsx";
        List<DemoData> list=new ArrayList<>(5);
        for (int i = 0; i < 5; i++) {
            DemoData demoData=new DemoData();
            demoData.setString1(String.valueOf(i));
            demoData.setString2(String.valueOf(i));
            demoData.setString3(String.valueOf(i));
            demoData.setString4(String.valueOf(i));
            list.add(demoData);
        }
        EasyExcelUtils.writeList(file,DemoData.class,"test",list);
    }

}