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

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

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

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

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

? 实现金额单位在元与万元之间的切换 如图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：

代码如下：

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

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

功切换，如图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：

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

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

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

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

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

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

[单位]-->[预算单位]

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

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

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

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