先分析一下報(bào)表結(jié)構(gòu)：

報(bào)表主要由三部分組成，如圖14.0.。

左側(cè)是“固定區(qū)域”，圖14.0中的灰色區(qū)域，共5列：排序、類、款、項(xiàng)、科目名稱。

中間是“指標(biāo)區(qū)域”，圖14.0中的藍(lán)色區(qū)域，共4列：指標(biāo)總金額、指標(biāo)已用金額、指標(biāo)可用金額、計(jì)劃金額合計(jì)。

右側(cè)是“支出區(qū)域”，圖14.0中的橙色區(qū)域，共6列：工資福利支出、對(duì)個(gè)人和家庭補(bǔ)助支出、公用經(jīng)費(fèi)、部門預(yù)算項(xiàng)目、專項(xiàng)資金項(xiàng)目、其他項(xiàng)目。

【顯示明細(xì)】按鈕的使用方法，鼠標(biāo)選中相應(yīng)單元格，使之成為活動(dòng)單元格，再點(diǎn)擊顯示明細(xì)按鈕。

顯示明細(xì)按鈕的代碼應(yīng)做如下工作：

第一步、判斷活動(dòng)單元格是否在指定的區(qū)域內(nèi)；

第二步、如果這個(gè)單元格在相應(yīng)區(qū)域內(nèi)：

a. 根據(jù)當(dāng)前活動(dòng)單元格的排序行，分析出是合計(jì)、類、款、項(xiàng)以及單位這些匯總行的哪一個(gè)（如圖13.0）;

b. 根據(jù)當(dāng)前活動(dòng)單元格的標(biāo)題決定是否執(zhí)行項(xiàng)目分類明細(xì)的查詢；

第三步、使用當(dāng)前工作薄的現(xiàn)有連接，創(chuàng)建新兩個(gè)新的查詢：

a. 查詢源數(shù)據(jù)抽出有用的列形成明細(xì)表；

b. 按項(xiàng)目名稱匯總出相關(guān)數(shù)據(jù)。

第一步細(xì)化說明及相關(guān)代碼：

? 如果不在報(bào)表數(shù)據(jù)區(qū)域內(nèi)，什么也不做

? 如果在“固定區(qū)域”，什么也不做

? 如果在“指標(biāo)區(qū)域”，則查詢出無項(xiàng)目分類的匯總數(shù)據(jù)（計(jì)劃合計(jì)）

? 如果在“支出區(qū)域”，則查詢出具體的項(xiàng)目分類匯總數(shù)據(jù)（項(xiàng)目分類為橙色列對(duì)應(yīng)的明細(xì) 圖14.0）

涉及到了查詢表所覆蓋的工作表區(qū)域，用到了Range區(qū)域?qū)ο驫ueryTable.ResultRange這個(gè)屬性。

整個(gè)查詢表使用的列是固定的，從B列到P列；使用的行不是固定的，從第4行開始，到ResultRange.End(xlDown).Row返回的行號(hào)。

要對(duì)QueryTable.ResultRange這個(gè)區(qū)域分類，在lib模塊添加三個(gè)函數(shù)，分別獲取到這三個(gè)區(qū)域。相關(guān)代碼如下：

/* getFixedRange 獲取左側(cè)固定區(qū)域 * 傳入報(bào)表返回的數(shù)據(jù)區(qū)域 * 返回C:F，4列，不包括排序列和標(biāo)題行 */function getFixedRange(ResultRange){return ResultRange.Offset(1,1).Resize(ResultRange.Rows.Count-1, 4)}/* getQuotaRange 獲取指標(biāo)區(qū)域 * 傳入報(bào)表返回的數(shù)據(jù)區(qū)域 * 返回J:G，4列，包括了計(jì)劃合計(jì)列，不包括標(biāo)題行 */function getQuotaRange(ResultRange){return ResultRange.Offset(1,5).Resize(ResultRange.Rows.Count-1, 4)}/* getDisbursedRange 獲取支出區(qū)域 * 傳入報(bào)表返回的數(shù)據(jù)區(qū)域 * 返回K:P，6列，不包括計(jì)劃合計(jì)列與標(biāo)題行 */function getDisbursedRange(ResultRange){return ResultRange.Offset(1,9).Resize(ResultRange.Rows.Count-1, 6)}

