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

Answer 2

After a lot of head-scratching, I got the following to work for dropdowns only. I've also got a similar-but-not-identical solution for RadioButtons, but have not tried checkboxes.

This wasn't made any easier by Interop returning a System.Object where one would expect an array (VS debugger tells me it's technically a System.Object[*] - but whatever that is, I can't parse it like an array), or the ControlFormat.List[] array being 1-indexed. hooray!

The below code assumes an open workbook and the name of the target dropDown

Worksheet worksheet = (Worksheet)workbook.Worksheets[worksheetName];

var control = worksheet.Shapes.Item(dropdownName).ControlFormat;
var vl = GetDropdownList(control);

var targetIndex = IndexOfMatch(targetValue, vl);
control.Value = targetIndex;


// control.List returns a System.Object that may indeed be an array, but it's hard to parse in that format
// let's loop through it, explicitly casting as we go
private List<string> GetDropdownList(ControlFormat control)
{
    var newList = new List<string>();
    // haw! the Excel control-list is one-indexed! And the last item is equal to the count-index.
    for (int i = 1; i <= control.ListCount; i++)
    {
        newList.Add((string)control.List[i]);
    }

    return newList;
}

private int IndexOfMatch(string targetValue, List<string> vals)
{
    int indexMatch = vals.IndexOf(targetValue);

    // the Excel target is 1-indexed, so increase by one
    return ++indexMatch;
}

I would much rather prefer to do this in the OpenXmlSDK -- but d****d if I can figure out how to do it. I can find the DataValidation attached to the cell, parse the worksheet and cells it points to, get their SharedString values from the SharedStringTable -- but no matter what I do, I can't write any data back. Feh.

Exel: From hell's heart I stab at thee.

Answer 3

string selectedText = myDropDown.get_List(myDropDown.ListIndex);