一. 概述

今天就写个简单的EasyExcel多sheet导出导入例子, 简单易学, 快速上手

二. 示例

2.1 多sheet导出

public class Test{
    @Data
    @ColumnWidth(20)
    public static class TestVO {
        @ExcelProperty( value = "姓名",index = 0)
        private String name;
        @ExcelProperty( value = "年龄",index = 1)
        private int age;
        @ExcelProperty( value = "学校",index = 2)
        private String school;
    }
     /**
     * 多个sheet导入测试
     * @throws FileNotFoundException
     */
    @Test
    public void sheetImport() throws FileNotFoundException {
        // 输出流
        OutputStream outputStream = null;
        outputStream = new FileOutputStream(new File("D:/1.xlsx"));

        // 导出的数据
        List<TestVO> dataList = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            TestVO testVO = new TestVO();
            testVO.setAge(i + 20);
            testVO.setName("vo" + i);
            testVO.setSchool("school" + i);
            dataList.add(testVO);
        }

        // 标题
        List<String> headList = Arrays.asList("姓名", "年龄", "学校");

        // 测试多sheel导出
        ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
        WriteSheet test1 = EasyExcel.writerSheet(0, "test1").head(TestVO.class).build();
        WriteSheet test2 = EasyExcel.writerSheet(1, "test2").head(TestVO.class).build();
        excelWriter.write(dataList,test1).write(dataList,test2);
        excelWriter.finish();
    }
}

如果导出需加样式, 示例

        // 表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 单元格样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 初始化表格样式
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        WriteSheet test1 = EasyExcel.writerSheet(0, "test1").head(TestVO.class).
                registerWriteHandler(horizontalCellStyleStrategy).build();

导出效果

2.2 多sheet导入

public class Test{
    @Data
    @ColumnWidth(20)
    public static class TestVO {
        @ExcelProperty( value = "姓名",index = 0)
        private String name;
        @ExcelProperty( value = "年龄",index = 1)
        private int age;
        @ExcelProperty( value = "学校",index = 2)
        private String school;
    }
    @Data
    @ColumnWidth(20)
    public static class TestVO1 {
        @ExcelProperty( value = "姓名",index = 0)
        private String name;
        @ExcelProperty( value = "年龄",index = 1)
        private int age;
        @ExcelProperty( value = "学校",index = 2)
        private String school;
    }
    /**
     * 测试导入多个sheet导入
     * @throws Exception
     */
    @Test
    public void read() throws Exception {
        String filePath = "D:/1.xlsx";
        InputStream inputStream = null;
        inputStream = new FileInputStream(new File(filePath));
        AnalysisEventListenerImpl<Object> listener = new AnalysisEventListenerImpl<>();
        ExcelReader excelReader = EasyExcel.read(inputStream,listener).build();
        // 第一个sheet读取类型
        ReadSheet readSheet1 = EasyExcel.readSheet(0).head(TestVO.class).build();
        // 第二个sheet读取类型
        ReadSheet readSheet2 = EasyExcel.readSheet(1).head(TestVO1.class).build();
        // 开始读取第一个sheet
        excelReader.read(readSheet1);
        List<Object> list = listener.getDatas();
        list.forEach((user)->{
            TestVO user1= (TestVO) user;
            System.out.println(user1.getName()+", "+user1.getAge()+", "+user1.getSchool());
        });
        // 清空之前的数据
        listener.getDatas().clear();
        // 开始读取第二个sheet
        excelReader.read(readSheet2);
        System.out.println("---------------------------------");
        List<Object> list2 = listener.getDatas();
        list2.forEach((user)->{
            TestVO1 user2= (TestVO1) user;
            System.out.println(user2.getName()+", "+user2.getAge()+", "+user2.getSchool());
        });
    }
}

打印

vo0, 20, school0
vo1, 21, school1
vo2, 22, school2
vo3, 23, school3
vo4, 24, school4
vo5, 25, school5
vo6, 26, school6
vo7, 27, school7
vo8, 28, school8
vo9, 29, school9
---------------------------------
小王, 20, school0
小王, 21, school1
小王, 22, school2
小王, 23, school3
小王, 24, school4
小王, 25, school5
小王, 26, school6
小王, 27, school7
小王, 28, school8
小王, 29, school9