.net - How do I read the values of Excel dropdowns or checkboxes from c# or vb.net?

Question

I'm using Microsoft.Office.Interop.Excel to read the values of cells of a worksheet, but I'm unable to find information that shows how to read dropdowns, checkboxes and option buttons.

Thanks!

Answer 1

Apparently accessing the DropDowns collection directly is verboten. A workaround is to access the Validation property of the cell containing the dropdown, get it's formula and then parse out the location of the list.

Excel.Range dropDownCell = (Excel.Range)ws.get_Range("A1", "A1"); //cell containing dropdown
string formulaRange = dropDownCell.Validation.Formula1;
string[] splitFormulaRange = formulaRange.Substring(1,formulaRange.Length-1).Split(':');

Excel.Range valRange = (Excel.Range)ws.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];
     System.Console.WriteLine(aCell.Value2);
    }
}