前言

在Excel 中，依賴列表或級(jí)聯(lián)下拉列表表示兩個(gè)或多個(gè)列表，其中一個(gè)列表的項(xiàng)根據(jù)另一個(gè)列表而變化。依賴列表通常用于Excel的業(yè)務(wù)報(bào)告，例如學(xué)術(shù)記分卡中的【班級(jí)-學(xué)生】列表、區(qū)域銷(xiāo)售報(bào)告中的【區(qū)域-國(guó)家/地區(qū)】列表、人口儀表板中的【年份-區(qū)域】列表以及生產(chǎn)摘要報(bào)告中的【單位-行-產(chǎn)品】列表等等。

在本博客中，小編將為大家介紹如何借助葡萄城公司基于 .NET 和 .NET Core 平臺(tái)的服務(wù)端高性能表格組件組件GrapeCity Documents for Excel （以下簡(jiǎn)稱GcExcel）和動(dòng)態(tài)數(shù)組函數(shù) UNIQUE、CHOOSECOLS 和 FILTER 以編程方式創(chuàng)建主列表和依賴下拉列表。

背景需求

下圖是一張某公司的客戶訂單表原始數(shù)據(jù)：

現(xiàn)在為了將這些數(shù)據(jù)按照人名分類(lèi)進(jìn)行查閱，小編需要制作兩個(gè)下拉列表（客戶姓名和訂單ID），同時(shí)需要滿足訂單ID的值是與客戶姓名相關(guān)的，然后最下面顯示的是根據(jù)訂單ID查詢出來(lái)的訂單詳細(xì)信息，如下圖所示：

使用GcExcel實(shí)現(xiàn)的步驟

步驟 1 - 工作簿初始化

使用 GcExcel API，第一步是初始化 Workbook 的實(shí)例。然后，您可以根據(jù)業(yè)務(wù)需求選擇打開(kāi)現(xiàn)有 Excel 文檔或創(chuàng)建新工作簿。在此博客中，我們將使用帶有 IWorkbook 接口的 API 加載包含客戶訂單歷史記錄的現(xiàn)有 Excel 文檔，如下所示：

Workbook workbook = new Workbook();
workbook.Open("E:\\download\\smartdependentlist\\CustomerOrderHistory.xlsx");

步驟 2 - 獲取工作表

接下來(lái)，您需要獲取用于創(chuàng)建所需報(bào)告的工作表。使用 GcExcel，可以使用 IWorkbook 界面中的 API 獲取工作表。您也可以選擇創(chuàng)建一個(gè)新的工作表。但是，為了簡(jiǎn)化報(bào)表中使用的公式，我們將在存儲(chǔ)訂單歷史記錄的同一工作表上創(chuàng)建報(bào)表，如下所示：

IWorksheet worksheet;
worksheet = workbook.Worksheets["data"]; //OR workbook.Worksheets[0];

步驟 3 - 獲取客戶名稱的唯一列表（用于主下拉列表）

初始化后，需要獲取要添加到報(bào)表中“選擇客戶名稱”部分的主下拉列表的唯一客戶名稱列表。為此，請(qǐng)選擇工作表中底部有空格的任何單元格以垂直溢出數(shù)據(jù);我們使用了單元格T3。接下來(lái)，對(duì)所需的客戶名稱數(shù)據(jù)范圍使用 UNIQUE 函數(shù)。

使用 GcExcel，可以使用帶有 IWorksheet 接口的 API 獲取單元格或單元格區(qū)域，并使用 IRange 接口的 API為其設(shè)置動(dòng)態(tài)公式，如下所示：

IRange rngUniqueCustomerNames;
rngUniqueCustomerNames = worksheet.Range["T3"]; //dummy cell to get unique list of customer names
rngUniqueCustomerNames.Formula2 = "=UNIQUE($B$2:$B$2156)";

執(zhí)行結(jié)果如下：

步驟 4 - 創(chuàng)建主下拉列表

獲得客戶名稱列表后，將其用作使用“列表上的數(shù)據(jù)驗(yàn)證”創(chuàng)建的主下拉列表的源。在此博客示例中，此主下拉列表在單元格 L3 中創(chuàng)建。

使用 GcExcel，使用 IRange 接口的 API 在某個(gè)范圍內(nèi)配置數(shù)據(jù)驗(yàn)證。使用 IValidation 接口的 API 為區(qū)域添加新的驗(yàn)證規(guī)則實(shí)例。選擇 ValidationType.List 列表類(lèi)型數(shù)據(jù)驗(yàn)證選項(xiàng)，并使用 UNIQUE 公式將公式設(shè)置為單元格;這里是 T3，如下圖所示：

