关于java:如何在java中使用-Excel-动态函数生成依赖列表

前言

在 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

2. 定义的 Unique_Cus_Order_combo 是指订单 ID 和客户名称的惟一组合范畴。它存储公式 =UNIQUE（data！$A$2：$B$2156），其中范畴 A 和 B 别离蕴含 OrderID 和 Customer Names。

返回的数据局部如下图所示：

3. 外部 CHOOSECOLS 函数提供由 Unique_Cus_Order_combo 示意的范畴内的 Customer 名称列表，以便与 FILTER 函数中的 CustomerName 匹配。

4. FILTER 函数从所选客户名称对应的 Unique_Cus_Order_combo 中筛选出数据，如下图所示：

5. 最初，内部 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 电子表格应用程序中创立流程图