java导出复杂的excel表格的方法(hutool)

旧城等待， 2022-12-04 01:06 345阅读 0赞

### maven ###

<dependency>
                <groupId>cn.hutool</groupId>
                <artifactId>hutool-all</artifactId>
                <version>4.5.1</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>4.0.1</version>
            </dependency>
            <dependency>
                <groupId>commons-net</groupId>
                <artifactId>commons-net</artifactId>
                <version>3.6</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>4.0.1</version>
            </dependency>

## 示例代码 ##

public static void main(String[] args) {
            getJdbcTemplate();
            List<Map<String, Object>> maps = jdbcTemplate.queryForList("select channel,paid_product,number_of_copies,total_paid_content,total_content,paid_percentage,total_number_of_pieces,total_episodes from summary_report");
            ExcelWriter writer = ExcelUtil.getWriter("D:\\windows\\Desktop\\1.xlsx");
            writer.setColumnWidth(-1, 20);
            Map<String, String> objectMap = new LinkedHashMap<>();
            objectMap.put("channel", "频道");
            objectMap.put("paid_product", "付费产品");
            objectMap.put("number_of_copies", "部数");
            objectMap.put("total_paid_content", "付费内容总量（部）");
            objectMap.put("total_content", "内容总量（部）");
            objectMap.put("paid_percentage", "付费占比（%）");
            objectMap.put("total_number_of_pieces", "片单总数");
            objectMap.put("total_episodes", "总集数");
            writer.setHeaderAlias(objectMap);
            layout(writer);
            writer.write(maps, true);
            // 关闭writer，释放内存
            writer.close();
    
        }
    
        public static void layout(ExcelWriter writer) {
            //数字从0开始算       前面两个数字是第几行到第几行合并    后面两个数字是第几列到第几列合并
            writer.merge(1, 4, 0, 0, null, true);
            writer.merge(5, 7, 0, 0, null, true);
            writer.merge(13, 18, 0, 0, null, true);
            writer.merge(1, 4, 3, 3, null, true);
            writer.merge(5, 7, 3, 3, null, true);
            writer.merge(13, 18, 3, 3, null, true);
            writer.merge(1, 4, 4, 4, null, true);
            writer.merge(5, 7, 4, 4, null, true);
            writer.merge(13, 18, 4, 4, null, true);
            writer.merge(1, 4, 5, 5, null, true);
            writer.merge(5, 7, 5, 5, null, true);
            writer.merge(13, 18, 5, 5, null, true);
            writer.merge(1, 11, 6, 6, null, true);
            writer.merge(12, 18, 6, 6, null, true);
            writer.merge(19, 24, 6, 6, null, true);
            writer.merge(1, 4, 7, 7, null, true);
            writer.merge(5, 7, 7, 7, null, true);
            writer.merge(13, 18, 7, 7, null, true);
        }

### 结果: ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTk0MTQ2_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTk0MTQ2_size_16_color_FFFFFF_t_70]: /images/20221123/f78c8505ef464446b5dc6ac28ed2d8f5.png