IValidation listValidation = worksheet.Range["L3"].Validation;
listValidation.Add(ValidationType.List, ValidationAlertStyle.Stop, ValidationOperator.Equal,"=$T$3#");

請(qǐng)注意，要獲得動(dòng)態(tài)數(shù)組函數(shù)的結(jié)果范圍，單元格引用后跟一個(gè)#請(qǐng)注意，要獲得動(dòng)態(tài)數(shù)組函數(shù)的結(jié)果范圍，單元格引用后跟一個(gè)#。

步驟 5 - 獲取唯一 OrderID 列表（用于依賴下拉列表）

準(zhǔn)備好主下拉列表后，讓我們獲取在主下拉列表中選擇的客戶名稱的唯一 OrderID 列表。為此，請(qǐng)?jiān)俅芜x擇工作表中的任何單元格（在此示例中，此單元格為 $V$2）。在此單元格中使用以下公式獲取所需的 OrderID 列表。

=CHOOSECOLS(
    FILTER(
        Unique_Cus_Order_combo,
        CHOOSECOLS(Unique_Cus_Order_combo,2)=CustomerName
    ),
    1
)

公式解析如下：

定義 CustomerName是指包含主下拉列表的單元格的值;在此示例中，它指的是 =$L$3

定義的Unique_Cus_Order_combo是指訂單 ID 和客戶名稱的唯一組合范圍。它存儲(chǔ)公式 =UNIQUE（data！$A$2：$B$2156），其中范圍 A 和 B 分別包含 OrderID 和 Customer Names。

返回的數(shù)據(jù)部分如下圖所示：

2.內(nèi)部 CHOOSECOLS 函數(shù)提供由 Unique_Cus_Order_combo 表示的范圍內(nèi)的 Customer 名稱列表，以便與 FILTER 函數(shù)中的 CustomerName 匹配。

3.FILTER函數(shù)從所選客戶名稱對(duì)應(yīng)的Unique_Cus_Order_combo中篩選出數(shù)據(jù)，如下圖所示：

4.最后，外部 CHOOSECOLS 函數(shù)從篩選的范圍內(nèi)返回所需的 OrderID 列表，如下所示：

要使用 GcExcel 設(shè)置定義的名稱和動(dòng)態(tài)公式，請(qǐng)按照以下示例代碼進(jìn)行操作：

 workbook.Names.Add("CustomerName", "=$L$3");
 workbook.Names.Add("Unique_Cus_Order_combo", "=UNIQUE(data!$A$2:$B$2156)");

IRange rngUniqueOrderIds;
rngUniqueOrderIds = worksheet.Range["V2"]; //dummy range to get unique list of customer names
rngUniqueOrderIds.Formula2 = "=CHOOSECOLS(FILTER(Unique_Cus_Order_combo, CHOOSECOLS(Unique_Cus_Order_combo,2)=CustomerName), 1)";

步驟 6 - 填充依賴下拉列表

下一步是使用上一步中提取的列表填充 OrderID 下拉列表（在此示例中，它位于 L6）。為此，請(qǐng)?zhí)砑宇?lèi)型列表的數(shù)據(jù)驗(yàn)證（與為主下拉列表添加的數(shù)據(jù)驗(yàn)證相同），并將其源值設(shè)置為包含上一步中公式的單元格值（即 =$V$2）前綴為 #。

IValidation orderIdList = worksheet.Range["L6"].Validation;
orderIdList.Add(ValidationType.List, ValidationAlertStyle.Stop, ValidationOperator.Equal, "=$v$2#");

步驟 7 - 將默認(rèn)值設(shè)置為下拉列表并保存工作簿

最后，使用 IRange 接口的 API將默認(rèn)值設(shè)置為下拉列表，并使用 IWorkbook 接口的 API保存工作簿，如下面的代碼片段所示：

worksheet.Range["L3"].Value = "Paul Henriot";
worksheet.Range["L6"].Value = 10248;
workbook.Save("E:\\download\\smartdependentlist\\CustomerOrderHistoryReport.xlsx");

生成的帶有智能依賴列表的 Excel 文件如下圖所示：

到此這篇關(guān)于C#使用Excel動(dòng)態(tài)函數(shù)實(shí)現(xiàn)生成依賴列表的文章就介紹到這了,更多相關(guān)C# Excel動(dòng)態(tài)函數(shù)生成依賴列表內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論