Java使用poi导出excel表格如何合并相同内容的行

2021-01-10 00:00:00 合并 导出 表格

记录一个开发过程中遇到的poi导出excel问题

一般的poi导出格式为以下这种
《Java使用poi导出excel表格如何合并相同内容的行》
这种实现起来也简单, 只需要配置好格式, 写好标题, 将数据循环导出到excel就行了 , 可是下面这种就比较难搞了
《Java使用poi导出excel表格如何合并相同内容的行》
由于第一列需要根据日期合并行, 而且行数不是固定的, 是需要根据上面的筛选条件, 查询出来, 将结果按照日期分组合并, 所以左边第一列需要合并的行是灵活多变的, 不能简单按照合并列那样。
下面是我的解决方案, 如有意见, 请多多指教
这是数据库里的数据
《Java使用poi导出excel表格如何合并相同内容的行》

实现逻辑和代码

1. 首先应该将数据按照日期排序, 查询出来

mapper.xml

    <select id="getDetail" resultType="com.XXX.pojo.dto.data.ActivityAnalysisDTO">
        select
        aa.*,
        cp.name as platform_name
        from activity_analysis aa
        left join cplatform cp on cp.cid = aa.platform_id
        <include refid="searchActivity"/>
        order by aa.create_time asc,aa.platform_id asc
    </select>
<sql id="searchActivity">
        <where>
            1=1
            <if test="startTime!=null and endTime!=null">
                and aa.create_time between #{startTime} and #{endTime}
            </if>
            <if test="activityId!=null">
                and aa.activity_id = #{activityId}
            </if>
            <if test="typeId!=null">
                and aa.type_id = #{typeId}
            </if>
            <if test="platformId!=null">
                and aa.platform_id = #{platformId}
            </if>
        </where>
    </sql>

我这里直接使用的查询这个活动数据的接口
这里主要是想和活动详情使用同样的接口, 日后修改起来, 也方便些(也可使用单独查询列表的接口)
service
由于HashMap集合中key是无序的, 所以, 在这里我将HashMap转为LinkedHashMap, 有序

public Map<String, List<ActivityAnalysisDTO>> getDetail(SearchActivityArgs args) { 
        //1.查找活动详情
        List<ActivityAnalysisDTO> activityAnalysisDTOList = activityAnalysisMapper.getDetail(args);
        Map<String, List<ActivityAnalysisDTO>> mapStrKey = new HashMap<>();
        //将Date类型的key转化为String 类型的key, 将数据按照创建日期分组, 构建
        if (CollectionUtils.isNotEmpty(activityAnalysisDTOList)) { 
            Map<Date, List<ActivityAnalysisDTO>> listMap = activityAnalysisDTOList.stream().collect(Collectors.groupingBy(o -> o.getCreateTime()));
            for (Date date : listMap.keySet()) { 
                mapStrKey.put(DateUtils.getStringFromDate(date, "yyyy-MM-dd"), listMap.get(date));
            }
        }
        LinkedHashMap<String, List<ActivityAnalysisDTO>> sortMap = this.sortKeyMap(mapStrKey);
        return sortMap;
    }

    /**
     * 给map集合按照key排序
     *
     * @param oriMap
     * @return
     */
    private LinkedHashMap<String, List<ActivityAnalysisDTO>> sortKeyMap(Map<String, List<ActivityAnalysisDTO>> oriMap) { 
        LinkedHashMap<String, List<ActivityAnalysisDTO>> sortKeyMap = new LinkedHashMap<>();
        if (oriMap != null) { 
            Set<String> set = oriMap.keySet();
            Object[] array = set.toArray();
            //数组排序
            Arrays.sort(array);
            for (Object o : array) { 
                sortKeyMap.put((String) o, oriMap.get(o));
            }
        }
        return sortKeyMap;
    }

postman查询出的结果

