Create all the rows of a column as a dropdownlist in excel sheet using c#

Question

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.

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;

score 1 · Answer 1 · answered Apr 21 '15 at 18:38

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

Thank you for the reply, for some reason i switched to closed xml dll insted of Interop excel so the range is not working now. But my question is still same i created the blank excel sheet now i have to fill 1st column of all the rows of the sheet. — user3369120, Apr 23 '15 at 09:32
oh, if you are using closedXml this answer is helpful no more, unfortunately i'm not very familar with closedXml but if you wanna get the last used row, take a look at http://stackoverflow.com/questions/22747360/append-to-excel-file-with-closedxml @user3369120 — Technovation, Apr 23 '15 at 15:04

Create all the rows of a column as a dropdownlist in excel sheet using c#

1 Answers1