EasyExcel使用模板文件填充数据

川长思鸟来 2023-10-11 15:09 9阅读 0赞

*  单组数据填充  
    可以填充一个对象或一个Map，单组数据填充时模板为\{ xxx \} ,xxx为对象的属性名或Map的key  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUwNjU2_size_16_color_FFFFFF_t_70]  
    测试代码：

/**
         *  使用模板单组数据填充
         */
        @Test
        public void test01(){
        
            //准备模板
            String template = "fill_data_template1.xlsx";
            ExcelWriterBuilder builder = EasyExcel.write("Execl-填充单组数据.xlsx", FillData.class).withTemplate(template);
            FillData fillData = new FillData();
            fillData.setAge(20);
            fillData.setName("Hello");
    
            //准备map数据
            Map<String,String> map=new HashMap<>();
    
            map.put("name","zs");
            map.put("number","50");
    
    //        builder.sheet().doFill(fillData);
            builder.sheet().doFill(map);
        }

![在这里插入图片描述][20201218162532864.png]  
注意：如果\{\}不需要转义，则需要用 \\ 转义。

*  多组数据填充  
    多组数据填充需要在\{\}前加 ‘.’如  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUwNjU2_size_16_color_FFFFFF_t_70 1]  
    测试代码：

/**
         *  多组数据填充 需要在模板的占位符中前加 '.'
         */
        @Test
        public void test02(){
        
            String template = "fill_data_template2.xlsx";
            ExcelWriterBuilder writerBuilder = EasyExcel.write("Excel-填充多组数据.xlsx", FillData.class).withTemplate(template);
            List<FillData> fillData = initData();
            writerBuilder.sheet().doFill(fillData);
    
        }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUwNjU2_size_16_color_FFFFFF_t_70 2]

*  复合填充

![在这里插入图片描述][202012181629337.png]  
现在是先填充多组数据，再最后这一行填充单组数据。这时需要分开填充，自己创建WorkBook和Sheet。

测试代码：

/**
         * 既能填充多组数据，又能填充单个数据
         */
        @Test
        public void test03(){
        
    
            String template = "fill_data_template3.xlsx";
    
            //工作薄对象
            ExcelWriter workBook = EasyExcel.write("Excel-填充单组和多组数据.xlsx", FillData.class).withTemplate(template).build();
    
            //工作区对象
            WriteSheet sheet = EasyExcel.writerSheet().build();
    
            Map<String,String> map = new HashMap<>();
    
            List<FillData> fillData = initData();
    
    //        FillConfig build = FillConfig.builder().forceNewRow(true).build();
    
            //填充单个数据
            map.put("date","2020-12-18");
            map.put("total",fillData.size() + "");
    
            //使用工作薄对象填充数据
    //        workBook.fill(fillData,build,sheet);
    //        workBook.fill(map,build,sheet);
            workBook.fill(fillData,sheet);
            workBook.fill(map,sheet);
    
            workBook.finish();
    
        }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUwNjU2_size_16_color_FFFFFF_t_70 3]  
发现填充数据错误，第三行的数据被单组数据填充了。  
解决方法：  
1. 每行数据填充完换行  
2. 将单组数据占位符放至最前，填充完单组数据再填充多组数据

每行数据填充完换行测试代码：

@Test
        public void test03(){
        
    
            String template = "fill_data_template3.xlsx";
    
            //工作薄对象
            ExcelWriter workBook = EasyExcel.write("Excel-填充单组和多组数据.xlsx", FillData.class).withTemplate(template).build();
    
            //工作区对象
            WriteSheet sheet = EasyExcel.writerSheet().build();
    
            Map<String,String> map = new HashMap<>();
    
            List<FillData> fillData = initData();
    
            FillConfig build = FillConfig.builder().forceNewRow(true).build();
    
            //填充单个数据
            map.put("date","2020-12-18");
            map.put("total",fillData.size() + "");
    
            //使用工作薄对象填充数据
            workBook.fill(fillData,build,sheet);
            workBook.fill(map,build,sheet);
    //        workBook.fill(fillData,sheet);
    //        workBook.fill(map,sheet);
    
            workBook.finish();
    
        }

此时已经正确填充  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUwNjU2_size_16_color_FFFFFF_t_70 4]

*  水平填充

测试代码

/**
         *  测试水平填充
         */
        @Test
        public void test04(){
        
            String template = "fill_data_template4.xlsx";
    
            //工作薄对象
            ExcelWriter workBook = EasyExcel.write("Excel-水平填充.xlsx", FillData.class).withTemplate(template).build();
    
            //工作区对象
            WriteSheet sheet = EasyExcel.writerSheet().build();
    
            Map<String,String> map = new HashMap<>();
    
            List<FillData> fillData = initData();
    
    //        设置水平填充
            FillConfig build = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
    
            //填充单个数据
            map.put("date","2020-12-18");
            map.put("total",fillData.size() + "");
    
            //使用工作薄对象填充数据
            workBook.fill(fillData,build,sheet);
            workBook.fill(map,build,sheet);
    
            workBook.finish();
    
        }

*  综合测试  
    先单组数据填充，再多组数据填充

/**
         * 测试复杂组合，当有复合填充时，如果单组和填充在多组合填充的下面，则会被覆盖，如模板3
         *
         *  否则要将其放在多组合填充上面，先把单组合填充完成，再填充多组合，这样就不会覆盖
         *
         */
        @Test
        public void test05(){
        
            String template = "report_template.xlsx";
    
            //创建workBook
            ExcelWriter workBook = EasyExcel.write("Excel-综合填充.xlsx", FillData.class).withTemplate(template).build();
    
            //创建Sheet
            WriteSheet sheet = EasyExcel.writerSheet().build();
    
            //准备单组和数据
            Map<String,String> map = new HashMap<>();
            map.put("date","2020-01-01");
            map.put("increaseCount","60");
            map.put("increaseCountWeek","7");
            map.put("totalCount","151");
            map.put("increaseCountMonth","22");
    
            List<User> users = initUsers();
    
            workBook.fill(map,sheet);
            workBook.fill(users,sheet);
    
            workBook.finish();
    
        }

效果图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUwNjU2_size_16_color_FFFFFF_t_70 5]  
这里没有被覆盖的原因是模板数据的单组数据写在了上面  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUwNjU2_size_16_color_FFFFFF_t_70 6]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUwNjU2_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20201218162307189.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUwNjU2,size_16,color_FFFFFF,t_70
[20201218162532864.png]: https://img-blog.csdnimg.cn/20201218162532864.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUwNjU2_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20201218162805460.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUwNjU2,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUwNjU2_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20201218162854466.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUwNjU2,size_16,color_FFFFFF,t_70
[202012181629337.png]: https://img-blog.csdnimg.cn/202012181629337.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUwNjU2_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20201218163254695.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUwNjU2,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUwNjU2_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20201218163625253.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUwNjU2,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUwNjU2_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20201218163757372.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUwNjU2,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUwNjU2_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20201218163857558.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUwNjU2,size_16,color_FFFFFF,t_70