判斷當(dāng)前活動(dòng)單元格是否在指標(biāo)與支出區(qū)域的代碼：

//顯示明細(xì)按鈕的單擊事件function CommandButton2_Click(){let ShReport = Application.ThisWorkbook.ActiveSheetlet DataRange = ShReport.QueryTables.Item(1).ResultRange//分別獲取指標(biāo)區(qū)域與支出區(qū)域let r1 = getQuotaRange(DataRange)let r2 = getDisbursedRange(DataRange)//得到當(dāng)前活動(dòng)單元格，如果是區(qū)域，則選擇第一個(gè)單元格let c1 = Selection.Cells(1)/* 第一步 判斷活動(dòng)單元格是否在指定的區(qū)域內(nèi)；*///交叉區(qū)域判斷 判斷所選單元格是否在金額數(shù)據(jù)匯總區(qū)域if (!Intersect(c1, Union(r1, r2))) {alert("所選單元格不在金額匯總數(shù)據(jù)顯示區(qū)域，無法顯示明細(xì)數(shù)據(jù)，請重新選擇。")return 0}

第二步細(xì)化說明及相關(guān)代碼：

/*第二步 a.?根據(jù)當(dāng)前活動(dòng)單元格的排序行分析出*是合計(jì)、類、款、項(xiàng)以及單位這些匯總行的哪一級(jí)*并將這些條件增加到where語句后面*///定義一個(gè)在sql where條件里增加的條件let AddWhere = ""http://定義一個(gè)單位單元格是否為空或者全部的布爾變量let UnitValue = ShReport.Range("f1").Textlet BoolUnit =UnitValue == "0-全部" || UnitValue == ""http://取得當(dāng)前活動(dòng)單元格同一行排序單元格的值let OrderValue = ShReport.Cells.Item(c1.Row, 2).Value2/*解析OrderValue，判斷當(dāng)前活動(dòng)單元格位于什么匯總級(jí)別 */if (OrderValue == '0'){ //匯總合計(jì)行，什么也不用做，全部提取就好了} else { //根據(jù)排序值的長度來判斷是哪一級(jí)的匯總switch(OrderValue.length){case7: //類款項(xiàng)的匯總行/* 查詢的是 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 這種項(xiàng)匯總行 */else { // 示例：and (left([支出功能分類],7) = '2010101')AddWhere = " and (left([支出功能分類],7) = '" + OrderValue.substr(0,7) +"')" }breakcase9://單位匯總行-缺款、項(xiàng)，只有類+單位的匯總行 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://單位匯總行-缺項(xiàng)，只有類款+單位的匯總行 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://單位匯總行 類款項(xiàng)+單位的匯總行 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("未知錯(cuò)誤1")}} /*第二步 b. 根據(jù)標(biāo)題列決定是否顯示項(xiàng)目分類明細(xì)的其中一個(gè)：* 工資福利支出對(duì)個(gè)人和家庭補(bǔ)助支出公用經(jīng)費(fèi)*部門預(yù)算項(xiàng)目專項(xiàng)資金項(xiàng)目其他項(xiàng)目 *///取得當(dāng)前活動(dòng)單元格列標(biāo)題的值let TitleValue = ShReport.Cells.Item(4, c1.Column).Value2//列舉項(xiàng)目分類明細(xì)內(nèi)容 如果標(biāo)題是這里面的內(nèi)容，要查詢項(xiàng)目分類明細(xì)const ProjectDetails = "工資福利支出對(duì)個(gè)人和家庭補(bǔ)助支出公用經(jīng)費(fèi)部門預(yù)算項(xiàng)目專項(xiàng)資金項(xiàng)目其他項(xiàng)目"http://定義在where里追加的第二個(gè)條件let AddWhere2 = ""http://查詢的是項(xiàng)目分類明細(xì)if (ProjectDetails.indexOf(TitleValue) > -1){AddWhere2 = TitleValue == "其他項(xiàng)目" ? " and ([項(xiàng)目類別] not in ('工資福利支出','對(duì)個(gè)人和家庭補(bǔ)助支出','公用經(jīng)費(fèi)','部門預(yù)算項(xiàng)目','專項(xiàng)資金項(xiàng)目')" : " and ([項(xiàng)目類別] ='" + TitleValue + "')"}

