word模板中替换文本中自定义字符串生成月报

梦里梦外; 2022-10-08 05:59 141阅读 0赞

背景：接到月末要自动生成报表，包含了文本模板描述，表格报表见图,每一个变量都是唯一![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDQ3MzIx_size_16_color_FFFFFF_t_70][]

2：技术方案：

用Map<key,value>存储变量名和变量值，读word文本readbuffer遍历文本进行字符匹配用于替换

3：代码示例：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDQ3MzIx_size_16_color_FFFFFF_t_70 1][]

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);
                                        }
                                    }
    
                                }
                            }
                        }
                    }
                }
            }
        }
    
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDQ3MzIx_size_16_color_FFFFFF_t_70]: /images/20221005/fe968a40b8644e2382d5e5c1b95e6e3e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDQ3MzIx_size_16_color_FFFFFF_t_70 1]: /images/20221005/47d02dd4b28e41f9af6e95e4b033db8c.png