如何从c＃或vb.net中读取Excel下拉列表或复选框的值？

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.

我正在使用Microsoft.Office.Interop.Excel来读取工作表单元格的值,但我无法找到显示如何读取下拉列表,复选框和选项按钮的信息。

Thanks!

3 个解决方案

#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.

显然直接访问DropDowns集合是禁止的。解决方法是访问包含下拉列表的单元格的Validation属性,获取它的公式,然后解析列表的位置。

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

#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.

在经历了很多令人头疼的事后,我得到了以下内容,仅用于下拉列表。我也有一个类似但不完全相同的RadioButtons解决方案,但没有尝试过复选框。

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!

Interop返回一个System.Object,其中一个人会期望一个数组(VS调试器告诉我它在技术上是一个System.Object [*] - 但是无论如何,我都不能像数组那样解析它)。 ,或者ControlFormat.List []数组是1索引的。万岁!

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

下面的代码假定一个打开的工作簿和目标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.

我宁愿更喜欢在OpenXmlSDK中这样做 - 但如果我能弄清楚如何做到这一点。我可以找到附加到单元格的DataValidation,解析它指向的工作表和单元格,从SharedStringTable获取它们的SharedString值 - 但无论我做什么,我都无法写回任何数据。 FEH。

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

Exel:从地狱的心里,我刺向你。

#3

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

#1