前言

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

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

背景需求

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

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

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

步驟 1 - 工作簿初始化

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

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

步驟 2 - 獲取工作表

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

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

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

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

使用 GcExcel，可以使用帶有 IWorksheet 接口的 API 獲取單元格或單元格區(qū)域，并使用 IRange 接口的 API為其設(shè)置動態(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ù)驗證”創(chuàng)建的主下拉列表的源。在此博客示例中，此主下拉列表在單元格 L3 中創(chuàng)建。

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

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

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

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

準備好主下拉列表后，讓我們獲取在主下拉列表中選擇的客戶名稱的唯一 OrderID 列表。為此，請再次選擇工作表中的任何單元格（在此示例中，此單元格為 $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 和客戶名稱的唯一組合范圍。它存儲公式 =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ù)從所選客戶名稱對應(yīng)的Unique_Cus_Order_combo中篩選出數(shù)據(jù)，如下圖所示：

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

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

 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）。為此，請?zhí)砑宇愋土斜淼臄?shù)據(jù)驗證（與為主下拉列表添加的數(shù)據(jù)驗證相同），并將其源值設(shè)置為包含上一步中公式的單元格值（即 =$V$2）前綴為 #。

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

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

最后，使用 IRange 接口的 API將默認值設(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動態(tài)函數(shù)實現(xiàn)生成依賴列表的文章就介紹到這了,更多相關(guān)C# Excel動態(tài)函數(shù)生成依賴列表內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論