{ 
    "code": 200,
    "msg": "操作成功",
    "data": { 
        "2020-07-10": [
            { 
                "id": 2,
                "activityId": "1",
                "title": " 清仓大甩卖啦2",
                "typeId": 1,
                "typeName": "营销活动类型1",
                "platformId": 1,
                "platformName": "APP",
                "activityPv": 111,
                "activityUv": 2,
                "joinTotal": 11,
                "cluesTotal": 11,
                "shareTotal": 11,
                "createTime": "2020-07-10"
            },
            { 
                "id": 1,
                "activityId": "1",
                "title": "清仓大甩卖啦1",
                "typeId": 1,
                "typeName": "营销活动类型1",
                "platformId": 1,
                "platformName": "APP",
                "activityPv": 11,
                "activityUv": 1,
                "joinTotal": 11,
                "cluesTotal": 11,
                "shareTotal": 11,
                "createTime": "2020-07-10"
            },
            { 
                "id": 3,
                "activityId": "1",
                "title": "清仓大甩卖啦3",
                "typeId": 2,
                "typeName": "营销活动类型2",
                "platformId": 2,
                "platformName": "官网",
                "activityPv": 11111,
                "activityUv": 3,
                "joinTotal": 11,
                "cluesTotal": 11,
                "shareTotal": 11,
                "createTime": "2020-07-10"
            },
            { 
                "id": 4,
                "activityId": "1",
                "title": "清仓大甩卖啦4",
                "typeId": 3,
                "typeName": "营销活动类型3",
                "platformId": 3,
                "platformName": "小程序",
                "activityPv": 11234,
                "activityUv": 4,
                "joinTotal": 11,
                "cluesTotal": 11,
                "shareTotal": 11,
                "createTime": "2020-07-10"
            }
        ],
        "2020-07-11": [
            { 
                "id": 5,
                "activityId": "1",
                "title": " 清仓大甩卖啦5",
                "typeId": 4,
                "typeName": "营销活动类型4",
                "platformId": 4,
                "platformName": "服务号",
                "activityPv": 111,
                "activityUv": 5,
                "joinTotal": 11,
                "cluesTotal": 11,
                "shareTotal": 11,
                "createTime": "2020-07-11"
            }
        ],
        "2020-07-12": [
            { 
                "id": 6,
                "activityId": "1",
                "title": "清仓大甩卖啦6",
                "typeId": 1,
                "typeName": "营销活动类型1",
                "platformId": 1,
                "platformName": "APP",
                "activityPv": 1221,
                "activityUv": 6,
                "joinTotal": 111,
                "cluesTotal": 11,
                "shareTotal": 11,
                "createTime": "2020-07-12"
            },
            { 
                "id": 7,
                "activityId": "1",
                "title": "清仓大甩卖啦7",
                "typeId": 2,
                "typeName": "营销活动类型2",
                "platformId": 2,
                "platformName": "官网",
                "activityPv": 1221,
                "activityUv": 7,
                "joinTotal": 11,
                "cluesTotal": 11,
                "shareTotal": 11,
                "createTime": "2020-07-12"
            },
            { 
                "id": 8,
                "activityId": "1",
                "title": "清仓大甩卖啦8",
                "typeId": 3,
                "typeName": "营销活动类型3",
                "platformId": 3,
                "platformName": "小程序",
                "activityPv": 1221,
                "activityUv": 8,
                "joinTotal": 111,
                "cluesTotal": 11,
                "shareTotal": 11,
                "createTime": "2020-07-12"
            },
            { 
                "id": 9,
                "activityId": "1",
                "title": "清仓大甩卖啦9",
                "typeId": 4,
                "typeName": "营销活动类型4",
                "platformId": 4,
                "platformName": "服务号",
                "activityPv": 1221,
                "activityUv": 9,
                "joinTotal": 11,
                "cluesTotal": 11,
                "shareTotal": 11,
                "createTime": "2020-07-12"
            }
        ],
        ]
    },
    "size": null,
    "total": null
}

