一、背景
在业务开发过程中,遇到有需要生成包含表格的word文档,且一部分表格需要动态生成,且需要根据数据来合并单元格,最后呈现的方式如下图:
一开始想到的解决方案是通过freemarker来生成,但是需要转xml生成模板,过程比较复杂,因此,在查阅一些资料后,最终选择了poi-tl来实现。相比于freemarker,poi-tl导出word的好处在于可以直接使用word模板,比较直观,且比较好调整格式。
二、实现
1、引入jar包
<!-- word导出 -->
<dependency>
<groupId>com.deepoove</groupId>
<artifactId>poi-tl</artifactId>
<version>1.7.3</version>
</dependency>
<!-- 上面需要的依赖-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
2、构造word模板
1)简单包含属性的表格模板
姓名 | {{name}} | 年龄 | {{age}} |
身份证号 | {{idCardNo}} | 家庭住址 | {{addr}} |
联系电话 | {{tel}} | 学历 | {{education}} |
是否党员 | {{isPartyMember}} | 毕业院校 | {{school}} |
紧急联系人 | {{contactPer}} | 紧急联系方式 | {{contactTel}} |
邮箱 | {{email}} |
2)需要循环list填充的表格模板
{{userList}} 姓名 | 性别 | 年龄 |
[name] | [sex] | [age] |
3)需要动态合并单元格的表格模板
{{userList}}班级 | 小组 | 姓名 | 学号 |
合并单元格操作需要在代码实现
3、代码实现合并单元格
public void export(HttpServletResponse response, SysDefineMethod sysDefineMethod) {
Map<String, Object> dataMap = new HashMap<>();
//企业基本信息
dataMap.put("baseInfo", baseInfo);
//机组信息
dataMap.put("unitList", unitList);
//人员信息
dataMap.put("userList", userList);
ClassPathResource classPathResource = new ClassPathResource("templates/template.docx");
String resource = classPathResource.getUrl().getPath();
//给需要单独处理的表格绑定处理策略
Configure config = Configure.newBuilder()
.bind("unitList", new DefineMethodPolicy())
.build();
XWPFTemplate template = XWPFTemplate.compile(resource, config).render(dataMap);
// 生成的word格式
String formatSuffix = ".docx";
// 拼接后的文件名
String fileName = "GreenHouseGas" + System.currentTimeMillis() + formatSuffix;//文件名
try {
//=================生成word到设置浏览默认下载地址=================
// 设置强制下载不打开
response.setContentType("application/force-download");
// 设置文件名
response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
OutputStream out = response.getOutputStream();
template.write(out);
out.flush();
out.close();
template.close();
} catch (Exception e) {
e.printStackTrace();
}
}
机组信息合并单元格策略类
package com.carbon.system.policy;
import com.alibaba.fastjson.JSON;
import com.carbon.system.domain.SysDefineMethod;
import com.carbon.system.domain.vo.ElectricityInfoVo;
import com.deepoove.poi.data.RowRenderData;
import com.deepoove.poi.policy.DynamicTableRenderPolicy;
import com.deepoove.poi.policy.MiniTableRenderPolicy;
import com.deepoove.poi.util.TableTools;
import org.apache.poi.xwpf.usermodel.XWPFTable;
import org.apache.poi.xwpf.usermodel.XWPFTableCell;
import org.apache.poi.xwpf.usermodel.XWPFTableRow;
import org.springframework.util.CollectionUtils;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
/**
* @Description
* @Author admin
*/
public class DefineMethodPolicy extends DynamicTableRenderPolicy {
// 填充数据所在行数
int listsStartRow = 2;
@Override
public void render(XWPFTable table, Object data) {
if (null == data) {
return;
}
List<SysDefineMethod> defineMethodList = JSON.parseArray(JSON.toJSONString(data), SysDefineMethod.class);
Map<Integer, List<SysDefineMethod>> unitDefineMethodMap = defineMethodList.stream().collect(Collectors.groupingBy(SysDefineMethod::getUnitId));
Map<String, List<SysDefineMethod>> unitParamMap = defineMethodList.stream().collect(Collectors.groupingBy(defineMethod->defineMethod.getUnitId()+"-"+defineMethod.getParamType()));
if (!CollectionUtils.isEmpty(defineMethodList)) {
table.removeRow(listsStartRow);
List<RowRenderData> dataList = new ArrayList<>();
for (SysDefineMethod tmp : defineMethodList) {
String paramType = tmp.getParamType();
switch (paramType){
case "0":
tmp.setParamType("测试参数1");
break;
case "1":
tmp.setParamType("测试参数2");
}
RowRenderData renderData = RowRenderData.build(tmp.getUnitName(),tmp.getParamType(),tmp.getParamMonth().toString(),
tmp.getEquipmentModel()==null?"":tmp.getEquipmentModel(),tmp.getCheckFrequency()==null?"":tmp.getCheckFrequency(),
tmp.getCheckFrequencyRule()==null?"":tmp.getCheckFrequencyRule(),tmp.getCheckMethodStandard()==null?"":tmp.getCheckMethodStandard(),
tmp.getEntrustOrgName()==null?"":tmp.getEntrustOrgName(),tmp.getCheckReportNo()==null?"":tmp.getCheckReportNo(),
tmp.getCheckDate()==null?"":tmp.getCheckDate().toString(),tmp.getMethodStandardEntrust()==null?"":tmp.getMethodStandardEntrust(),
tmp.getDefaultValue()==null?"":tmp.getDefaultValue());
dataList.add(renderData);
}
// 循环插入行
for (int i = dataList.size() - 1; i >= 0; i--) {
XWPFTableRow insertNewTableRow = table.insertNewTableRow(listsStartRow);
for (int j = 0; j < 12; j++) {
insertNewTableRow.createCell();
}
// 渲染单行机组数据
MiniTableRenderPolicy.Helper.renderRow(table, listsStartRow, dataList.get(i));
}
List<Integer> unitList = JSON.parseArray(JSON.toJSONString(unitDefineMethodMap.keySet()), Integer.class);
List<String> paramList = JSON.parseArray(JSON.toJSONString(unitParamMap.keySet()), String.class);
//处理合并
for (int i = 0; i < dataList.size(); i++) {
//处理第一列机组合并
Object v = dataList.get(i).getCells().get(0).getCellText();
String unit_name = String.valueOf(v);
for (int j = 0; j < unitList.size(); j++) {
Integer unitId = unitList.get(j);
String unitName = unitDefineMethodMap.get(unitId).get(0).getUnitName();
List<SysDefineMethod> tmpList = unitDefineMethodMap.get(unitId);
if (unit_name.equals(unitName)) {
// 合并第0列的第i+2行到第i+unitSize行的单元格
TableTools.mergeCellsVertically(table, 0, i + 2, i + tmpList.size()+1);
//处理垂直居中
for (int y = 0; y < 12; y++) {
XWPFTableCell cell = table.getRow(i + 2).getCell(y);
cell.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER); //垂直居中
}
unitList.remove(j);
break;
}
}
//处理第二列参数类型合并
Object v1 = dataList.get(i).getCells().get(1).getCellText();
String paramType = v+"-"+v1;
for (int j = 0; j < paramList.size(); j++) {
String key = paramList.get(j);
List<SysDefineMethod> tmpList = unitParamMap.get(key);
String paramName=tmpList.get(0).getUnitName()+"-"+tmpList.get(0).getParamType();
if (paramType.equals(paramName)) {
// 合并第1列的第i+1行到第i+unitSize行的单元格
TableTools.mergeCellsVertically(table, 1, i+2, i + tmpList.size()+1);
//处理垂直居中
for (int y = 0; y < 12; y++) {
XWPFTableCell cell = table.getRow(i + 2).getCell(y);
cell.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER); //垂直居中
}
paramList.remove(j);
break;
}
}
}
}
}
}