如何用C# ClosedXML/Aspose制作Excel联动下拉菜单？

实现思路 #

先把country-state-city的映射数据存到一个隐藏工作表，给每个国家对应的州、每个州对应的城市创建命名区域，再通过Excel的INDIRECT函数让子下拉菜单关联父选项的选择结果。

代码示例 #

using ClosedXML.Excel;
using System.Collections.Generic;
using System.Linq;

// 准备联动数据
var countryStateCity = new Dictionary<string, Dictionary<string, List<string>>>
{
    {
        "中国", new Dictionary<string, List<string>>
        {
            { "北京", new List<string> { "朝阳区", "海淀区", "西城区" } },
            { "上海", new List<string> { "黄浦区", "徐汇区", "浦东新区" } }
        }
    },
    {
        "美国", new Dictionary<string, List<string>>
        {
            { "加利福尼亚州", new List<string> { "洛杉矶", "旧金山", "圣地亚哥" } },
            { "纽约州", new List<string> { "纽约市", "布法罗", "罗切斯特" } }
        }
    }
};

using var workbook = new XLWorkbook();
// 用于存放联动数据的隐藏工作表
var dataSheet = workbook.Worksheets.Add("Data");
dataSheet.Hide();

int row = 1;
// 写入所有国家，并创建对应州的命名区域
foreach (var countryEntry in countryStateCity)
{
    string country = countryEntry.Key;
    dataSheet.Cell(row, 1).Value = country;
    row++;

    // 写入当前国家的所有州，创建对应城市的命名区域
    int stateStartRow = row;
    foreach (var stateEntry in countryEntry.Value)
    {
        string state = stateEntry.Key;
        dataSheet.Cell(row, 2).Value = state;
        row++;

        // 写入当前州的所有城市，创建城市的命名区域
        int cityStartRow = row;
        foreach (var city in stateEntry.Value)
        {
            dataSheet.Cell(row, 3).Value = city;
            row++;
        }
        // 给当前州的城市创建命名区域（替换空格为下划线避免Excel命名报错）
        dataSheet.Range(cityStartRow, 3, row - 1, 3).Name = state.Replace(" ", "_");
    }
    // 给当前国家的州创建命名区域
    dataSheet.Range(stateStartRow, 2, row - 1, 2).Name = country.Replace(" ", "_");
}

// 用户操作的工作表
var mainSheet = workbook.Worksheets.Add("Main");
mainSheet.Cell(1, 1).Value = "Country";
mainSheet.Cell(1, 2).Value = "State";
mainSheet.Cell(1, 3).Value = "City";

// 设置Country列的下拉菜单（A2及以下单元格）
var countryList = countryStateCity.Keys.ToList();
var countryValidation = mainSheet.Range("A2:A100").DataValidation.List();
countryValidation.Style = XlDataValidationStyle.InList;
countryValidation.IgnoreBlanks = true;
countryValidation.Source = $"\"{string.Join(",", countryList)}\"";

// 设置State列的下拉菜单（B2及以下单元格），关联A列的选择
var stateValidation = mainSheet.Range("B2:B100").DataValidation.List();
stateValidation.Style = XlDataValidationStyle.InList;
stateValidation.IgnoreBlanks = true;
// 用INDIRECT引用A列对应单元格的命名区域
stateValidation.Source = "INDIRECT(SUBSTITUTE(A2,\" \",\"_\"))";

// 设置City列的下拉菜单（C2及以下单元格），关联B列的选择
var cityValidation = mainSheet.Range("C2:C100").DataValidation.List();
cityValidation.Style = XlDataValidationStyle.InList;
cityValidation.IgnoreBlanks = true;
cityValidation.Source = "INDIRECT(SUBSTITUTE(B2,\" \",\"_\"))";

// 保存文件
workbook.Save("联动下拉菜单.xlsx");

实现思路 #

和ClosedXML逻辑一致：用隐藏工作表存储映射数据，创建命名区域，通过INDIRECT函数关联父选项的选择结果，利用Aspose.Cells的API直接操作数据验证规则。

