How to read excel list elements (data validation) using C# Excel Interop?

Question

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.

you will need to do a foreach loop or a for loop also there is a similar question with an answer provided here on this link http://stackoverflow.com/questions/3712312/how-do-i-read-the-values-of-excel-dropdowns-or-checkboxes-from-c-sharp-or-vb-net — MethodMan, Dec 22 '14 at 16:07

score 3 · Accepted Answer · edited May 23 '17 at 12:04

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;
}

How to read excel list elements (data validation) using C# Excel Interop?

1 Answers1

Linked