《Dubbo 实现原理与源码解析 —— 精品合集》 《Netty 实现原理与源码解析 —— 精品合集》
《Spring 实现原理与源码解析 —— 精品合集》 《MyBatis 实现原理与源码解析 —— 精品合集》
《Spring MVC 实现原理与源码解析 —— 精品合集》 《数据库实体设计合集》
《Spring Boot 实现原理与源码解析 —— 精品合集》 《Java 面试题 + Java 学习指南》

摘要: 原创出处 blog.csdn.net/a656678879/article/details/88050789/ 「a656678879」欢迎转载,保留摘要,谢谢!


🙂🙂🙂关注**微信公众号:【芋道源码】**有福利:

  1. RocketMQ / MyCAT / Sharding-JDBC 所有源码分析文章列表
  2. RocketMQ / MyCAT / Sharding-JDBC 中文注释源码 GitHub 地址
  3. 您对于源码的疑问每条留言将得到认真回复。甚至不知道如何读源码也可以请教噢
  4. 新的源码解析文章实时收到通知。每周更新一篇左右
  5. 认真的源码交流微信群。

说明

  • 使用的是easypoi进行导出
  • 行头是动态生成
  • 依据key进行列匹配,进行数据填充
  • 第一列进行纵向动态合并

自己的一个使用,记录一下

工具依赖

<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.2.0</version>
</dependency>

实现效果

变更前样式

变更后样式

代码解析

动态生成列头

 private List<ExcelExportEntity> dynamicNewAddExcel(Map<String, PlatformStatisParamRespData> paramInfo) {
//表头的集合,用于添加表头
List<ExcelExportEntity> entityList = new ArrayList<>();

//ExcelExportEntity构造参数【第一个是列名头的统计字段,第二个是需要指定的一个key在填充数据的时候是需要根据这个key进行填充值,第三个参数是列宽】
ExcelExportEntity platformXh = new ExcelExportEntity("统计字段1", "statisKey1", 30);
//列的合并(纵向列的同名称会进行合并,效果见上图的平台名称的变化)
platformXh.setMergeVertical(true);
entityList.add(platformXh);

ExcelExportEntity statisDateXh = new ExcelExportEntity("统计字段2", "statisKey2", 30);
entityList.add(statisDateXh);

//参数信息--[用于动态拼接列头]
final Iterator<String> iterator = paramInfo.keySet().iterator();
while (iterator.hasNext()) {
final String paramKeyStr = iterator.next();
final String paramNameStr = paramInfo.get(paramKeyStr).getDataName();
//列头由参数汉字名称,参数key为列key
entityList.add(new ExcelExportEntity(paramNameStr, paramKeyStr, 30));
}
return entityList;
}

动态填充数据

private List<Map<String, Object>> dynamicListDataByKey(List<PlatformIncomeRespDTO> statisData) {
//参数类型
final Set<String> statisParamKey = statisData.get(0).getParamInfo().keySet();
final List<String> statisDate = statisData.get(0).getStatisDate();
final int platformNum = statisData.size();

//最终的数据
List<Map<String, Object>> datas = new ArrayList<>();
for (int i = 0; i < platformNum; i++) {
for (int j = 0; j < statisDate.size(); j++) {
Map<String, Object> hashMap = new LinkedHashMap<>(10);
//这个是依据key进行数据的填充,(根据前面填写的statisKey1进行填充数据)
hashMap.put("statisKey1", statisData.get(i).getPlatformNickName());
String statisDateStr = statisDate.get(j);
//这个是依据key进行数据的填充,(根据前面填写的statisKey2进行填充数据)
hashMap.put("statisKey2", statisDateStr);
//参数的验证
for (String paramKey : statisParamKey) {
for (BiPlatformStatisRespDTO paramData : statisData.get(i).getStatisData().get(j)) {
if (paramKey.equals(paramData.getParamKey())) {
hashMap.put(paramData.getParamKey(), paramData.getValue() + "(" + paramData.getRateValue() + ")");
}
}
}
datas.add(hashMap);
}
}
return datas;
}

excel的导出

