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

    Question

  •  

    Hi

     

    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?

     

    Thanks.

     

    Thursday, January 10, 2008 4:28 PM

Answers

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

    Range.Validation

    object.

     

     

    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

    Range.Validation

    object.

     

     

    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())
                    {
                        MessageBox.Show(dd.Name);
                        MessageBox.Show(dd.ListIndex.ToString());
                        MessageBox.Show(dd.ListCount.ToString());
                        MessageBox.Show(dd.Value.ToString());
                        MessageBox.Show(dd.Text);
                        MessageBox.Show(dd.Index.ToString());
                        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.

    Thanks,
    Gummadi
    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);

    Excel.

    DropDowns xlDropDowns;

    Excel.

    DropDown xlDropDown;

    xlDropDowns = ((Excel.

    DropDowns)(xlWorkSheet.DropDowns(Missing.Value)));

    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 ,

    for

     

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

    {

     

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

    {

     

    if (j == 3)

    {

     

    //ds.Tables[0].Rows[i].ItemArray[j].ToString();

    data =

    "2";

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

    }

     

    else

    {

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

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

    }

    }

    row++;

     

     

    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