最近在实习做了一个业务报表平台,要求是不用改前后端代码,在数据库里写SQL等信息就能实习页面新增并可以使用。在做到最后一个导出excel需求的时候发现Excel表头是不规则的,就像下面这样:
区域 | 项目 | 可出租 | |||||||
本月 | 上月 | 环比可租面积变化 | 去年同期 | 同比可租面积变化 | |||||
铺位数 | 面积 | 铺位数 | 面积 | 铺位数 | 面积 |
给到我们的数据主要只有三个:id(表头id)、parentId(父表头id)、name(表头名称),我们需要去拼装这样一个表头。(下文表头与标题同义)
刚开始我觉得是不可实现的,也百度不到相关的内容,由于ddl很短,就找领导说了一下,最后改成“可出租_本月_铺位数”这种的一级表头。最近思考了一下,这些信息理论上是足够我们去得到这样一个表头的,就尝试着手去做了一下,也是实现出来了。
先导个poi依赖
<!-- excel -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
我的思路是这样的,创建一个表头对象,包括其基础属性的同时,定义一个List<ExcelTitle>存储下级标题,数据结构就是一个多叉树,如下:
//标题对象
@Data
public class ExcelTitle {
private int id;
private String name;
private int parentId;
private boolean isProp;//是否是最底层标题
private int width;//单元格宽度
private List<ExcelTitle> excelTitleList = new LinkedList<>();//子标题列表
public ExcelTitle(int id , String name, int parentId, boolean isProp){
this.id = id;
this.name = name;
this.parentId = parentId;
this.isProp = isProp;
this.width = name.length() > 5 ? name.length() * 500 : 2500;
}
//返回子标题数量,没有则返回1
public int getChildNums() {
if (excelTitleList.isEmpty()) return 1;
int sum = 0;
for (ExcelTitle excelTitle : excelTitleList){
sum += excelTitle.getChildNums();
}
return sum;
}
//添加子标题
public void addChild(ExcelTitle excelTitle){
excelTitleList.add(excelTitle);
}
//我的项目有要求只导出某几列,会出现A的子标题都不导出,但A还在这个List中,会被导出,用这个函数删除A
public void titleArrange(){
if (!excelTitleList.isEmpty())
excelTitleList.removeIf(excelTitle -> !excelTitle.isProp && excelTitle.getExcelTitleList().isEmpty());
}
/**
*
* @param firstRow 起始行
* @param firstCol 起始列
* @param maxDeep 标题最大等级
* @param sheet 需要操作的表
* @return 操作完成的表
*/
//将这个标题写入单元格,并执行合并单元格操作(核心操作)
public XSSFSheet setValue(int firstRow, int firstCol, int maxDeep, XSSFSheet sheet){
XSSFWorkbook workbook = sheet.getWorkbook();
CellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(org.apache.poi.ss.usermodel.VerticalAlignment.CENTER);
XSSFRow row = sheet.getRow(firstRow);
XSSFCell cell = row.createCell(firstCol);
cell.setCellValue(name);
cell.setCellStyle(style);
if (!excelTitleList.isEmpty()){
sheet.addMergedRegion(new CellRangeAddress(firstRow, firstRow, firstCol, firstCol + this.getChildNums() - 1));
for (ExcelTitle excelTitle : excelTitleList){
excelTitle.setValue(firstRow + 1, firstCol, maxDeep, sheet);
firstCol = firstCol + excelTitle.getChildNums();
}
}else {
sheet.setColumnWidth(firstCol,width);
if (firstRow != maxDeep - 1 || this.getChildNums() != 1)
sheet.addMergedRegion(new CellRangeAddress(firstRow, maxDeep - 1, firstCol, firstCol + this.getChildNums() - 1));
}
return sheet;
}
}
为其定义构造器与一些方法:
- getChildNums:计算有几个最底层子标题。假设A的直属子标题为B、C,B的直属子标题为E、F,C没有。那么A的最底层子标题为3(C、E、F);
- addChild:添加直属子标题;
- titleArrange:删除非最底层子标题且没有子标题(无需导出)的标题;
- setValue:数据表表头拼接函数。
- 表头位置定位:我们每次递归,就是深入到下一级子标题,那么就要往下一行,也就是firstRow+1,每次单元格操作完成,我们需要往后移动到下一个该操作的列位置,也就是当前位置firstCol加上已操作的位置的数量execlTitle.getChildNums()。
- 合并单元格:合并单元格是根据子标题数量去合并的,如果没有下级标题了,说明这个已经是最下级标题,我们合并当前行到最底行,列不变;如果还有子标题,那么合并当前列到往后i-1(子标题数量-1)的列位置,行不变。
整体思路是构建多叉树结构,使用DFS算法对其进行先序遍历,逐个操作单元格完成的。
接下来展示一部分我的业务代码,对大家没帮助的我已经删了一部分了。
最主要是要获取ExcelTitle的一级标题集合,放入List中,再遍历这个List,通过setValue方法就能生成多级标题了。
@Component
public class DynamicExcelUtil {
public XSSFWorkbook creatExcel(List<Map<String,Object>> colnumList, List<String> requiredHeader, List<Map<String,Object>> dataList){
ExcelTitleRequire excelTitleRequire = getExcelTitleRequire(colnumList, requiredHeader);//业务代码,用于后续导出需要的列
Map<Integer, ExcelTitle> excelTitleMap = getExcelTitleMap(colnumList, excelTitleRequire.getIdSet());//首先需要获取我们所有标题对象的集合
List<String> tableHeaderType = excelTitleRequire.getTableHeaderType();
int sumCol = 0;
//计算出最多是几级标题
int maxDeep = titleRank(excelTitleMap);
//定义一个迭代器,遍历集合并删除非一级标题(最顶上的,如上图“可出租”)
Iterator<Map.Entry<Integer, ExcelTitle>> iterator = excelTitleMap.entrySet().iterator();
while (iterator.hasNext()) {
Map.Entry<Integer, ExcelTitle> entry = iterator.next();
ExcelTitle excelTitle = entry.getValue();
if (excelTitle.getParentId() != 0) {
iterator.remove();
}else {
//如果是一级标题,删除其中不需要导出的列
excelTitle.titleArrange();
sumCol = sumCol + excelTitle.getChildNums();
}
}
XSSFWorkbook excel = new XSSFWorkbook();
CellStyle style = excel.createCellStyle();
style.setDataFormat(excel.createDataFormat().getFormat("0.00%"));
XSSFSheet sheet = excel.createSheet("Sheet1");
//先将标题需要的行都创建出来
for (int i = 0; i < maxDeep; i++){
sheet.createRow(i);
}
int col = 0;
//遍历一级表头,在col起始的列写入数据,col向后移动子节点数的位置
for (ExcelTitle excelTitle : excelTitleMap.values()){
sheet = excelTitle.setValue(0, col, maxDeep, sheet);
col = col + excelTitle.getChildNums();
}
//以下代码用于存入业务数据,大家根据自己的需求自定义
int dataRow = maxDeep;
for (Map<String,Object> data : dataList){
//......
}
return excel;
}
//用于计算最多几级标题
private int titleRank(Map<Integer, ExcelTitle> excelTitleMap){
int maxDeep = 1;
for (ExcelTitle excelTitle : excelTitleMap.values()){
int deep = 1;
while(excelTitle.getParentId() != 0){
excelTitle = excelTitleMap.get(excelTitle.getParentId());
deep++;
}
maxDeep = Math.max(maxDeep,deep);
}
return maxDeep;
}
//获取全部标题集合
private Map<Integer, ExcelTitle> getExcelTitleMap(List<Map<String,Object>> colnumList, Set<Integer> idSet){
Map<Integer, ExcelTitle> excelTitleMap = new LinkedHashMap<>();
for (Map<String,Object> colnum : colnumList){
int id = Integer.parseInt(colnum.get("id").toString());
if (!idSet.contains(id)) continue;
String name = colnum.get("name").toString();
int parentId = Integer.parseInt(colnum.get("parentId").toString());
ExcelTitle excelTitle = new ExcelTitle(id, name, parentId, colnum.containsKey("prop"));
excelTitleMap.put(excelTitle.getId(),excelTitle);
if (excelTitle.getParentId() != 0){
excelTitleMap.get(excelTitle.getParentId()).addChild(excelTitle);
}
}
return excelTitleMap;
}
//业务代码,获取需要打印的列,不通用,请大家自行定义
private ExcelTitleRequire getExcelTitleRequire(List<Map<String,Object>> colnumList, List<String> requiredTableHeader){
Set<Integer> idSet = new HashSet<>();//需要打印的列id(全部非最底层标题+需要的最底层标题)
List<String> tableHeaderType = new ArrayList<>();
//......
return new ExcelTitleRequire(tableHeaderType, idSet);
}
}
最后给大家简单展示一下我的colnumList的组成:
"colnumList": [
{
"isSort": true,
"conditionFormat": [],
"headingAlign": "center",
"isCross": false,
"contType": "string",//字段类型
"sort": 1,
"align": "center",
"parentId": 0,
"isDisplay": true,
"isShow": true,
"fixedAlign": "left",
"prop": "areaName",//字段名
"name": "区域",
"width": 80,
"id": 139,
"isFixed": true
},
{
//......
}
],
大学马上毕业了,大家有好工作麻烦给我推一推T_T,最好base浙江,当然,有钱我去哪都行。