上市公司报表查询

先分析一下报表结构：

报表主要由三部分组成，如图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.ActiveSheetlet 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").Textlet BoolUnit =UnitValue == "0-全部" || UnitValue == ""//取得当前活动单元格同一行排序单元格的值let OrderValue = ShReport.Cells.Item(c1.Row, 2).Value2/*解析OrderValue，判断当前活动单元格位于什么汇总级别 */if (OrderValue == '0'){ //汇总合计行，什么也不用做，全部提取就好了} else { //根据排序值的长度来判断是哪一级的汇总switch(OrderValue.length){case7: //类款项的汇总行/* 查询的是 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) +"')" }breakcase9://单位汇总行-缺款、项，只有类+单位的汇总行 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) + "')" : ""breakcase 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) + "')" : ""breakcase 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) + "')" : ""breakdefault: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.ｒｅｐｌａｃｅ(/\?/g, whereStr + AddWhere + AddWhere2)//使用当前工作薄的现有连接 cnstrlet cnstr = Application.ThisWorkbook.Connections.Item(1).OLEDBConnection.Connection//支出查询数据存放工作表let ShDetail = Application.Worksheets.Item("支出明细")//先清除查询for (let qt of ShDetail.QueryTables){qt.ResultRange.Clear()qt.ｄｅｌｅｔｅ()}//查询明细 创建支出查询let qDetail = ShDetail.QueryTables.Add(cnstr, ShDetail.Range("A3"), sqlstr)qDetail.Refresh()/**下面查询项目分类明细*/let ShProjectDetails = Application.Worksheets.Item("项目分类明细汇总") //项目分类明细汇总查询的存放工作表sqlstr = ShDict.Range("d2").Value2.ｒｅｐｌａｃｅ(/\?/g, whereStr + AddWhere + AddWhere2)//先清除查询for (let qt of ShProjectDetails.QueryTables){qt.ResultRange.Clear()qt.ｄｅｌｅｔｅ()}//查询明细 创建项目明细查询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