使用c#在excel表中创建列的所有行作为下拉列表

My requirement is to export a blank excel sheet with 3 columns in which 1st columns of all rows as a dropdownlist, so that user can use this work sheet to modify the data as per their need. I am using c# to exporting file.

我的要求是导出一个包含3列的空白excel表，其中所有行的第一列作为下拉列表，以便用户可以使用此工作表根据需要修改数据。我正在使用c#来导出文件。

I already worked on it but that at the moment, it only creates a dropdown list in a particular cell but I want to make all the rows of first column as a dropdown list .

我已经做过了，但是现在，它只在一个特定的单元格中创建下拉列表但是我想把第一列的所有行作为下拉列表。

Below is the code I am using:

下面是我正在使用的代码:

object oMissing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application app;
Microsoft.Office.Interop.Excel.Worksheet wksheet;
Microsoft.Office.Interop.Excel.Workbook wkbook;
app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = false;
wkbook = app.Workbooks.Add(true);
wksheet = (Microsoft.Office.Interop.Excel.Worksheet)wkbook.ActiveSheet;
string[] ddl_item = 
{
    "Answers",
    "Autos",
    "Finance",
    "Games",
    "Groups",
    "HotJobs",
    "Maps",
    "Mobile Web",
    "Movies",
    "Music",
    "Personals",
    "Real Estate",
    "Shopping",
    "Sports",
    "Tech",
    "Travel",
    "TV",
    "Yellow Pages"
};
Microsoft.Office.Interop.Excel.Range xlsRange;
xlsRange = wksheet.get_Range("A1", "A1");

Microsoft.Office.Interop.Excel.DropDowns xlDropDowns;
Microsoft.Office.Interop.Excel.DropDown xlDropDown;

xlDropDowns = ((Microsoft.Office.Interop.Excel.DropDowns)(wksheet.DropDowns(oMissing)));
xlDropDown = xlDropDowns.Add((double)xlsRange.Left, (double)xlsRange.Top, (double)xlsRange.Width, (double)xlsRange.Height, true);

//Add item into drop down list
for (int i = 0; i < ddl_item.Count(); i++)
{
    xlDropDown.AddItem(ddl_item[i], i + 1);
}
app.Visible = true;

1 个解决方案

#1

i just saw your question, it's a little bit late, but your problem is in your range, you can change your code as this to fulfill your needs:

我刚看到你的问题，有点晚了，但是你的问题在你的范围内，你可以改变你的代码，以满足你的需要:

        //change your range
        Range range = worksheet.UsedRange;
        //this part makes all the in-range rows of first column as a dropdown list
        int row;
        for (row = 1; row <= range.Rows.Count; row++)
        {
            xlDropDowns = ((DropDowns) (worksheet.DropDowns(Type.Missing)));
            xlDropDown = xlDropDowns.Add((double) range[row, 1].Left, (double) range[row, 1].Top,
                (double) range[row, 1].Width, (double) range[row, 1].Height, true);
            string[] ddl_item =
            {
                "Answers",
                "Autos",
                "Finance",
                "Games",
                "Groups",
                "HotJobs",
                "Maps",
                "Mobile Web",
                "Movies",
                "Music",
                "Personals",
                "Real Estate",
                "Shopping",
                "Sports",
                "Tech",
                "Travel",
                "TV",
                "Yellow Pages"
            };
            for (int i = 0; i < ddl_item.Count(); i++)
            {
                xlDropDown.AddItem(ddl_item[i], i + 1);
            }
        }