EasyExecl读取Execl数据

小鱼儿 2023-03-01 14:59 119阅读 0赞

### 参考位置: [https://www.jianshu.com/p/80505fb72493][https_www.jianshu.com_p_80505fb72493] ###

### 0.execl数据结构 ###

![20200727155831828.png][]

### 1.pom.xml文件 ###

<dependency>
         <groupId>com.alibaba</groupId>
         <artifactId>easyexcel</artifactId>
         <version>1.1.2-beta5</version>
    </dependency>

### 2.根据execl建立实体数据 ###

@Data
    public class DemoData extends BaseRowModel {
    
        @ExcelProperty(value = "序号", index = 0)
        private Integer id;
    
        @ExcelProperty(value = "名称", index = 1)
        private String  name;
    
        @ExcelProperty(value = "文本类型", index = 2)
        private String type;
    
        @ExcelProperty(value = "格式", index = 3)
        private String format;
    
        @ExcelProperty(value = "供应商", index = 4)
        private String cp;
    
        @ExcelProperty(value = "合同到期时间", index = 5)
        private String expireDate;
    
    }

### 3.建立监听 ###

@Slf4j
    public class ExcelListener<T extends BaseRowModel> extends AnalysisEventListener<T> {
        private final List<T> rows = new ArrayList<>();
    
        @Override
        public void invoke(T object, AnalysisContext context) {
            rows.add(object);
        }
    
        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            log.info("read {} rows %n", rows.size());
        }
    
        public List<T> getRows() {
            return rows;
        }
    }

### 4.execl工具类 ###

@Slf4j
    public final class ExcelUtil {
        /**
         * 从Excel中读取文件，读取的文件是一个DTO类，该类必须继承BaseRowModel
         * 具体实例参考 ： MemberMarketDto.java
         * 参考：https://github.com/alibaba/easyexcel
         * 字符流必须支持标记，FileInputStream 不支持标记，可以使用BufferedInputStream 代替
         * BufferedInputStream bis = new BufferedInputStream(new FileInputStream(...));
         */
        public static <T extends BaseRowModel> List<T> readExcel(final InputStream inputStream, final Class<? extends BaseRowModel> clazz) {
            if (null == inputStream) {
                throw new NullPointerException("the inputStream is null!");
            }
            ExcelListener<T> listener = new ExcelListener<>();
            // 这里因为EasyExcel-1.1.1版本的bug，所以需要选用下面这个标记已经过期的版本
            ExcelReader reader = new ExcelReader(inputStream,  valueOf(inputStream), null, listener);
            //读取Sheet1   从第2行开始读取
            reader.read(new com.alibaba.excel.metadata.Sheet(1, 2, clazz));
    
            return listener.getRows();
        }
    
    
        public static void writeExcel(final File file, List<? extends BaseRowModel> list) {
            try (OutputStream out = new FileOutputStream(file)) {
                ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
                //写第一个sheet,  有模型映射关系
                Class<? extends BaseRowModel> t = list.get(0).getClass();
                Sheet sheet = new Sheet(1, 0, t);
                writer.write(list, sheet);
                writer.finish();
            } catch (IOException e) {
                log.warn("fail to write to excel file: file[{}]", file.getName(), e);
            }
        }
    
    
        /**
         * 根据输入流，判断为xls还是xlsx，该方法原本存在于easyexcel 1.1.0 的ExcelTypeEnum中。
         */
        public static ExcelTypeEnum valueOf(InputStream inputStream) {
            try {
                FileMagic fileMagic = FileMagic.valueOf(inputStream);
                if (FileMagic.OLE2.equals(fileMagic)) {
                    return ExcelTypeEnum.XLS;
                }
                if (FileMagic.OOXML.equals(fileMagic)) {
                    return ExcelTypeEnum.XLSX;
                }
                throw new IllegalArgumentException("excelTypeEnum can not null");
    
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }
    }

### 5.测试读取数据 ###

public static void main(String[] args) {
            try (FileInputStream inputStream = new FileInputStream("C:\\Users\\Administrator\\Desktop\\demo.xls")) {
                List<DemoData> demoDataList = ExcelUtil.readExcel(new BufferedInputStream(inputStream), DemoData.class);
                System.out.println(demoDataList);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

[https_www.jianshu.com_p_80505fb72493]: https://www.jianshu.com/p/80505fb72493
[20200727155831828.png]: /images/20230208/0041c7042cfd4807956a18f36d1f6566.png