14. WPS表格使用JS宏查询报表明细之查询实现

本节继续 上一节 内容实现明细查询的代码。

先分析一下报表结构：

报表主要由三部分组成，如图14.0.。

左侧是“固定区域”，图14.0中的灰色区域，共5列：排序、类、款、项、科目名称。

中间是“指标区域”，图14.0中的蓝色区域，共4列：指标总金额、指标已用金额、指标可用金额、计划金额合计。

右侧是“支出区域”，图14.0中的橙色区域，共6列：工资福利支出、对个人和家庭补助支出、公用经费、部门预算项目、专项资金项目、其他项目。

【显示明细】按钮的使用方法，鼠标选中相应单元格，使之成为活动单元格，再点击显示明细按钮。

显示明细按钮的代码应做如下工作：

第一步、判断活动单元格是否在指定的区域内；

第二步、如果这个单元格在相应区域内：

a. 根据当前活动单元格的排序行，分析出是合计、类、款、项以及单位这些汇总行的哪一个（如图13.0）;

b. 根据当前活动单元格的标题决定是否执行项目分类明细的查询；

第三步、使用当前工作薄的现有连接，创建新两个新的查询：

a. 查询源数据抽出有用的列形成明细表；

b. 按项目名称汇总出相关数据。

第一步细化说明及相关代码：

? 如果不在报表数据区域内，什么也不做

? 如果在“固定区域”，什么也不做

? 如果在“指标区域”，则查询出无项目分类的汇总数据（计划合计）

? 如果在“支出区域”，则查询出具体的项目分类汇总数据（项目分类为橙色列对应的明细 图14.0）

涉及到了查询表所覆盖的工作表区域，用到了Range区域对象QueryTable.ResultRange这个属性。

整个查询表使用的列是固定的，从B列到P列；使用的行不是固定的，从第4行开始，到ResultRange.End(xlDown).Row返回的行号。

要对QueryTable.ResultRange这个区域分类，在lib模块添加三个函数，分别获取到这三个区域。相关代码如下：

/* getFixedRange 获取左侧固定区域
 * 传入报表返回的数据区域
 * 返回C:F，4列，不包括排序列和标题行
 */
function getFixedRange(ResultRange){
	return ResultRange.Offset(1,1).Resize(ResultRange.Rows.Count-1, 4)
}

/* getQuotaRange 获取指标区域
 * 传入报表返回的数据区域
 * 返回J:G，4列，包括了计划合计列，不包括标题行
 */
function getQuotaRange(ResultRange){
	return ResultRange.Offset(1,5).Resize(ResultRange.Rows.Count-1, 4)
}

/* getDisbursedRange 获取支出区域
 * 传入报表返回的数据区域
 * 返回K:P，6列，不包括计划合计列与标题行
 */
function getDisbursedRange(ResultRange){
	return ResultRange.Offset(1,9).Resize(ResultRange.Rows.Count-1, 6)
}

判断当前活动单元格是否在指标与支出区域的代码：

