Java操作excel工具&easyExcel

EasyExcel写入Excel代码：

方法一（没成功）：

/**
     * 根据路径写入excel
     * @param path
     */
    public static void writeExcelSample(String path) {
        try {
            OutputStream out = new FileOutputStream(path);
            try {
                ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX,false);
                //写第一个sheet, sheet1  数据全是List<String> 无模型映射关系
                Sheet sheet1 = new Sheet(1, 0);
                sheet1.setSheetName("第一个sheet");
                writer.write(getListString(), sheet1);
                writer.finish();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
    }

方法二：

表头，也可以作为写入数据读取数据的载体

    @Data
    public static class ExcelTitle extends BaseRowModel {

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

        @ExcelProperty(value = "学校", index = 1)
        private String school;

        @ExcelProperty(value = "教室", index = 2)
        private String classRoom;

        @ExcelProperty(value = "性别", index = 3)
        private String sex;

        @ExcelProperty(value = "年龄", index = 4)
        private Integer age;

        @ExcelProperty(value = "地址", index = 5)
        private String address;
    }

创建50个工作簿，每个工作部写入6万条数据，共3百万条数据

　　　/**
     * 模拟查询数据
     * @return
     */
    private static List<ExcelTitle> getList() {
        List<ExcelTitle> list = new ArrayList<>();
        ExcelTitle data = new ExcelTitle();
        data.setName("***");
        data.setAddress("**省**市**县**镇**村***路*号");
        data.setAge(24);
        data.setSchool("*****学校");
        data.setSex("男");
        data.setClassRoom("******班");
        for(int i=0; i<60000; i++) {
            list.add(data);
        }
        return list;
    }

    /**
     * 根据文件路径写入数据
     * @param path
     */
    public static void writeExel(String path) {
        ExcelWriter excelWriter = EasyExcel.write(path, ExcelTitle.class).build();
        for(int i=0; i<50; i++) {
            WriteSheet sheet = EasyExcel.writerSheet(i, i+"工作簿").build();
            excelWriter.write(getList(), sheet);
        }
        excelWriter.finish();
    }

EasyExcel读取数据：

    /**
     * 根据path 同步读取excel数据
     * @param path
     */
    public static void readExcel(String path) {
        Sheet sheet = new Sheet(1, 1, ExcelTitle.class);
        List<Object> readList = null;
        List<ExcelTitle> list = new ArrayList<>();
        try {
            readList = EasyExcelFactory.read(new FileInputStream(path), sheet);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        for (Object object : readList) {
            list.add((ExcelTitle) object);
            System.out.println(object.toString());
        }
    }

    public static class ExcelModelListener extends AnalysisEventListener<ExcelTitle> {

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

        private static int count = 0;

        @Override
        public void invoke(ExcelTitle excelTitle, AnalysisContext analysisContext) {
            System.out.println("解析到一条数据:" + excelTitle.toString());
            list.add(excelTitle);
            count++;
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            System.out.println("所有数据解析完成,解析道" + count + "条");
        }
    }

    /**
     * 根据path异步读取excel
     * @param path
     */
    public static void readExcelOfAsyn(String path) {
        try {
            Sheet sheet = new Sheet(1, 1, ExcelTitle.class);
            EasyExcelFactory.readBySax(new FileInputStream(path), sheet, new ExcelModelListener());
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
    }

EasyExcel依赖：

        <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.6</version>
        </dependency>    

 

推荐阅读： https://blog.csdn.net/jiangjiandecsd/article/details/81115622

                   https://blog.csdn.net/weixin_37775583/article/details/89642093

                   https://www.cnblogs.com/oukele/p/11443659.html

解决反射报错： https://blog.csdn.net/hanchao5272/article/details/79435358?depth_1-utm_source=distribute.pc_relevant.none-task&utm_source=distribute.pc_relevant.none-task