如何使用C#Excel Interop读取Excel列表元素(数据验证)? [英] How to read excel list elements (data validation) using C# Excel Interop?

问题描述

我有一个Excel文件，其单元格已将数据验证"设置为列表".因此，此单元格是一个下拉列表.如何使用C#Excel Interop读取此列表的元素?我可以轻松读取当前选择的值:

I have an Excel file with a cell that has set "Data Validation" to "List". Thanks to that, this cell is a drop down list. How can I read the elements of this list using C# Excel Interop? I can easily read the currently selected value:

Range range = xlWorkSheet.UsedRange; // Worksheet
string cellValue = (range.Cells[x, y] as Excel.Range).Value2.ToString();

但是我无法读取此单元格包含的下拉列表的内容.

but I cannot read the content of the dropdown list that this cell contains.

推荐答案

如此处所述

As it was stated here How do I read the values of Excel dropdowns or checkboxes from c# or vb.net? there is no easy way to do that, but it is possible to create custom function and do it manually. Below is my function that that reads drop down values into a string list. This function is based on an a question mentioned before, but I added support for formulas on other sheets.

List<string> ReadDropDownValues(Excel.Workbook xlWorkBook, Excel.Range dropDownCell)
{
    List<string> result = new List<string>();

    string formulaRange = dropDownCell.Validation.Formula1;
    string[] formulaRangeWorkSheetAndCells = formulaRange.Substring(1, formulaRange.Length - 1).Split('!');
    string[] splitFormulaRange = formulaRangeWorkSheetAndCells[1].Split(':');
    Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(formulaRangeWorkSheetAndCells[0]);

    Excel.Range valRange = (Excel.Range)xlWorkSheet.get_Range(splitFormulaRange[0], splitFormulaRange[1]);
    for (int nRows = 1; nRows <= valRange.Rows.Count; nRows++)
    {
        for (int nCols = 1; nCols <= valRange.Columns.Count; nCols++)
        {
            Excel.Range aCell = (Excel.Range)valRange.Cells[nRows, nCols];
            if (aCell.Value2 != null)
            {
                result.Add(aCell.Value2.ToString());
            }
        }
    }

    return result;
}

这篇关于如何使用C#Excel Interop读取Excel列表元素(数据验证)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！