#Easy Excel - Excel读取 - 《Java 扩展组件学习》

基础学习
指定列名查询或者下标查询
- IndexOrNameData
读取多个Sheet
- 核心业务代码
日期、数字或者自定义格式转换
- 全局注册转换器
- 局部使用转换器
超链接、批注、合并单元格读取
- 监听器重写方法
读取单元格类型和公式
- 核心代码
数据读取，数据转换异常处理
- 核心代码
读取表头信息（读取列名）
不创建对象进行读取（实用）
- 核心代码
读取web接口上传的文件

基础学习

准备excel文件如下：

根据文件准备实体类

SimpleData

@Data
public class SimpleData {
    private String strLabel;
    private String dateLabel;
    private int intLabel;
    private double doubleLabel;
}

监听器

为该文件创建一个监听类

// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class DemoDataListener extends AnalysisEventListener<DemoData> {
    private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class);
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    List<DemoData> list = new ArrayList<>();
    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private DemoDAO demoDAO;
    public DemoDataListener() {
        // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
        demoDAO = new DemoDAO();
    }
    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param demoDAO
     */
    public DemoDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }
    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }
    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        LOGGER.info("所有数据解析完成！");
    }
    /**
     * 加上存储数据库
     */
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        demoDAO.save(list);
        LOGGER.info("存储数据库成功！");
    }
}

业务代码

public class DemoDAO {
    public void save(List<DemoData> list) {
        System.out.println("存储成功");
    }
}

测试代码

public class SimpleRead {
    public static void main(String[] args) throws IOException {
        ClassPathResource resource = new ClassPathResource("demo/demo.xlsx");
        String absolutePath = resource.getFile().getAbsolutePath();
        ExcelReader excelReader = null;
        try {
            excelReader = EasyExcel.read(absolutePath, DemoData.class, new DemoDataListener()).build();
            ReadSheet readSheet = EasyExcel.readSheet(0).build();
            excelReader.read(readSheet);
        } finally {
            assert excelReader != null;
            excelReader.finish();
        }
    }
}

运行结果

这种方式一定严格遵循excel表中列的数据类型进行设计实体类的数据类型，因为程序会进行自上而下的获取列，如果顺序错误，可能会导致数据类型转换异常

指定列名查询或者下标查询

IndexOrNameData

@Data
public class IndexOrNameData {
    @ExcelProperty("字符串")
    private String strLabel;
    @ExcelProperty("日期")
    private String dateLabel;
    @ExcelProperty("数字")
    private int intLabel;
    @ExcelProperty("小数")
    private double doubleLabel;
}
//或者
@Data
public class IndexOrNameData {
    @ExcelProperty(index = 0)
    private String strLabel;
    @ExcelProperty(index = 2)
    private String dateLabel;
    @ExcelProperty(index = 3)
    private int intLabel;
    @ExcelProperty(index = 1)
    private double doubleLabel;
}

注意对于@ExcelProperty注解，要么使用index进行标记列的位置，下标从0 开始，要么使用name值进行匹配列
一般推荐使用name进行匹配，不推荐两个混合使用，
如果name重复，后面的列会覆盖前面的列，导致只能获取到一列的值

使用指定之后只要指定正确即可，不用太在意实体类属性的声明顺序

读取多个Sheet

读取一张表中的多个sheet，默认读取第一个

核心业务代码

public static void readMoreSheets(String path) {
        ExcelReader excelReader = null;
        try {
            //读取全部sheet
            EasyExcel.read(path, SimpleData.class, new SimpleDataListener()).doReadAll();
            //读取部分sheet
            excelReader = EasyExcel.read(path).build();
            //不同的sheet必须使用不同的listener
            ReadSheet readSheet1 = EasyExcel.readSheet(0).head(SimpleData.class).registerReadListener(new SimpleDataListener()).build();
            ReadSheet readSheet2 = EasyExcel.readSheet(1).head(IndexOrNameData.class).registerReadListener(new IndexOrNameDataListener()).build();
            //必须将多个sheet传入
            excelReader.read(readSheet1,readSheet2);
        } finally {
            assert excelReader != null;
            excelReader.finish();
        }
    }

核心步骤如下：

生成ExcelReader，先读取全部的sheets
单独读取每一个sheet，并为之分配对应的实体类和监听器
将多个读取的sheet传入ExcelReader

但是读取sheet会时如果是03版本的excel会重复读取多次

日期、数字或者自定义格式转换

全局注册转换器

在进行读取或者写入操作的时候进行注册

  public static void convertExcel(String path) {
        EasyExcel.read(path, ConvertData.class, new ConvertDataListener())
                //可以注册转换器，但是这种注册方式是全局的，可以对所有列进行转换
                //如果只需要转换一列的话，使用@ExcelProperty(converter="转换器.class")
                .registerConverter(new CustomStringConvert())
                .sheet()
                .doRead();
    }

