js 编辑wps表格文件：WPS报表JS宏代码实现格式化显示
js 编辑wps表格文件：WPS报表JS宏代码实现格式化显示2. 执行【查询】之后。1. F2单元格内容变动后；▶ 实现金额单位在元与万元之间的切换 如图16.0：金额显示 图16.0金额单位在元与万元之间的切换：
接 上一节 内容，在本节将实现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语句字段，以符合系统内导出的字段名：
[单位]-->[预算单位]
[编号]-->[计划明细编号]
[政府经济分类]-->[政府支出经济分类]
[部门经济分类]-->[部门支出经济分类]
如果有什么想法和建议，请关注我并评论，感谢阅读。
