Add a Drop Down List into Excel File using C# .NET

Anuradha  6:54 AM  C# No comments :

Here is sample code to add a drop down list into an Excel File using C# .NET.

Before you adding this codes, make sure you have add the Microsoft Excel Object Library from COM as reference into your project.

Here are the codes:

using Microsoft.Office.Interop.Excel ;

  protected void Button1_Click(object sender, EventArgs e)

        {

            string Filename = "samp.xls";

            Application xlsApp = new Application();

            Workbook xlsWorkbook;

            Worksheet xlsWorksheet;

            object oMissing = System.Reflection.Missing.Value;

            //Create new workbook

            xlsWorkbook = xlsApp.Workbooks.Add(true);

            //Get the first worksheet

            xlsWorksheet = (Worksheet)(xlsWorkbook.Worksheets[1]);

            string[] ddl_item = { "Answers", "Autos", "Finance", "Games", "Groups", "HotJobs",

"Maps", "Mobile Web", "Movies", "Music", "Personals", "Real Estate", "Shopping", "Sports",

"Tech", "Travel", "TV", "Yellow Pages" };

            Range xlsRange;

            xlsRange = xlsWorksheet.get_Range("A1", "A1");

            DropDowns xlDropDowns;

            DropDown xlDropDown;

            xlDropDowns = ((DropDowns)(xlsWorksheet.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.Length; i++)

            {

                xlDropDown.AddItem(ddl_item[i], i + 1);

            }

            xlsApp.DisplayAlerts = false;

            xlsWorkbook.Close(true, Filename, null);

            xlsApp.Quit();

            xlsWorksheet = null;

            xlsWorkbook = null;

            xlsApp = null;

        }