局部使用转换器

在对应的实体类中进行声明
实体

@Data
public class ConvertData {
    @ExcelProperty(index = 0, converter = CustomStringConvert.class)
    private String strLabel;
    @ExcelProperty(index = 2)
    @DateTimeFormat("yy-MM-dd HH:mm:ss")
    private String dateLabel;
    @ExcelProperty(index = 3)
    private int intLabel;
    @ExcelProperty(index = 1)
    @NumberFormat("#.##%")
    private double doubleLabel;
}

测试代码

  public static void convertExcel(String path) {
        EasyExcel.read(path, ConvertData.class, new ConvertDataListener())
                //可以注册转换器，但是这种注册方式是全局的，可以对所有列进行转换
                //如果只需要转换一列的话，使用@ExcelProperty(converter="转换器.class")
                //.registerConverter(new CustomStringConvert())
                .sheet()
                .doRead();
    }

超链接、批注、合并单元格读取

在相对应的监听器中重写extra方法即可

为额外的数据读取创建实体，或者使用map

@Data
public class ExtraData {
    private String row1;
    private String row2;
    private String row3;
}

监听器重写方法

public class ExtraDataListener extends AnalysisEventListener<ExtraData> {
    private static final Logger LOGGER = LoggerFactory.getLogger(ExtraDataListener.class);
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private final DemoDAO demoDAO;
    List<ExtraData> list = new ArrayList<>();
    public ExtraDataListener() {
        // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
        demoDAO = new DemoDAO();
    }
    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param demoDAO
     */
    public ExtraDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }
    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data    one row value. It is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(ExtraData data, AnalysisContext context) {
        LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if(list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }
    @Override
    public void extra(CellExtra extra, AnalysisContext context) {
        LOGGER.info("读取到了一条额外信息:{}", JSON.toJSONString(extra));
        switch(extra.getType()) {
            case COMMENT:
                LOGGER.info("额外信息是批注,在rowIndex:{},columnIndex;{},内容是:{}", extra.getRowIndex(), extra.getColumnIndex()
                        , extra.getText());
                break;
            case HYPERLINK:
                if("Sheet1!A1".equals(extra.getText())) {
                    LOGGER.info("额外信息是超链接,在rowIndex:{},columnIndex;{},内容是:{}", extra.getRowIndex(),
                                extra.getColumnIndex(), extra.getText());
                } else if("Sheet2!A1".equals(extra.getText())) {
                    LOGGER.info("额外信息是超链接,而且覆盖了一个区间,在firstRowIndex:{},firstColumnIndex;{},lastRowIndex:{}," +
                                "lastColumnIndex:{}," + "内容是:{}", extra.getFirstRowIndex(),
                                extra.getFirstColumnIndex(), extra.getLastRowIndex(), extra.getLastColumnIndex(),
                                extra.getText());
                } else {
                    LOGGER.error("Unknown hyperlink!");
                }
                break;
            case MERGE:
                LOGGER.info("额外信息是超链接,而且覆盖了一个区间,在firstRowIndex:{},firstColumnIndex;{},lastRowIndex:{}," +
                            "lastColumnIndex:{}", extra.getFirstRowIndex(), extra.getFirstColumnIndex(),
                            extra.getLastRowIndex(), extra.getLastColumnIndex());
                break;
            default:
        }
    }
    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        LOGGER.info("所有数据解析完成！");
    }
    /**
     * 加上存储数据库
     */
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        demoDAO.save(list);
        LOGGER.info("存储数据库成功！");
    }
}

读取操作的时候需要进行显示的指定想要读取哪些额外的内容

 /**
     * 读取excel其他的额外内容，批注，合并单元格，超链接等等
     * 监听器中重写extra方法即可
     */
    private static void extraRead(String path) {
        EasyExcel.read(path, ExtraData.class, new ExtraDataListener())
                 //需要显示指定读取的额外内容
                 //批注
                 .extraRead(CellExtraTypeEnum.COMMENT)
                 //单元格信息
                 .extraRead(CellExtraTypeEnum.MERGE)
                 //超链接
                 .extraRead(CellExtraTypeEnum.HYPERLINK).doReadAll();
    }

读取单元格类型和公式

可能会用到公式进行计算产生结果

使用CellData进行接收数据，指定数据类型进行接收接口

核心代码

@Data
public class CellReadData {
    private CellData<String> strLabel;
    //虽然制定了Date类型，但是实际上excel存储的是number类型的值
    private CellData<Date> dateLabel;
    private CellData<Double> doubleData;
    private CellData<String> formulaValue;
}

测试代码

/**
     * 读取单元格类型和公式
     *
     * @param path
     */
    private static void cellReadDataRead(String path) {
        EasyExcel.read(path, CellReadData.class, new CellReadDataListener()).sheet(1).doRead();
    }