//statisData就是我们查询出来的数据
public void downloadPlatformIncomeContrast(List<PlatformIncomeRespDTO> statisData, HttpServletResponse response) {
if (CollectionUtils.isEmpty(statisData)) {
return;
}
//获取参数信息
final Map<String, PlatformStatisParamRespData> paramInfo = statisData.get(0).getParamInfo();

//拼装列头
List<ExcelExportEntity> colList = this.dynamicNewAddExcel(paramInfo);

//数据拼装
List<Map<String, Object>> list = this.dynamicListDataByKey(statisData);
final String xlsFileName = DateHelper.getNowString(FormatUnit.yyyyMMddHHmmss, true) + ".xls";
final Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), colList, list);

//动态合并纵列[mergeMap key列索引(从0开始),value依赖的列,没有传空,startRow 开始行(从零开始)]
//Map<Integer, int[]> mer = new HashMap<>();
//mer.put(0, new int[]{});
//PoiMergeCellUtil.mergeCells(workbook.getSheetAt(0), mer, 1);
EasypoiUtil.downLoadExcel(xlsFileName, response, workbook);
}

EasypoiUtil工具类

public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
workbook.write(response.getOutputStream());
} catch (IOException e) {
throw new RuntimeException(e.getMessage());
}
}

PlatformIncomeRespDTO

@Data
@NoArgsConstructor
@AllArgsConstructor
public class PlatformIncomeRespDTO implements Serializable {

private static final long serialVersionUID = 1100499105160261425L;


/**
* 平台别名
*/
private String platformNickName;

/*统计时间*/
private List<String> statisDate;

/*查询参数信息--[用户收入来源统计导出使用]*/
private Map<String, PlatformStatisParamRespData> paramInfo;


/*统计数据*/
private List<List<BiPlatformStatisRespDTO>> statisData;
}

PlatformStatisParamRespData

@Data
@NoArgsConstructor
@AllArgsConstructor
public class PlatformStatisParamRespData implements Serializable {

private static final long serialVersionUID = 4263523446154995471L;

/**
* 参数名称
*/
private String dataName;

/**
* 参数key
*/
private String dateKey;

/**
* 参数描述
*/
private String dateDesc;

}

BiPlatformStatisRespDTO

@Data
@AllArgsConstructor
public class BiPlatformStatisRespDTO implements Serializable {

private static final long serialVersionUID = 6070471415344415351L;

@Excel(name = "统计字段", orderNum = "1")
private String param;

/**
* 参数的key
*/
private String paramKey;

/**
* 参数描述
*/
private String paramDesc;

private String startDate;

private String endDate;

@Excel(name = "统计数据", orderNum = "2")
private String value;

private String rateValue;

private Long id;

private Integer riseOrFall;

public BiPlatformStatisRespDTO(String startDate, String paramKey, String value) {
this.paramKey = paramKey;
this.startDate = startDate;
this.value = value;
}

public BiPlatformStatisRespDTO() {
}
}

测试用例

测试特殊说明

导出的结果有个控制,是在拼装的时候没有填充此数据,不影响整体效果

测试结果示例

测试数据json示例

[
{
"paramInfo": {
"userCount": {
"dataName": "用户数",
"dateDesc": "用户信息",
"dateKey": "userCount"
},
"friendsCount": {
"dataName": "好友数",
"dateDesc": "好友信息",
"dateKey": "friendsCount"
}
},
"platformNickName": "aaa",
"statisData": [
[
{
"paramKey": "userCount",
"startDate": "2019-12-26",
"value": "100"
},
{
"paramKey": "friendsCount",
"startDate": "2019-12-26",
"value": "200"
}
],
[
{
"paramKey": "userCount",
"startDate": "2019-12-27",
"value": "300"
},
{
"paramKey": "friendsCount",
"startDate": "2019-12-27",
"value": "400"
}
]
],
"statisDate": [
"2019-12-26",
"2019-12-27"
]
},
{
"paramInfo": {
"userCount": {
"dataName": "用户数",
"dateDesc": "用户信息",
"dateKey": "userCount"
},
"friendsCount": {
"dataName": "好友数",
"dateDesc": "好友信息",
"dateKey": "friendsCount"
}
},
"platformNickName": "bbb",
"statisData": [
[
{
"paramKey": "userCount",
"startDate": "2019-12-26",
"value": "500"
},
{
"paramKey": "friendsCount",
"startDate": "2019-12-26",
"value": "600"
}
],
[
{
"paramKey": "userCount",
"startDate": "2019-12-27",
"value": "700"
},
{
"paramKey": "friendsCount",
"startDate": "2019-12-27",
"value": "800"
}
]
],
"statisDate": [
"2019-12-26",
"2019-12-27"
]
}
]

