记录一个开发过程中遇到的poi导出excel问题
一般的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();
}
5329
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