可以看出已经按照数据格式展现出来
接下来

 @ApiOperation("导出营销活动详情")
    @GetMapping("/getDetail/export")
    public void exportActivityDetail(@ApiParam("筛选条件") SearchActivityArgs args, HttpServletResponse response) throws IOException { 
        Map<String, List<ActivityAnalysisDTO>> detailMap = activityAnalysisService.getDetail(args);
        if (CollectionUtils.isEmpty(detailMap)) { 
            return;
        }
        List<String> dateStrList = new ArrayList<>();
        List<ActivityAnalysisDTO> detailList = new ArrayList<>();
        int dateGroup = detailMap.size();
        for (String date : detailMap.keySet()) { 
            dateStrList.add(date);
            List<ActivityAnalysisDTO> activityAnalysisDTOList = detailMap.get(date);
            detailList.addAll(activityAnalysisDTOList);
        }
//        //进行排序, 按照指定字段升序排列
//        Collections.sort(detailList, new Comparator<ActivityAnalysisDTO>() { 
//            @Override
//            public int compare(ActivityAnalysisDTO o1, ActivityAnalysisDTO o2) { 
//                return o1.getCreateTime().compareTo(o2.getCreateTime());
//            }
//        });

        //获取活动标题名称
        String activityTitle = detailList.get(0).getTitle();
        List<String> titles = Lists.newArrayList();
        titles.add("日期");
        titles.add("平台");
        titles.add("活动PV");
        titles.add("活动UV");
        titles.add("参与量");
        titles.add("线索量");
        titles.add("分享量");
        HSSFWorkbook result = new HSSFWorkbook();
        HSSFSheet sheet = result.createSheet(activityTitle);
        HSSFRow row = sheet.createRow(0);
        HSSFCellStyle style = result.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        HSSFCell cell = null;
        //创建标题
        for (int i = 0; i < titles.size(); i++) { 
            cell = row.createCell(i);
            cell.setCellValue(titles.get(i));
            cell.setCellStyle(style);
        }
        //创建内容
        List<String> tempDate = new ArrayList<>();
        //初始化起始行和结束行
        int startRow = 1;
        int endRow = 1;
        //从第一行开始
        int temNum = 1;
        //当前日期
        String lastDate = "";
        //用来记录是否是第一次循环
        boolean flag = true;
        for (int i = 0; i < detailList.size(); i++) { 
            //利用相同日期合并单元格(合并行)
            String currentDate = DateUtils.getStringFromDate(detailList.get(i).getCreateTime(), "yyyy-MM-dd");
            //int firstRow 第几行开始, int lastRow 第几行结束, int firstCol 第几个单元格开始, int lastCol 第几个单元格结束
            if (flag) { 
                //如果是第一次循环,将lastDate初始化,以便后面的第一次判断
                lastDate = currentDate;
                flag = false;
            }
            if (i != 0 && lastDate.equals(currentDate)) { 
                endRow++;
            } else { 
                //两者不相同时,融合之前相同的行
                if (endRow > startRow) { 
                    sheet.addMergedRegion(new CellRangeAddress(startRow, endRow, 0, 0));
                }
                startRow = temNum;
                endRow = startRow;
                lastDate = currentDate;
            }
            temNum++;
            if (i==detailList.size()-1&&startRow!=endRow) { 
                //融合最后相同的行
                sheet.addMergedRegion(new CellRangeAddress(startRow, endRow, 0, 0));
            }
            row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(currentDate);
            row.createCell(1).setCellValue(detailList.get(i).getPlatformName());
            row.createCell(2).setCellValue(detailList.get(i).getActivityUv());
            row.createCell(3).setCellValue(detailList.get(i).getActivityPv());
            row.createCell(4).setCellValue(detailList.get(i).getJoinTotal());
            row.createCell(5).setCellValue(detailList.get(i).getCluesTotal());
            row.createCell(6).setCellValue(detailList.get(i).getShareTotal());
        }
        if (result == null) { 
            return;
        }
//        String activityTitleXls =
        String fileName = new String("活动详情.xls".getBytes(), "ISO8859-1");
        response.setContentType("application/octet-stream;charset=ISO8859-1");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        response.addHeader("Pargam", "no-cache");
        response.addHeader("Cache-Control", "no-cache");
        OutputStream osw = response.getOutputStream();
        result.write(osw);
        osw.flush();
        osw.close();
    }
    原文作者:意田天
    原文地址: https://blog.csdn.net/A_Java_Dog/article/details/107311951
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系博主进行删除。

相关文章