测试用例代码

public class Simple {

/**
* @Description: 拼接表头
* @Param: paramInfo :表头信息
* @return: java.util.List<cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity>
* @Author: peikunkun
* @Date: 2019/12/26 0026 上午 10:42
*/
private static List<ExcelExportEntity> dynamicNewAddExcel(Map<String, PlatformStatisParamRespData> paramInfo) {
//表头的集合,用于添加表头
List<ExcelExportEntity> entityList = new ArrayList<>();

//ExcelExportEntity构造参数【第一个是列名头的统计字段,第二个是需要指定的一个key在填充数据的时候是需要根据这个key进行填充值,第三个参数是列宽】
ExcelExportEntity platformXh = new ExcelExportEntity("统计字段1", "statisKey1", 30);
//列的合并(纵向列的同名称会进行合并,效果见上图的平台名称的变化)
platformXh.setMergeVertical(true);
entityList.add(platformXh);

ExcelExportEntity statisDateXh = new ExcelExportEntity("统计字段2", "statisKey2", 30);
entityList.add(statisDateXh);

//参数信息--[用于动态拼接列头]
final Iterator<String> iterator = paramInfo.keySet().iterator();
while (iterator.hasNext()) {
final String paramKeyStr = iterator.next();
final String paramNameStr = paramInfo.get(paramKeyStr).getDataName();
//列头由参数汉字名称,参数key为列key
entityList.add(new ExcelExportEntity(paramNameStr, paramKeyStr, 30));
}
return entityList;
}


/**
* @Description: 拼接数据
* @Param: statisData :拼接数据
* @Author: peikunkun
* @Date: 2019/12/26 0026 上午 10:42
*/
private static List<Map<String, Object>> dynamicListDataByKey(List<PlatformIncomeRespDTO> statisData) {
//参数类型
final Set<String> statisParamKey = statisData.get(0).getParamInfo().keySet();
final List<String> statisDate = statisData.get(0).getStatisDate();
final int platformNum = statisData.size();

//最终的数据
List<Map<String, Object>> datas = new ArrayList<>();
for (int i = 0; i < platformNum; i++) {
for (int j = 0; j < statisDate.size(); j++) {
Map<String, Object> hashMap = new LinkedHashMap<>(10);
//这个是依据key进行数据的填充,(根据前面填写的statisKey1进行填充数据)
hashMap.put("statisKey1", statisData.get(i).getPlatformNickName());
String statisDateStr = statisDate.get(j);
//这个是依据key进行数据的填充,(根据前面填写的statisKey2进行填充 数据)
hashMap.put("statisKey2", statisDateStr);
//参数的验证
for (String paramKey : statisParamKey) {
for (BiPlatformStatisRespDTO paramData : statisData.get(i).getStatisData().get(j)) {
if (paramKey.equals(paramData.getParamKey())) {
hashMap.put(paramData.getParamKey(), paramData.getValue() + "(" + paramData.getRateValue() + ")");
}
}
}
datas.add(hashMap);
}
}
return datas;
}


@Test
public void Administrator_84_20191226095523() throws IOException {
System.out.println("欢迎使用单元测试方法【Administrator_84()_20191226095523】");
System.out.println("此方法测试描述:【】");
//拼装第一个数据---------------------------------------------------------------------
final PlatformIncomeRespDTO platformIncomeRespDTO1 = new PlatformIncomeRespDTO();
platformIncomeRespDTO1.setPlatformNickName("aaa");
//拼装时间维度
platformIncomeRespDTO1.setStatisDate(Lists.newArrayList("2019-12-26","2019-12-27"));
//拼装头信息
Map<String, PlatformStatisParamRespData> paramInfo1=new HashMap<>();
paramInfo1.put("userCount", new PlatformStatisParamRespData("用户数","userCount","用户信息"));
paramInfo1.put("friendsCount", new PlatformStatisParamRespData("好友数","friendsCount","好友信息"));
platformIncomeRespDTO1.setParamInfo(paramInfo1);
//拼装数据
final ArrayList<List<BiPlatformStatisRespDTO>> data1 = Lists.newArrayList();
data1.add(Lists.newArrayList(new BiPlatformStatisRespDTO("2019-12-26","userCount","100"),new BiPlatformStatisRespDTO("2019-12-26","friendsCount","200")));
data1.add(Lists.newArrayList(new BiPlatformStatisRespDTO("2019-12-27","userCount","300"),new BiPlatformStatisRespDTO("2019-12-27","friendsCount","400")));
platformIncomeRespDTO1.setStatisData(data1);


//拼装第二个数据---------------------------------------------------------------------
final PlatformIncomeRespDTO platformIncomeRespDTO2 = new PlatformIncomeRespDTO();
platformIncomeRespDTO2.setPlatformNickName("bbb");
//拼装时间维度
platformIncomeRespDTO2.setStatisDate(Lists.newArrayList("2019-12-26","2019-12-27"));
//拼装头信息
Map<String, PlatformStatisParamRespData> paramInfo2=new HashMap<>();
paramInfo2.put("userCount", new PlatformStatisParamRespData("用户数","userCount","用户信息"));
paramInfo2.put("friendsCount", new PlatformStatisParamRespData("好友数","friendsCount","好友信息"));
platformIncomeRespDTO2.setParamInfo(paramInfo2);

//拼装数据
final ArrayList<List<BiPlatformStatisRespDTO>> data2 = Lists.newArrayList();
data2.add(Lists.newArrayList(new BiPlatformStatisRespDTO("2019-12-26","userCount","500"),new BiPlatformStatisRespDTO("2019-12-26","friendsCount","600")));
data2.add(Lists.newArrayList(new BiPlatformStatisRespDTO("2019-12-27","userCount","700"),new BiPlatformStatisRespDTO("2019-12-27","friendsCount","800")));
platformIncomeRespDTO2.setStatisData(data2);

final ArrayList<PlatformIncomeRespDTO> platformIncomeRespDTOS = Lists.newArrayList(platformIncomeRespDTO1, platformIncomeRespDTO2);
System.out.println(JSONObject.toJSONString(platformIncomeRespDTOS));

//拼装列头
List<ExcelExportEntity> colList = dynamicNewAddExcel(paramInfo2);

//数据拼装
List<Map<String, Object>> list = dynamicListDataByKey(platformIncomeRespDTOS);

//文件名称
final Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), colList, list);

