如何在java中使用 Excel 動態函式生成依賴列表

發表於2024-02-28
JavaExcel函式

前言

在Excel 中，依賴列表或級聯下拉選單表示兩個或多個列表，其中一個列表的項根據另一個列表而變化。依賴列表通常用於Excel的業務報告，例如學術記分卡中的【班級-學生】列表、區域銷售報告中的【區域-國家/地區】列表、人口儀表板中的【年份-區域】列表以及生產摘要報告中的【單位-行-產品】列表等等。

在本部落格中，小編將為大家介紹如何藉助葡萄城公司的Java API 元件GrapeCity Documents for Excel （以下簡稱GcExcel）和動態陣列函式 UNIQUE、CHOOSECOLS 和 FILTER 以程式設計方式建立主列表和依賴下拉選單。

背景需求

下圖是一張某公司的客戶訂單表原始資料：
在這裡插入圖片描述

現在為了將這些資料按照人名分類進行查閱，小編需要製作兩個下拉選單（客戶姓名和訂單ID），同時需要滿足訂單ID的值是與客戶姓名相關的，然後最下面顯示的是根據訂單ID查詢出來的訂單詳細資訊，如下圖所示：
在這裡插入圖片描述

使用 GcExcel實現的步驟

步驟 1 - 工作簿初始化

使用 GcExcel API，第一步是初始化 Workbook 的例項。然後，可以根據業務需求選擇開啟現有 Excel 文件或建立新工作簿。在此部落格中，小編將使用帶有 IWorkbook 介面的 API) 載入包含客戶訂單歷史記錄的現有 Excel 文件，如下所示：

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

步驟 2 - 獲取工作表

接下來，小編需要獲取用於建立所需報告的工作表。使用 GcExcel，可以使用 IWorkbook 介面中的 API) 獲取工作表。如下所示：

IWorksheet worksheet;
worksheet = workbook.getWorksheets().get(0);

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

初始化工作簿後，需要獲取新增到報表中“選擇客戶名稱”部分的主下拉選單的唯一客戶名稱列表，並對所需的客戶名稱資料範圍使用 UNIQUE 函式。使用 GcExcel，可以使用帶有 IWorksheet 介面的 API 獲取單元格或單元格區域，並使用 IRange 介面的 API) 為其設定動態公式，如下所示：

IWorksheet worksheet;
worksheet = workbook.getWorksheets().get(0);

顯示效果如下所示：
在這裡插入圖片描述

步驟 4 - 建立主下拉選單

獲得客戶名稱列表後，將其用作為客戶姓名下拉選單的資料來源。在此部落格示例中，此主下拉選單在單元格 L3 中建立。 小編使用GcExcel的IRange 介面的 API) 在某個範圍內配置資料驗證。使用 IValidation 介面的 API) 為區域新增新的驗證規則例項。選擇 ValidationType.List 列表型別資料驗證選項，並使用 UNIQUE 公式將公式設定為單元格，如下圖所示：

IValidation customerNameList = worksheet.getRange("L3").getValidation();
customerNameList.add(ValidationType.List, ValidationAlertStyle.Stop, ValidationOperator.Equal, "=$T$3#", null);

需要注意的是，要獲得動態陣列函式的結果範圍，單元格引用後跟一個\#。
在這裡插入圖片描述

步驟 5 - 獲取唯一 OrderID（訂單ID） 列表（用於依賴下拉選單）

設定完主下拉選單後，需要獲取在主下拉選單中客戶名稱的唯一 OrderID 列表。為此，需要再次選擇工作表中的單元格（在此示例中，此單元格為 $V$2）。在此單元格中使用以下公式獲取所需的 OrderID 列表。

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

公式解析如下：

  1. 定義 CustomerName是指包含主下拉選單的單元格的值;在此示例中，它指的是 =$L$3
    在這裡插入圖片描述
  1. 定義的Unique_Cus_Order_combo是指訂單 ID 和客戶名稱的唯一組合範圍。它儲存公式 =UNIQUE（data！$A$2：$B$2156），其中範圍 A 和 B 分別包含 OrderID 和 Customer Names。

在這裡插入圖片描述

返回的資料部分如下圖所示：
在這裡插入圖片描述

  1. 內部 CHOOSECOLS 函式提供由 Unique_Cus_Order_combo 表示的範圍內的 Customer 名稱列表，以便與 FILTER 函式中的 CustomerName 匹配。
    在這裡插入圖片描述
  1. FILTER函式從所選客戶名稱對應的Unique_Cus_Order_combo中篩選出資料，如下圖所示：
    在這裡插入圖片描述
  1. 最後，外部 CHOOSECOLS 函式從篩選的範圍內返回所需的 OrderID 列表，如下所示：
    在這裡插入圖片描述

使用 GcExcel 設定定義的名稱和動態公式的程式碼如下：

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

IRange rngUniqueOrderIds;
rngUniqueOrderIds = worksheet.getRange("V2"); //dummy rnage to get unique list of customer names
rngUniqueOrderIds.setFormula2("=CHOOSECOLS(FILTER(Unique_Cus_Order_combo, CHOOSECOLS(Unique_Cus_Order_combo,2)=CustomerName), 1)");

步驟 6 - 填充依賴下拉選單

獲取唯一訂單ID後，緊接著需要提取的列表填充 OrderID 下拉選單（在此示例中，它位於 L6）。為此，請新增型別列表的資料驗證（與為主下拉選單新增的資料驗證相同），並將其源值設定為包含上一步中公式的單元格值（即 =$V$2）字首為 \#。

IValidation orderIdList = worksheet.getRange("L6").getValidation();
orderIdList.add(ValidationType.List, ValidationAlertStyle.Stop, ValidationOperator.Equal, "=$v$2#", null);

步驟 7 - 將預設值設定為下拉選單並儲存工作簿

最後，使用 IRange 介面的 API) 將預設值設定為下拉選單，並使用 IWorkbook 介面的 API) 儲存工作簿，如下面的程式碼片段所示：

worksheet.getRange("L3").setValue("Paul Henriot");
worksheet.getRange("L6").setValue(10248);
workbook.save("E:\\download\\smartdependentlist\\CustomerOrderHistoryReport.xlsx");

生成的帶有智慧依賴列表的 Excel 檔案如下圖所示：
在這裡插入圖片描述

附上完整的程式碼工程檔案：https://gitee.com/GrapeCity/gc-excel_-dynamic_functions

總結

以上就是使用Java生成依賴列表的全過程，如果您想了解更多資訊，歡迎點選這裡檢視更多資料。

擴充套件連結：

輕鬆構建低程式碼工作流程：簡化繁瑣任務的利器

最佳化預算管理流程：Web端實現預算編制的利器

如何在.NET電子表格應用程式中建立流程圖

相關文章