How to view values ​​of excel dropdowns or checkboxes with c # or vb.net?

Question

I use Microsoft.Office.Interop.Excel to read the values ​​of the worksheet cells, but I can not find information that shows how to read the drop-down lists, check boxes and selection buttons.

Thank!

Answer 1

Obviously, access to the DropDowns collection is directly verboten . The workaround is to access the Validation property of the cell containing the drop-down list, get its formula, and then analyze 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

, , . , RadioButtons, .

, Interop System.Object, ( VS System.Object[*]), , , ), ControlFormat.List[], 1-. !

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 hard to parse in that format
// let 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;
}

OpenXmlSDK - d **** d, , . DataValidation, , , , SharedString SharedStringTable - , , . .

Exel: .

Answer 3

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