How to create dropdownlist in excel on the fly in C#?





    I'm trying to create a dropdownlist on the fly in excel worksheets by C#, i saw an interface called Microsoft.Office.Interop.Excel.DropDown but still cant find a way of creating it yet, could someone help please?


    I know this can be done in VB, is there a similar way to do it in C# please?




    Thursday, January 10, 2008 4:28 PM


  • Ok, after three hours non stop playing around with Range, i finally found a way -- dropdownlists can be created by editing the





    Thursday, January 10, 2008 5:01 PM

All replies

  • Ok, after three hours non stop playing around with Range, i finally found a way -- dropdownlists can be created by editing the





    Thursday, January 10, 2008 5:01 PM
  • I wouldn't say your solution creates an Excel DropDown via C# on the fly.

    Here is how to do it:

    Excel.Range range = sheet.get_Range("A1", "A1");
    Excel.DropDowns xlDropDowns;
    Excel.DropDown xlDropDown;
    xlDropDowns = ((Excel.DropDowns)(sheet.DropDowns(oMissing)));
    xlDropDown = xlDropDowns.Add((double)range.Left, (double)range.Top, (double)range.Width, (double)range.Height, true);

    //Add items into drop down list
    for (int i = 0; i < items.Length; i++)
        xlDropDown.AddItem(items[i], i + 1);

    I'm trying now to find a way to get the value from a DropDown created programmatically.  All I can seem to get is the index:
    Excel.DropDown xlDropDown = (Excel.DropDown)sheet.DropDowns(list_name);
    int index = xlDropDown.ListIndex;
    Thursday, June 11, 2009 2:35 PM
  • Hi,

    I have used the method you have mentioned to create the drop down.

    But i am not able to retrieve the index value that i have assigned.
    I have created the drop down using the following code.

    int[] geoId = new int[] { 3003, 3004, 3005 };
                                        string[] geoValue = new string[] { "geo3003", "geo3004", "geo3005" };
                                        for (int i = 0; i < 3; i++)
                                            xlDropDown.AddItem(geoValue[i], geoId[i]); //text and Index values

    Now i am trying to retrieve the index values using the following code.

    Worksheet ws = Application.ActiveSheet;
                int i = 0;

                foreach(Microsoft.Office.Interop.Excel.DropDown dd in (Microsoft.Office.Interop.Excel.DropDowns)ws.DropDowns(Type.Missing))
                    if (dd.Name == headerId.ToUpper())
                        Object test = dd.get_List(dd.ListIndex);                    

    I am able to successfully access the drop down and also get the text in the drop down that has been selected by the user.
    But i also need the corresponding key value for the selected item.

    Lets say if the user selected "geo3004", i am getting the dd.Text as "geo3004".
    But i am not able to the Key value which is "3004". I have tried various options but in vain.
    Is there a way that i can get the Key value for the selected item.

    Wednesday, July 22, 2009 6:32 PM
  • Hi Winifred,


      Can u tel me how to create using validations ?

    I created the dropdown list bos using the above methos. Now i am facing 2 problems,


    1. After creating the dropdown list box in Sheet1, i coudlnt able to store the value retrieved from the database into the cell.

    Code used :


    const string upperCellGrade = "D2";


    const string lowerCellGrade ="D2";

    range_grade = xlWorkSheet.get_Range(upperCellGrade, lowerCellGrade);


    DropDowns xlDropDowns;


    DropDown xlDropDown;

    xlDropDowns = ((Excel.


    xlDropDown = xlDropDowns.Add((

    double)range_grade.Left, (double)range_grade.Top, (double)range_grade.Width, (double)range_grade.Height, true);


    // Add items into drop down list


    for (int k = 0; k < grade_Array.Length; k++)


    xlDropDown.AddItem(grade_Array[k], k + 1);




    Code For Storing the values in D2 cel ,



    (i = 0; i < ds.Tables[0].Rows.Count; i++)



    for (j = 0; j < ds.Tables[0].Columns.Count; j++)



    if (j == 3)




    data =


    xlWorkSheet.Cells[row, j + 1] = data;





    data = ds.Tables[0].Rows[i][j].ToString();

    xlWorkSheet.Cells[row, j + 1] = data;






    But still iam getting the dropdown list box in d2, rather than getting   2

    My dropdown values will be 1,2,3,4

    Sunday, September 26, 2010 3:10 AM
  • Do you know how to use the onAction property with the dropdown?
    Thursday, September 05, 2013 7:59 PM