第三步、使用當(dāng)前工作薄的現(xiàn)有連接，創(chuàng)建新兩個(gè)新的查詢：

/* 第三步、使用當(dāng)前工作薄的現(xiàn)有連接，創(chuàng)建新兩個(gè)新的查詢 *///數(shù)據(jù)查詢參數(shù)設(shè)置let ShDict = Application.Worksheets.Item("字典") //獲取報(bào)表上面的查詢條件參數(shù)let whereStr = getWhereStr(ShReport)//使用where條件替換存儲(chǔ)的支出查詢SQL中的?let sqlstr = ShDict.Range("d3").Value2.ｒｅｐｌａｃｅ(/\?/g, whereStr + AddWhere + AddWhere2)//使用當(dāng)前工作薄的現(xiàn)有連接 cnstrlet cnstr = Application.ThisWorkbook.Connections.Item(1).OLEDBConnection.Connection//支出查詢數(shù)據(jù)存放工作表let ShDetail = Application.Worksheets.Item("支出明細(xì)")//先清除查詢for (let qt of ShDetail.QueryTables){qt.ResultRange.Clear()qt.ｄｅｌｅｔｅ()}//查詢明細(xì) 創(chuàng)建支出查詢let qDetail = ShDetail.QueryTables.Add(cnstr, ShDetail.Range("A3"), sqlstr)qDetail.Refresh()/**下面查詢項(xiàng)目分類明細(xì)*/let ShProjectDetails = Application.Worksheets.Item("項(xiàng)目分類明細(xì)匯總") //項(xiàng)目分類明細(xì)匯總查詢的存放工作表sqlstr = ShDict.Range("d2").Value2.ｒｅｐｌａｃｅ(/\?/g, whereStr + AddWhere + AddWhere2)//先清除查詢for (let qt of ShProjectDetails.QueryTables){qt.ResultRange.Clear()qt.ｄｅｌｅｔｅ()}//查詢明細(xì) 創(chuàng)建項(xiàng)目明細(xì)查詢let qProjectDetails = ShProjectDetails.QueryTables.Add(cnstr, ShProjectDetails.Range("A3"), sqlstr)qProjectDetails.Refresh()alert("明細(xì)查詢完成，請切換到【支出明細(xì)】和【項(xiàng)目分類明細(xì)匯總】工作表(Sheet)查看。")｝//END function CommandButton2_Click()// CommandButton2_Click() 函數(shù)結(jié)束

至此，完成了明細(xì)查詢的所有代碼。下一節(jié)，將對(duì)main模塊內(nèi)CommandButton_Click()函數(shù)的代碼進(jìn)行適當(dāng)?shù)木啠褂煤瘮?shù)替換其中的大部分內(nèi)容，以優(yōu)化閱讀體驗(yàn)。

本節(jié)示例，附下載鏈接：

/*本節(jié)使用的兩個(gè)工作簿在下面的鏈接，下載后放到一個(gè)文件夾中。將文件名分別重命名為database.et 和 報(bào)表.et打開【報(bào)表.et】，操作參考第4節(jié)的圖4.4：在數(shù)據(jù)菜單中，【導(dǎo)入數(shù)據(jù)】-【編輯連接屬性】點(diǎn)擊連接文件路徑后面的【瀏覽】，找到下載的database.et文件，就可以使用了。*///本次查詢使用的數(shù)據(jù)庫文件database.et 文件的下載鏈接：https://kdocs.cn/l/cjquBVytt7DX//本次查詢使用的報(bào)表文件報(bào)表.et文件的下載鏈接：https://kdocs.cn/l/cs6tGas5E2he