16. WPS报表JS宏代码实现格式化显示（完结）

大家好，欢迎来到IT知识分享网。

接 上一节 内容，在本节将实现WPS表格报表的格式化显示及相关兼容性代码的修改。

在这个报表里需要格式化的有两处：

一是实现金额单位在元与万元之间的切换；

二是实现报表相关区域根据A列1~3行示例的字体和边框进行格式化。

▶ 实现金额单位在元与万元之间的切换 如图16.0：

金额显示 图16.0

金额单位在元与万元之间的切换：

1. F2单元格内容变动后；

2. 执行【查询】之后。

在lib模块定义元与万元的格式：

/* 定义元与万元的格式*/ function Yuan(){ return '#,0.00;[红色]-#,0.00;" - " ;@' } function WanYuan(){ return '0!.0, ;[红色]-0!.0, ;"" - "" ;@'} /* * setAmountFmt 设置金额格式 元与万元 * 传入一个参数Sh 为工作表的引用 * 无返回值 */ function setAmountFmt(Sh) { let QueryRange = Sh.QueryTables.Item(1).ResultRange let tmpr = getAmountRange(QueryRange) tmpr.NumberFormat = Sh.Range("f2").Value2.substr(0,1) == 0 ? Yuan():WanYuan() }

切换到main模块，在对象下拉栏选择Workbook，在后面的事件栏选择SheetChange(Sh, rg)，生成 function Workbook_SheetChange(Sh, rg) 这个函数，如图16.1：

SheetChange函数 图16.1

代码如下：

//金额单位设置 function Workbook_SheetChange(Sh, rg) { if (Sh.Name == getReportShName() && rg.Address() == "$F$2"){ //调用lib模块的金额格式设置函数 setAmountFmt(Sh) } }

代码完成后，切换到报表工作表窗口，在设置格式后面切换元与万元，可以看到金额能成

功切换，如图16.2：

金额切换 图16.2

▶ 实现报表区域根据示例单元格进行格式化

切换到lib模块，增加setRangeFmt函数：

/* * setRangeFmt 设置区域格式 * 传入一个参数Sh 为工作表的引用 * 无返回值 */ function setRangeFmt(Sh) { //先保存当前活动（选择）区域 let sl = Application.Selection //清除旧的格式 Sh.Range("c5:p500").ClearFormats() //查询区域 let QueryRange = Sh.QueryTables.Item(1).ResultRange //将报表a1单元格刷到左侧固定区域 Sh.Range("a1").Copy(undefined) tmpr = getFixedRange(QueryRange) tmpr.PasteSpecial(xlPasteFormats, xlPasteSpecialOperationNone, false, false) //将报表a2单元格刷到指标区域 Sh.Range("a2").Copy(undefined) tmpr = getQuotaRange(QueryRange) tmpr.PasteSpecial(xlPasteFormats, xlPasteSpecialOperationNone, false, false) //将报表a3单元格刷到支出区域 Sh.Range("a3").Copy(undefined) tmpr = getDisbursedRange(QueryRange) tmpr.PasteSpecial(xlPasteFormats, xlPasteSpecialOperationNone, false, false) //取消copy的模式（去掉了动态的矩形选框） Application.CutCopyMode = false; //设置金额格式 setAmountFmt(Sh) sl.Select() //恢复旧的选择区域 } 

在main模块的CommandButton1_Click()事件下面调用setRangeFmt：

//查询按钮的单击事件 function CommandButton1_Click() { let ReportSh = Application.Worksheets.Item(getReportShName() ) let DictSh = Application.Worksheets.Item(getDictShName()) let ReportQuery = ReportSh.QueryTables.Item(1) ReportQuery.CommandText = DictSh.Range("D1").Value2.replace(/\?/g, getWhereStr(ReportSh)) ReportQuery.Refresh() setRangeFmt(ReportSh) }

点击查询按钮的效果如图16.3：

格式刷 图16.3

Workbook_SheetChange这个函数只是切换了金额单位，如果也想实现图16.3的格式刷的效果，把最后的调用函数改为setRangeFmt即可：

//金额单位设置 function Workbook_SheetChange(Sh, rg) { if (Sh.Name == getReportShName() && rg.Address() == "$F$2"){ //调用lib模块的金额格式设置函数 // setAmountFmt(Sh) setRangeFmt(Sh) } }

为了应对报表迁移后，不必手动设置连接也能打开查询，要设置查询连接的动态路径，在lib模块增加setCnPath()这个函数，代码如下：

/* * setCnPath() 设置查询连接的动态路径 * database.et 与 报表.et 放在一个目录下， * 无论拷贝到何处，都不必手动设置连接文件路径 * 返回值为包含新路径的连接字符串 */ function setCnPath(){ let cn = Application.ThisWorkbook.Connections.Item(1).OLEDBConnection let newpath=Application.ThisWorkbook.Path + "\\database.et" if (newpath != cn.SourceDataFile){ cn.SourceDataFile= newpath /*正则表达式解析：(?<=(Source=)) 从"Source="右侧开始 .*? 除了换行其他字符都包括，即使空(0次)也可定位 (?=;) 到";"左侧结束*/ cn.Connection = cn.Connection.replace(/(?<=(Source=)).*?(?=;)/g, newpath) } return cn.Connection }

切换到main模块，在CommandButton1_Click与CommandButton2_Click的使用查询或连接的代码之前都加上函数setCnPath()的调用，以保证始终连接同一文件夹内的database.et。如图：16.4

动态连接调用 图16.4

为了更好的兼容JS ES6之前的版本，经过测试，将部分代码修改，如图16.5：

兼容性修改 图16.5

至此，报表功能基本完善。

最后附上最终版表格下载供读者学习参考：

//下载后解压到一个文件夹，打开报表.et使用 下载链接：https://kdocs.cn/l/csbkIyIcLFCS

最终版修改了database.et中的四个字段名称和相关的SQL语句字段，以符合系统内导出的字段名：

[单位]–>[预算单位]

[编号]–>[计划明细编号]

[政府经济分类]–>[政府支出经济分类]

[部门经济分类]–>[部门支出经济分类]

如果有什么想法和建议，请关注我并评论，感谢阅读。