Add dropdown list in the Excel sheet using C#

Question

I am filling the Excel cells as row and column using C# in my entire project as given below. Now there is a new requirement to add a dropdownlist in the particular cell.

var oXl = new Microsoft.Office.Interop.Excel.Application {DisplayAlerts = false};
var oWb = oXl.Workbooks.Open(excelFileName);
Microsoft.Office.Interop.Excel._Worksheet oSheet = oWb.Sheets[2];
oSheet.Cells[row, 1] = changeName + "\t";
oSheet.Cells[row, 2] = newName + "\t";
oSheet.Cells[row, 3] = (i + 1) + "\t";
oSheet.Cells[row, 4] = filename;
oSheet.Cells[row, 5] = type;
oSheet.Cells[row, 8] = dropdown; // Here I need to add a dropdown list

How can I do this?

Suresh Mahawar · Accepted Answer · 2014-09-04 07:55:05Z

16

First make a list for dropdown

        var list = new System.Collections.Generic.List<string>();
        list.Add("Charlie");
        list.Add("Delta");
        list.Add("Echo");
        var flatList = string.Join(",", list.ToArray());

then add this list as dropdown in the particular cell as below

var cell = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[row, 8];
            cell.Validation.Delete();
            cell.Validation.Add(
               XlDVType.xlValidateList,
               XlDVAlertStyle.xlValidAlertInformation,
               XlFormatConditionOperator.xlBetween,
               flatList,
               Type.Missing);

            cell.Validation.IgnoreBlank = true;
            cell.Validation.InCellDropdown = true;

answered Sep 4, 2014 at 7:55

Suresh Mahawar

1,59816 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Suresh Mahawar Over a year ago

add default value like as cell.Value = "default value";

Tot Zam Over a year ago

If you are quoting from another source, at least give credit to the original author: clear-lines.com/blog/post/…

user1017882 Over a year ago

@TotZam - no, not necessary. If you were to credit code reuse as a developer you'd be there all day. This is pretty bog-standard stuff.

Ozgur Saklanmaz Over a year ago

I don't know if it used to be like this or if there is a mistake in the writing of the answer. Combining with "," doesn't work. ";" combining is necessary. stackoverflow.com/a/65697065/14875740

code7amza Over a year ago

@OzgurSaklanmaz Combine with CultureInfo.CurrentCulture.TextInfo.ListSeparator to avoid Locale issues (instead of , or ;)

|

Collectives™ on Stack Overflow

Add dropdown list in the Excel sheet using C#

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related