//显示明细按钮的单击事件
function CommandButton2_Click()
{
	let ShReport = Application.ThisWorkbook.ActiveSheet
	let DataRange = ShReport.QueryTables.Item(1).ResultRange
  
	//分别获取指标区域与支出区域
	let r1 = getQuotaRange(DataRange)
	let r2 = getDisbursedRange(DataRange)
	
	//得到当前活动单元格，如果是区域，则选择第一个单元格
	let c1 = Selection.Cells(1)
  
/* 第一步 判断活动单元格是否在指定的区域内；*/
	//交叉区域判断 判断所选单元格是否在金额数据汇总区域
	if (!Intersect(c1, Union(r1, r2))) 
	{
		alert("所选单元格不在金额汇总数据显示区域，无法显示明细数据，请重新选择。")
		return 0
	}

第二步细化说明及相关代码：

   /*  第二步 a. 根据当前活动单元格的排序行分析出
  	*  是合计、类、款、项以及单位这些汇总行的哪一级
    *  并将这些条件增加到where语句后面
    */
	
	//定义一个在sql where条件里增加的条件
	let AddWhere = ""
	
	//定义一个单位单元格是否为空或者全部的布尔变量
	let UnitValue = ShReport.Range("f1").Text
	let BoolUnit =  UnitValue == "0-全部" || UnitValue == ""
	
	//取得当前活动单元格同一行排序单元格的值
	let OrderValue = ShReport.Cells.Item(c1.Row, 2).Value2	
		
	/*  解析OrderValue，判断当前活动单元格位于什么汇总级别 */
	if (OrderValue == '0')
	{ 
		//汇总合计行，什么也不用做，全部提取就好了
    } 
	else 
	{ //根据排序值的长度来判断是哪一级的汇总
		switch(OrderValue.length)
		{
			case  7: //类款项的汇总行
				/* 查询的是 2010000 这种类汇总行 */
				if (OrderValue.substr(3,4) == "0000")							
				{ 	// l示例：and (left([支出功能分类],3) = '201')
					AddWhere = " and (left([支出功能分类],3) = '" + OrderValue.substr(0,3) +"')" 
				}  	
				/* 查询的是 2010100 这种款汇总行 */
				else if (OrderValue.substr(5,2) == "00") 
				{ 	// 示例：and (left([支出功能分类],5) = '20101')
					AddWhere = " and (left([支出功能分类],5) = '" + OrderValue.substr(0,5) +"')" 
				} 
				/* 查询的是 2010201 这种项汇总行 */
				else 
				{ 	// 示例：and (left([支出功能分类],7) = '2010101')
					AddWhere = " and (left([支出功能分类],7) = '" + OrderValue.substr(0,7) +"')" 
				}  
				break		
			case  9://单位汇总行-缺款、项，只有类+单位的汇总行 201+101003				
				// 示例：and (left([支出功能分类],4) = '201-') and (left([单位],6) = '101003')			 
				AddWhere = " and (left([支出功能分类],4) = '" + OrderValue.substr(0,3) +"-')" 				
				AddWhere += BoolUnit ? " and (left([单位],6) = '" + OrderValue.substr(4) + "')" : ""
				break
			case 11://单位汇总行-缺项，只有类款+单位的汇总行 20102+101003			
				// 示例：and (left([支出功能分类],6) = '20102-') and (left([单位],6) = '101003')			
				AddWhere = " and (left([支出功能分类],6) = '" + OrderValue.substr(0,5) +"-')" 
				AddWhere += BoolUnit ? " and (left([单位],6) = '" + OrderValue.substr(6) + "')" : ""
				break
			case 13://单位汇总行 类款项+单位的汇总行 2010201+102001			
				// 示例：and (left([支出功能分类],8) = '2010201-') and (left([单位],6) = '101003')
				AddWhere = " and (left([支出功能分类],8) = '" + OrderValue.substr(0,7) +"-')" 
				AddWhere += BoolUnit ? " and (left([单位],6) = '" + OrderValue.substr(8) + "')" : ""
				break
			default:
				Console.log("未知错误1")		
		}
	}
 /*  第二步 b. 根据标题列决定是否显示项目分类明细的其中一个：
	*	 工资福利支出	对个人和家庭补助支出	公用经费
  *   部门预算项目	专项资金项目	其他项目 
  */
	
	//取得当前活动单元格列标题的值
	let TitleValue = ShReport.Cells.Item(4, c1.Column).Value2
	
	//列举项目分类明细内容 如果标题是这里面的内容，要查询项目分类明细
	const ProjectDetails = "工资福利支出	对个人和家庭补助支出	公用经费	部门预算项目	专项资金项目	其他项目"	

	//定义在where里追加的第二个条件
	let AddWhere2 = ""
	//查询的是项目分类明细
	if (ProjectDetails.indexOf(TitleValue) > -1){
		AddWhere2 = TitleValue == "其他项目" ? 
		   " and ([项目类别] not in ('工资福利支出','对个人和家庭补助支出','公用经费','部门预算项目','专项资金项目')"
		 : " and ([项目类别] ='" + TitleValue + "')"
	}		

第三步、使用当前工作薄的现有连接，创建新两个新的查询：

/* 第三步、使用当前工作薄的现有连接，创建新两个新的查询 */
	//数据查询参数设置	
	let ShDict = Application.Worksheets.Item("字典")	 
	
	//获取报表上面的查询条件参数
	let whereStr = getWhereStr(ShReport)
	
	//使用where条件替换存储的支出查询SQL中的?
	let sqlstr = ShDict.Range("d3").Value2.replace(/\?/g, whereStr + AddWhere + AddWhere2)
	
	//使用当前工作薄的现有连接 cnstr
	let cnstr = Application.ThisWorkbook.Connections.Item(1).OLEDBConnection.Connection
	//支出查询数据存放工作表
  let ShDetail = Application.Worksheets.Item("支出明细")
	//先清除查询
	for (let qt of ShDetail.QueryTables){
		qt.ResultRange.Clear()
		qt.Delete()	
	}
	//查询明细 创建支出查询
	let qDetail = ShDetail.QueryTables.Add(cnstr, ShDetail.Range("A3"), sqlstr)
	qDetail.Refresh()
	
	/*
	*下面查询项目分类明细
	*/
	let ShProjectDetails = Application.Worksheets.Item("项目分类明细汇总")
 //		项目分类明细汇总查询的存放工作表
	sqlstr = ShDict.Range("d2").Value2.replace(/\?/g, whereStr + AddWhere + AddWhere2)
		
	//先清除查询
	for (let qt of ShProjectDetails.QueryTables){
		qt.ResultRange.Clear()
		qt.Delete()	   
	}
	//查询明细 创建项目明细查询
	let qProjectDetails = ShProjectDetails.QueryTables.Add(cnstr, ShProjectDetails.Range("A3"), sqlstr)
	qProjectDetails.Refresh()
	
	alert("明细查询完成，请切换到【支出明细】和【项目分类明细汇总】工作表(Sheet)查看。")
｝//END function CommandButton2_Click()
// CommandButton2_Click() 函数结束

至此，完成了明细查询的所有代码。下一节，将对main模块内CommandButton_Click()函数的代码进行适当的精简，使用函数替换其中的大部分内容，以优化阅读体验。

本节示例，附下载链接：

/*
本节使用的两个工作簿在下面的链接，下载后放到一个文件夹中。
将文件名分别重命名为database.et 和 报表.et
打开【报表.et】，操作参考第4节的图4.4：
在数据菜单中，【导入数据】-【编辑连接属性】
点击连接文件路径后面的【浏览】，找到下载的database.et文件，就可以使用了。
*/
//本次查询使用的数据库文件
database.et 文件的下载链接：https://kdocs.cn/l/cjquBVytt7DX
//本次查询使用的报表文件
报表.et文件的下载链接：https://kdocs.cn/l/cs6tGas5E2he