数据读取，数据转换异常处理

当我们使用了错误的数据类型进行接收excel的列时，就会导致异常的出现，我们可以指定异常处理，跳过该列，继续读取剩下的列数据

核心代码

重写监听器中的onException方法，在里面执行相关的业务逻辑


/**
 * @author xupu
 * @Description 数据监听器
 * @Date 2021-08-27 11:43
 */
// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class ExceptionDataListener extends AnalysisEventListener<ExceptionData> {
    private static final Logger LOGGER = LoggerFactory.getLogger(ExceptionDataListener.class);
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private final DemoDAO demoDAO;
    List<ExceptionData> list = new ArrayList<>();
    public ExceptionDataListener() {
        // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
        demoDAO = new DemoDAO();
    }
    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param demoDAO
     */
    public ExceptionDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }
    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data    one row value. It is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(ExceptionData data, AnalysisContext context) {
        LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if(list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }
    /**
     * 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。
     *
     * @param exception
     * @param context
     *
     * @throws Exception
     */
    @Override
    public void onException(Exception exception, AnalysisContext context) {
        LOGGER.error("解析失败，但是继续解析下一行:{}", exception.getMessage());
        // 如果是某一个单元格的转换异常 能获取到具体行号
        // 如果要获取头的信息 配合invokeHeadMap使用
        if(exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
            LOGGER.error("第{}行，第{}列解析异常", excelDataConvertException.getRowIndex(),
                         excelDataConvertException.getColumnIndex());
        }
    }
    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        LOGGER.info("doAfterAllAnalysed！");
        LOGGER.info("所有数据解析完成！");
    }
    /**
     * 加上存储数据库
     */
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        demoDAO.save(list);
        LOGGER.info("存储数据库成功！");
    }
}

读取表头信息（读取列名）

核心代码
重写监听器中的invokeHeadMap方法，读取表格中的头部数据

/**
     * 读取多行表头
     * 监听器中重写invokeHeadMap方法
     *
     * @param path
     */
    public static void headerRead(String path) {
        //指定已知实体类读取列名
        //EasyExcel.read(path, SimpleData.class, new SimpleDataListener()).sheet().doRead();
        //未知实体，可以直接读取
        EasyExcel.read(path, new NoModelDataListener()).sheet().doRead();
    }

监听器

public class NoModelDataListener extends AnalysisEventListener<Map<Integer, String>> {
    private static final Logger LOGGER = LoggerFactory.getLogger(NoModelDataListener.class);
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    List<Map<Integer, String>> list = new ArrayList<Map<Integer, String>>();
    @Override
    public void invoke(Map<Integer, String> data, AnalysisContext context) {
        LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
        list.add(data);
        if(list.size() >= BATCH_COUNT) {
            saveData();
            list.clear();
        }
    }
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
        LOGGER.info("所有数据解析完成！");
    }
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        LOGGER.info("解析到一条头数据:{}", JSON.toJSONString(headMap));
    }

不创建对象进行读取（实用）

在一些通用的情况下，我们可能不会清楚的知道excel的每一个列是什么，这个时候我们可以使用Map来读取excel的列数据

核心代码

@Slf4j
public class NoModelDataListener extends AnalysisEventListener<Map<Integer, String>> {
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 3000;
    List<Map<Integer, String>> list = new ArrayList<>();
    @Override
    public void invoke(Map<Integer, String> data, AnalysisContext context) {
        log.info("解析到一条数据:{}", JSON.toJSONString(data));
        list.add(data);
        if (list.size() >= BATCH_COUNT) {
            saveData();
            list.clear();
        }
    }
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
        log.info("所有数据解析完成！");
    }
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        log.info("解析到一条头数据:{}", JSON.toJSONString(headMap));
    }
    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", list.size());
        log.info("存储数据库成功！");
    }
}

测试代码

 /**
     * 通用读取，不创建对象进行读取，
     * 将监听器中的监听具体对象改为Map
     *
     * @param path
     */
    private static void noModelDataRead(String path) {
        //不再要求声明相对应的实体
        EasyExcel.read(path, new NoModelDataListener()).sheet().doRead();
    }

可以搭配读取表头信息方法进行获取每一列的列名

读取web接口上传的文件

当我们需要使用到上传文件的读取方法时，可以直接传递上传的文件的字节码进行解析获取

/**
     * 读取通过web接口上传的文件
     *
     * @param file
     */
    private static void webDataRead(MultipartFile file) {
        try {
            EasyExcel.read(file.getInputStream(), UploadData.class, new UploadDataListener()).sheet().doRead();
        } catch(IOException e) {
            e.printStackTrace();
        }
    }

可以结合不创建对象进行读取一起使用