本节继续 上一节 内容实现明细查询的代码。
先分析一下报表结构：
报表主要由三部分组成，如图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 文件的下载链接：https://kdocs.cn/l/cjquBVytt7DX
//本次查询使用的报表文件
报表.et文件的下载链接：https://kdocs.cn/l/cs6tGas5E2he声明：本站所有文章，如无特殊说明或标注，均为本站原创发布。任何个人或组织，在未征得本站同意时，禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益，可联系我们进行处理。
