原 easyexcel（二十三）：同一个sheet 双表头填充，下面的表头和数据动态往下移动

版权声明：本文为博主原创文章，请尊重他人的劳动成果，转载请附上原文出处链接和本声明。
本文链接：https://www.91mszl.com/zhangwuji/article/details/1473

一：代码

@GetMapping("/download")
    public void download(HttpServletResponse response) throws IOException {
        ClassPathResource resource=new ClassPathResource("template/user.xlsx");
        InputStream inputStream=resource.getInputStream();

        // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
        String fileName = System.currentTimeMillis() + ".xlsx";
        response.reset();
        response.addHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
        OutputStream os=response.getOutputStream();

        List<UserInfo> userList=new ArrayList<>();
        UserInfo ui1=new UserInfo();
        ui1.setName("张无忌");
        ui1.setAge(20);

        UserInfo ui2=new UserInfo();
        ui2.setName("赵敏");
        ui2.setAge(18);

        UserInfo ui3=new UserInfo();
        ui3.setName("周芷若");
        ui3.setAge(22);

        UserInfo ui4=new UserInfo();
        ui4.setName("金毛狮王");
        ui4.setAge(38);

        UserInfo ui5=new UserInfo();
        ui5.setName("杨肖");
        ui5.setAge(28);

        UserInfo ui6=new UserInfo();
        ui6.setName("灭绝师太");
        ui6.setAge(50);

        UserInfo ui7=new UserInfo();
        ui7.setName("白眉鹰王");
        ui7.setAge(67);

        userList.add(ui1);
        userList.add(ui2);
        userList.add(ui3);
        userList.add(ui4);
        userList.add(ui5);
        userList.add(ui6);
        userList.add(ui7);

        List<UserCity> cityList=new ArrayList<>();
        UserCity uc1=new UserCity();
        uc1.setCity("上海");
        uc1.setAddress("上海火车站");
        UserCity uc2=new UserCity();
        uc2.setCity("北京");
        uc2.setAddress("北京西站");

        cityList.add(uc1);
        cityList.add(uc2);

        try (ExcelWriter excelWriter = EasyExcel.write(os).withTemplate(inputStream).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            FillConfig fillConfig = FillConfig.builder().build();
            excelWriter.fill(new FillWrapper("data1", userList), fillConfig, writeSheet);
            excelWriter.fill(new FillWrapper("data2", cityList), fillConfig, writeSheet);
        }
    }

二：模板

三：效果图

备注：此时我们发现，怎么导致的效果和我们设置的模板效果不一致，原因是我们模板设置第一个表头和第二个表头的间隔行只有一行，当第一个表头的数据超过一行时，会往下填充，这样就会覆盖第二个表头和数据。

四：解决方法：在填充的时候加上 forceNewRow(Boolean.TRUE)

@GetMapping("/download")
    public void download(HttpServletResponse response) throws IOException {
        ClassPathResource resource=new ClassPathResource("template/user.xlsx");
        InputStream inputStream=resource.getInputStream();

        // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
        String fileName = System.currentTimeMillis() + ".xlsx";
        response.reset();
        response.addHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
        OutputStream os=response.getOutputStream();

        List<UserInfo> userList=new ArrayList<>();
        UserInfo ui1=new UserInfo();
        ui1.setName("张无忌");
        ui1.setAge(20);

        UserInfo ui2=new UserInfo();
        ui2.setName("赵敏");
        ui2.setAge(18);

        UserInfo ui3=new UserInfo();
        ui3.setName("周芷若");
        ui3.setAge(22);

        UserInfo ui4=new UserInfo();
        ui4.setName("金毛狮王");
        ui4.setAge(38);

        UserInfo ui5=new UserInfo();
        ui5.setName("杨肖");
        ui5.setAge(28);

        UserInfo ui6=new UserInfo();
        ui6.setName("灭绝师太");
        ui6.setAge(50);

        UserInfo ui7=new UserInfo();
        ui7.setName("白眉鹰王");
        ui7.setAge(67);

        userList.add(ui1);
        userList.add(ui2);
        userList.add(ui3);
        userList.add(ui4);
        userList.add(ui5);
        userList.add(ui6);
        userList.add(ui7);

        List<UserCity> cityList=new ArrayList<>();
        UserCity uc1=new UserCity();
        uc1.setCity("上海");
        uc1.setAddress("上海火车站");
        UserCity uc2=new UserCity();
        uc2.setCity("北京");
        uc2.setAddress("北京西站");

        cityList.add(uc1);
        cityList.add(uc2);

        try (ExcelWriter excelWriter = EasyExcel.write(os).withTemplate(inputStream).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            excelWriter.fill(new FillWrapper("data1", userList), fillConfig, writeSheet);
            excelWriter.fill(new FillWrapper("data2", cityList), fillConfig, writeSheet);
        }
    }

五：最终效果