背景：接到月末要自动生成报表，包含了文本模板描述，表格报表见图,每一个变量都是唯一
2：技术方案：
用Map<key,value>存储变量名和变量值，读word文本readbuffer遍历文本进行字符匹配用于替换
3：代码示例：
package com.extracme.hntask.oas.business.service;
import com.alibaba.fastjson.JSON;
import com.extracme.hntask.common.BaseDto;
import com.extracme.hntask.common.OfficeBaseDto;
import com.extracme.hntask.common.OfficeCommonDefine;
import com.extracme.hntask.oas.business.domain.MonthlyReportInfo;
import com.extracme.hntask.oas.business.entity.CityStatisticsVo;
import com.extracme.hntask.oas.business.entity.ReportOperationalMonitoringDo;
import com.extracme.hntask.oas.business.entity.ReportStationTop10Do;
import com.extracme.hntask.oas.business.iservice.IServiceReport;
import com.extracme.hntask.oas.business.iservice.IServiceStatistics;
import com.extracme.hntask.oas.business.mapper.MonthlyReportInfoMapper;
import com.extracme.hntask.utils.CustomXWPFDocument;
import com.extracme.hntask.utils.OSSUtil;
import com.extracme.hntask.utils.OfficeUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFRun;
import org.apache.poi.xwpf.usermodel.XWPFTable;
import org.apache.poi.xwpf.usermodel.XWPFTableRow;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.io.*;
import java.math.BigInteger;
import java.net.URL;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/* *
*
* @title Extracme SH
* @project
* @note 自动月报
* @author alex
* @Date 2019/10/29
*/
@Service
public class ServiceReport implements IServiceReport {
private final Logger log = LoggerFactory.getLogger(this.getClass());
@Autowired
private IServiceStatistics serviceStatistics;
@Resource
private MonthlyReportInfoMapper monthlyReportInfoMapper;
@Resource
private OSSUtil oSSUtil;
@Value(value = "${monthlyReportUrl}")
private String monthlyReportUrl;
@Value(value = "${templatePath}")
private String templatePath;
/**
* @param tempParam 封装 对应 模板字段 的参数 数据的map
*/
public Map<String, Object> EncapsulationTempParamDate2ReplaceText(Map<String, Object> tempParam) {
/**
* 一、全省充电设施情况
*/
tempParam = serviceStatistics.getHomeStatistics(tempParam);
System.out.println("一:|"+JSON.toJSONString(tempParam));
/**
* 二、 运营监测情况
*/
tempParam = serviceStatistics.queryOperationMonitor(tempParam);
System.out.println("二:|"+JSON.toJSONString(tempParam));
/**
* 三、排名情况
*/
tempParam = serviceStatistics.queryLMOrderTop10(tempParam);
System.out.println("三:|"+JSON.toJSONString(tempParam));
/**
* 四、接入平台的充电桩市县分布情况
*/
tempParam = serviceStatistics.findAllOrgCityStatistics(tempParam);
System.out.println("四:|"+JSON.toJSONString(tempParam));
return tempParam;
}
@Override
public void generateMonthStatsReportByMonthly() throws IOException {
InputStream isUpload = null;
InputStream is = null;
try {
/**
* 1:下载模板转化bytes 数据处理word
*/
URL url = new URL(monthlyReportUrl);//http://evready-test.oss-cn-shanghai.aliyuncs.com/hainan/template/MonthlyReport%28hnjk%29.docx
is = url.openStream();
byte[] templateBytes = IOUtils.toByteArray(is);//OSSUtil.downloadOssFileByUrl(null);//4.下载报告模板
if (templateBytes == null) {
log.error("下载的报告模板 is null,下载错误");
return;
}
/**
* 2:封装模板参数数据用于替换
*/
Map<String, Object> tempParam = new HashMap<String, Object>();
tempParam = EncapsulationTempParamDate2ReplaceText(tempParam);
if (tempParam.isEmpty()) {
System.err.println("封装模板参数数据用于替换 map|" + null);
log.error("封装模板参数数据用于替换 map is null,return");
return;
}
SimpleDateFormat format = new SimpleDateFormat("yyyy");
Date date = new Date();
Calendar calendar = Calendar.getInstance();
calendar.setTime(date); // 设置为当前时间
calendar.set(Calendar.MONTH, calendar.get(Calendar.MONTH) - 1); // 设置为上一个月
date = calendar.getTime();
String year = format.format(date);
SimpleDateFormat format2 = new SimpleDateFormat("MM");
String lastMonth = format2.format(date);
BaseDto dto = new BaseDto();
dto.setYear(year);
dto.setLastMonth(lastMonth);
tempParam.put("${year}", year);
tempParam.put("${lastMonth}", lastMonth);
//替换word中的自定义字符串以及图片（适用于word2003+ 版本）
byte[] byt = this.replaceAndGenerateWord(dto, templateBytes, tempParam);
// //写本地
// File cc = new File("C:\\Users\\alex\\Desktop\\extracme\\外部业务\\海南充电桩平台\\政府平台\\技术方案\\自动月报\\aa.docx");
// OutputStream out = new FileOutputStream(cc);
// out.write(byt);
/**
* 3：替换后数据的模板 上传到oss 供页面下载
*/
String fileName = year+lastMonth+"_reportMonthly.docx";
String filePath=templatePath+fileName;
isUpload = new ByteArrayInputStream(byt);
oSSUtil.uploadFile(isUpload, filePath);
/**
* 4：数据处理word insert到mysql
*/
//用此线程单独记录月报信息用（不影响generateReport）
String report_body=JSON.toJSON(tempParam)+"";
MonthlyReportInfo mr=new MonthlyReportInfo();
mr.setYear(dto.getYear());
mr.setMonth(dto.getLastMonth());
mr.setReportUrl("https://evready-test.oss-cn-shanghai.aliyuncs.com/" + filePath);
mr.setCreatedUser("job");
mr.setReportBody(report_body);
monthlyReportInfoMapper.insert(mr);
} catch (Exception e) {
log.error("generateMonthStatsReportByMonthly失败", e);
e.printStackTrace();
} finally {
if (is != null) {
is.close();
}
if (isUpload != null) {
isUpload.close();
}
}
}
/**
* 获取报告模板
*
* @param templateUrl 模板oss地址
* @return
*/
private byte[] reportTemplate(String templateUrl) {
// 获取原始记录模版URL
// 获取原始记录模版
byte[] templateByte = null;
try {
templateByte = OSSUtil.downloadOssFileByUrl(templateUrl);
} catch (Exception e) {
log.error("get downloadOssFileByFileUrl err|" + e.getMessage());
e.printStackTrace();
}
return templateByte;
}
/**
* 替换word中的自定义字符串以及图片（适用于word2003+ 版本）
* <p>
* 注：2003版本word不支持替换图片，2007版本以上可以替换图片
*
* @param bytes 模板文件流
* @param param 原始记录map
* @return byte[] 报告OSS保存地址
* @throws Exception 异常
*/
public byte[] replaceAndGenerateWord(BaseDto dto, byte[] bytes, Map<String, Object> param) throws Exception {
CustomXWPFDocument document = null;
InputStream inputStream = new ByteArrayInputStream(bytes);
OPCPackage pack = OPCPackage.open(inputStream);
document = new CustomXWPFDocument(pack);
document.getHeaderList();
// 处理段落
List<XWPFParagraph> paragraphList = document.getParagraphs();
processParagraphs(dto, paragraphList, param, document);
// // 处理表格
// Iterator<XWPFTable> it = document.getTablesIterator();
// while (it.hasNext()) {
// XWPFTable table = it.next();
// List<XWPFTableRow> rows = table.getRows();
// for (XWPFTableRow row : rows) {
// List<XWPFTableCell> cells = row.getTableCells();
// for (XWPFTableCell cell : cells) {
// final CustomXWPFDocument document2 = document;
// List<XWPFParagraph> paragraphListTable = cell.getParagraphs();
// processParagraphs(dto, paragraphListTable, param, document2);
// }
// }
// }
// getExistsPlaceholderListWord(document, existsPlaceholderList);
ByteArrayOutputStream fos = new ByteArrayOutputStream();
document.write(fos);
return fos.toByteArray();
}
/**
* 把 string型的值 替代源模板中的字符
*/
private static String instanceofStrValue2TemplateStr(String text, String key, String value, XWPFParagraph paragraph) {
List<XWPFRun> runs = paragraph.getRuns();
String text2 = text.replace(key, value.toString());
System.out.println(key + " " + value + "instanceofStr2TemplateStr11 text|" + text + "txt2:" + text2);
String keyStr = "";
int flag = 0;
for (int i = 0; i < runs.size(); i++) {
String textTemp = runs.get(i).getText(0);
if (textTemp != null && textTemp.contains(key)) {
String textStr = textTemp.replace(key, value.toString());
runs.get(i).setText(textStr, 0);
System.out.println("set key1|"+key+"|"+textStr);
runs.get(i).setFontFamily("宋体");
break;
}
// 占位符被截断的方案
if (textTemp != null && textTemp.startsWith("$")
&& !textTemp.matches("^\\$\\{[a-zA-Z0-9]+\\}$")) {
keyStr += textTemp;
runs.get(i).setText("", 0);
flag = 1;
System.out.println("set key2|"+key+"|");
} else if (flag == 1) {
if (textTemp.contains("}")) {//TODO 此处的contains应该换成startsWith 避免 一个字符为 ”text}“情况
keyStr += textTemp;
runs.get(i).setText("", 0);
System.out.println("set keyStr| "+ keyStr + "|key3 |"+key+"|"+ value.toString());
runs.get(i).setText(keyStr.replace(key, value), 0);
runs.get(i).setFontFamily("宋体");
break;
}else{
if(textTemp!=null){
keyStr += textTemp;
runs.get(i).setText("", 0);
}
}
}
// System.out.println(i+ "runtxt:" + textTemp + " keyStr："+ keyStr);
}
return text2;
}
/**
* hainan处理段落
*
* @param dto
* @param paragraphList 段落列表
* @param map 键值对map
* @param doc doc
* @throws InvalidFormatException 异常
* @throws FileNotFoundException 异常
*/
private static void processParagraphs(BaseDto dto, List<XWPFParagraph> paragraphList, Map<String, Object> map,
CustomXWPFDocument doc) throws InvalidFormatException, FileNotFoundException {
if (paragraphList != null && paragraphList.size() > 0) {
// 首选循环段落
for (XWPFParagraph paragraph : paragraphList) {
// 获取段落的text
String text = paragraph.getText();
if (StringUtils.isNotBlank(text)) {
List<String> ids = new ArrayList<String>();
Pattern pattern = Pattern.compile("(?<=\\$\\{)[^}]*(?=\\})");//匹配出${}之间的id
Matcher matcher = pattern.matcher(text);
while (matcher.find()) {
String id = "${" + matcher.group() + "}";
ids.add(id);
}
for (String id : ids) {
Object value = map.get(id);
System.err.println("test id|" + id + "|value=" + value);
String key = id;
//字符串值
if (value instanceof String) {
text = instanceofStrValue2TemplateStr(text, key, value.toString(), paragraph);
} else if (value instanceof List) {
//1：list
instanceofStrValue2TemplateStr(text, key, "", paragraph);
if (OfficeCommonDefine.Report_template_table_name_tag[1].equals(id) || OfficeCommonDefine.Report_template_table_name_tag[2].equals(id)) {
//二、 运营监测情况 接入/预接入统计title内容一致
String accessTileText = "月（预接入）";
if (OfficeCommonDefine.Report_template_table_name_tag[2].equals(id)) {
accessTileText = "月（接入）";
}
System.err.println("--tb1 create--" + id);
// 运营监测情况
List<ReportOperationalMonitoringDo> list = (List<ReportOperationalMonitoringDo>) value;
// 动态创建表头
String titleText = dto.getYear() + "年" + dto.getLastMonth() + accessTileText;
OfficeBaseDto obd = new OfficeBaseDto(titleText, OfficeCommonDefine.OPERATIONAL_MONITORING_TITLE, 8);
XWPFTable table = OfficeUtil.createTableHeader(obd, doc, paragraph);
// 动态list行
for (int j = 0; j < list.size(); j++) {
ReportOperationalMonitoringDo tb1_DO = list.get(j);
XWPFTableRow dataRow = table.createRow();
dataRow.getCell(0).setText(tb1_DO.getOperatorName());
dataRow.getCell(1).setText(tb1_DO.getPowerMonth());
String prStr=tb1_DO.getPowerRise();
if (StringUtils.isBlank(prStr)) {
prStr="";
}else if (prStr.indexOf("-") >= 0) {
prStr = "↓" + prStr + "%";
} else if ("0.00".equals(prStr)||"0".equals(prStr)) {
prStr = "0";
} else {
prStr = "↑" + prStr + "%";
}
dataRow.getCell(2).setText(prStr);
dataRow.getCell(3).setText(tb1_DO.getChargeNumMonth());
String crStr = tb1_DO.getChargeNumRise();
if (StringUtils.isBlank(crStr)) {
crStr="";
}else if (crStr.indexOf("-") >= 0) {
crStr = "↓" + crStr + "%";
// dataRow.getCell(4).setColor("008000");
}else if ("0.00".equals(crStr)||"0".equals(crStr)) {
crStr = "0";
}else {
crStr = "↑" + crStr + "%";
// dataRow.getCell(4).setColor("ff0000");//setColor("ff0000"); //红色
}
dataRow.getCell(4).setText(crStr);
dataRow.getCell(5).setText(tb1_DO.getEquipmentNum());
dataRow.getCell(6).setText(tb1_DO.getChargeEquipmentNumMonth());
dataRow.getCell(7).setText(tb1_DO.getChargeEquipmentProportion()+"%");
}
} else if (OfficeCommonDefine.Report_template_table_name_tag[3].equals(id) || OfficeCommonDefine.Report_template_table_name_tag[4].equals(id)
|| OfficeCommonDefine.Report_template_table_name_tag[5].equals(id) || OfficeCommonDefine.Report_template_table_name_tag[6].equals(id)) {
String firstRowText = "月 充电量排行 前十（预接入）";
if (OfficeCommonDefine.Report_template_table_name_tag[4].equals(id)) {
firstRowText = "月 充电量排行 前十（接入）";
} else if (OfficeCommonDefine.Report_template_table_name_tag[5].equals(id)) {
firstRowText = "月 充电次数排行 前十（预接入）";
} else if (OfficeCommonDefine.Report_template_table_name_tag[6].equals(id)) {
firstRowText = "月 充电次数排行 前十（接入）";
}
System.err.println("--tb3-6 create--" + id);
List<ReportStationTop10Do> list = (List<ReportStationTop10Do>) value;
// 动态创建表头
String titleText = dto.getYear() + "年" + dto.getLastMonth() + firstRowText;
OfficeBaseDto obd = new OfficeBaseDto(titleText, OfficeCommonDefine.STATION_TOP10_TITLE, 4);
XWPFTable table = OfficeUtil.createTableHeader(obd, doc, paragraph);
//设置标头某列宽度
table.getRow(1).getCell(1).getCTTc().addNewTcPr().addNewTcW().setW(BigInteger.valueOf(4000));
// 动态行
for (int j = 0; j < list.size(); j++) {
XWPFTableRow dataRow = table.createRow();
String sort = (j + 1) + "";
ReportStationTop10Do st_do = list.get(j);
dataRow.getCell(0).setText(sort);
dataRow.getCell(1).setText(st_do.getStationName());
dataRow.getCell(2).setText(st_do.getDistrictName());
dataRow.getCell(3).setText(st_do.getOrgName());
}
} else if (id.indexOf("${layoutTb") >= 0) {
//四、接入平台的充电桩市县分布情况 ${tb7}是总计
System.err.println("--tb7- create--" + id);
List<CityStatisticsVo> list = (List<CityStatisticsVo>) value;
System.out.println("layoutTb|" + JSON.toJSONString(list));
String titleText = list.get(0).getName();
// 动态创建表头
OfficeBaseDto obd = new OfficeBaseDto(titleText, OfficeCommonDefine.EQUIPMENT_ACCESS_AREA_TITLE, 5);
XWPFTable table = OfficeUtil.createTableHeader(obd, doc, paragraph);
// 动态行
int size = list.size();
int rowCnt=size+1;//加一行为总计
int accessStationNumSum=0,preAccessStationNumSum=0;
int accessEquipmentNumSum=0,preAccessEquipmentNumSum=0;
int accessDCEquipmentNumSum=0,preAccessDCEquipmentNumSum=0;
int accessACEquipmentNumSum=0,preAccessACEquipmentNumSum=0;
for (int j = 0; j < rowCnt; j++) {
XWPFTableRow dataRow = table.createRow();
if (j < size) {
CityStatisticsVo cs = list.get(j);
dataRow.getCell(0).setText(cs.getCity());
dataRow.getCell(1).setText(cs.getAccessStationNum() + "/" + cs.getPreAccessStationNum());
dataRow.getCell(2).setText(cs.getAccessEquipmentNum() + "/" + cs.getPreAccessEquipmentNum());
dataRow.getCell(3).setText(cs.getAccessDCEquipmentNum() + "/" + cs.getPreAccessDCEquipmentNum());
dataRow.getCell(4).setText(cs.getAccessACEquipmentNum() + "/" + cs.getPreAccessACEquipmentNum());
//算总计row
accessStationNumSum += cs.getAccessStationNum();
preAccessStationNumSum += cs.getPreAccessStationNum();
accessEquipmentNumSum += cs.getAccessEquipmentNum();
preAccessEquipmentNumSum += cs.getPreAccessEquipmentNum();
accessDCEquipmentNumSum += cs.getAccessDCEquipmentNum();
preAccessDCEquipmentNumSum += cs.getPreAccessDCEquipmentNum();
accessACEquipmentNumSum += cs.getAccessACEquipmentNum();
preAccessACEquipmentNumSum += cs.getPreAccessACEquipmentNum();
} else {
dataRow.getCell(0).setText("总计");
dataRow.getCell(1).setText(accessStationNumSum + "/" + preAccessStationNumSum);
dataRow.getCell(2).setText(accessEquipmentNumSum + "/" + preAccessEquipmentNumSum);
dataRow.getCell(3).setText(accessDCEquipmentNumSum + "/" + preAccessDCEquipmentNumSum);
dataRow.getCell(4).setText(accessACEquipmentNumSum + "/" + preAccessACEquipmentNumSum);
}
}
}
}
}
}
}
}
}
}本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