代码示例 #

using Aspose.Cells;
using System.Collections.Generic;

// 准备联动数据
var countryStateCity = new Dictionary<string, Dictionary<string, List<string>>>
{
    {
        "中国", new Dictionary<string, List<string>>
        {
            { "北京", new List<string> { "朝阳区", "海淀区", "西城区" } },
            { "上海", new List<string> { "黄浦区", "徐汇区", "浦东新区" } }
        }
    },
    {
        "美国", new Dictionary<string, List<string>>
        {
            { "加利福尼亚州", new List<string> { "洛杉矶", "旧金山", "圣地亚哥" } },
            { "纽约州", new List<string> { "纽约市", "布法罗", "罗切斯特" } }
        }
    }
};

var workbook = new Workbook();
// 隐藏工作表存数据
Worksheet dataSheet = workbook.Worksheets.Add("Data");
dataSheet.IsVisible = false;

int row = 0;
foreach (var countryEntry in countryStateCity)
{
    string country = countryEntry.Key;
    dataSheet.Cells[row, 0].PutValue(country);
    row++;

    int stateStartRow = row;
    foreach (var stateEntry in countryEntry.Value)
    {
        string state = stateEntry.Key;
        dataSheet.Cells[row, 1].PutValue(state);
        row++;

        int cityStartRow = row;
        foreach (var city in stateEntry.Value)
        {
            dataSheet.Cells[row, 2].PutValue(city);
            row++;
        }
        // 创建城市的命名区域
        Name cityName = workbook.Worksheets.Names.Add(state.Replace(" ", "_"));
        cityName.RefersTo = $"=Data!${CellsHelper.ColumnIndexToName(2)}${cityStartRow + 1}:${CellsHelper.ColumnIndexToName(2)}${row}";
    }
    // 创建州的命名区域
    Name stateName = workbook.Worksheets.Names.Add(country.Replace(" ", "_"));
    stateName.RefersTo = $"=Data!${CellsHelper.ColumnIndexToName(1)}${stateStartRow + 1}:${CellsHelper.ColumnIndexToName(1)}${row}";
}

// 用户操作的工作表
Worksheet mainSheet = workbook.Worksheets[0];
mainSheet.Name = "Main";
mainSheet.Cells[0, 0].PutValue("Country");
mainSheet.Cells[0, 1].PutValue("State");
mainSheet.Cells[0, 2].PutValue("City");

// 设置Country列下拉
Validation countryValidation = mainSheet.Validations.Add("A2:A100");
countryValidation.Type = ValidationType.List;
countryValidation.IgnoreBlank = true;
countryValidation.InCellDropDown = true;
// 拼接国家列表作为数据源
string countrySource = string.Join(",", countryStateCity.Keys);
countryValidation.Formula1 = $"\"{countrySource}\"";

// 设置State列下拉，关联A列
Validation stateValidation = mainSheet.Validations.Add("B2:B100");
stateValidation.Type = ValidationType.List;
stateValidation.IgnoreBlank = true;
stateValidation.InCellDropDown = true;
stateValidation.Formula1 = "INDIRECT(SUBSTITUTE(A2,\" \",\"_\"))";

// 设置City列下拉，关联B列
Validation cityValidation = mainSheet.Validations.Add("C2:C100");
cityValidation.Type = ValidationType.List;
cityValidation.IgnoreBlank = true;
cityValidation.InCellDropDown = true;
cityValidation.Formula1 = "INDIRECT(SUBSTITUTE(B2,\" \",\"_\"))";

// 保存文件
workbook.Save("联动下拉菜单_Aspose.xlsx");

注意事项 #

• 如果数据包含空格、特殊字符，创建命名区域时要替换成下划线等合法字符，避免Excel识别错误。
• 隐藏数据工作表是为了防止用户误操作原始映射数据，也可以选择将数据放在主工作表的隐藏列中。

内容的提问来源于stack exchange，提问作者fazal mithani