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

先分析一下报表结构：

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

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

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

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

报表分区 图14.0

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

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

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

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

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

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

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

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

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

标题与排序 图13.0

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

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

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

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

▶ 如果在“支出区域”，则查询出具体的项目分类汇总数据（项目分类为橙色列对应的明细 图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 文件的下载链接：本次查询使用的报表文件报表.et文件的下载链接：