//此功能与【拼装列头】中的 platformXh.setMergeVertical(true);功能效果一样,可直接使用 platformXh.setMergeVertical(true);进行纵向合并
//动态合并纵列[mergeMap key列索引(从0开始),value依赖的列,没有传空,startRow 开始行(从零开始)]
//Map<Integer, int[]> mer = new HashMap<>();
//mer.put(0, new int[]{});
//PoiMergeCellUtil.mergeCells(workbook.getSheetAt(0), mer, 1);

final FileOutputStream fileOutputStream = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\1.xls");
//导出excel
downLoadExcel(null, fileOutputStream, workbook);
}




/**
* @Description: 下载文件
* @Param: fileName
* @Param outputStream
* @Param workbook
* @return: void
* @Author: peikunkun
* @Date: 2019/12/26 0026 上午 10:44
*/
public static void downLoadExcel(String fileName, FileOutputStream outputStream, Workbook workbook)
throws IOException {
try {
workbook.write(outputStream);
} catch (IOException e) {
throw new RuntimeException(e.getMessage());
} finally {
outputStream.close();
}
}
}

文章目录
  1. 1. 说明
  2. 2. 工具依赖
  3. 3. 实现效果
  4. 4. 代码解析
    1. 4.0.1. 动态生成列头
    2. 4.0.2. 动态填充数据
    3. 4.0.3. excel的导出
  • 5. 测试用例
    1. 5.0.1. 测试特殊说明
    2. 5.0.2. 测试结果示例
    3. 5.0.3. 测试数据json示例
    4. 5.0.4. 测试用例代码