Thursday, March 27, 2008

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

Here I will demonstrate to you how 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 as reference into your project.

Here are the codes:

Excel.Application xlsApp = new Excel.ApplicationClass();
Excel.Workbook xlsWorkbook;
Excel.Worksheet xlsWorksheet;
object oMissing = System.Reflection.Missing.Value;

//Create new workbook
xlsWorkbook = xlsApp.Workbooks.Add(true);

//Get the first worksheet
xlsWorksheet = (Excel.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");

Excel.DropDowns xlDropDowns;
Excel.DropDown xlDropDown;
xlDropDowns = ((Excel.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;


Cheers!

21 comments:

Anonymous said...

Fantastic code. thanks so much!

Unknown on May 15, 2009 at 2:40 PM said...

How to select the value in the Excel.dropdown?

Srini Nilagiri on June 4, 2009 at 7:00 PM said...

How to read the DropDown Selected value back? any help?

Mike Ellis on June 11, 2009 at 10:39 PM said...

I've only found how to get the index of the selected value; I can't seem to get the text.

Excel.DropDown xlDropDown = (Excel.DropDown)sheet.DropDowns("Drop Down 4");
int index = xlDropDown.ListIndex;

Rakesh Sakhamoori on August 18, 2009 at 8:10 PM said...

The artical is fantastic. I need some more additionas to the drop down. I want to add the drop down to one particular column and repeat the dropdown through out the sheet. Also, i would like to upload the excel and read the selected dropdown from c# code. Thank you in advance.

Unknown on August 31, 2009 at 6:39 PM said...

The artical is fantastic. I need some more additionas to the drop down. I want to add the drop down to one particular column and repeat the dropdown through out the sheet. Also, i would like to upload the excel and read the selected dropdown from c# code. Thank you in advance.

Satya on November 16, 2009 at 11:46 AM said...

Thanks for the post. After writing everything i couldn't find out any excel file. How can i open the excel file with this code on fly .


Thanks in advance...Please urgent

xiaoyu on November 16, 2009 at 1:20 PM said...

Hi Satya, you can open an existing Excel file by using Open method, as below:

xlWorkBook = xlApp.Workbooks.Open("yourexcelfile.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

Satya on November 17, 2009 at 2:08 AM said...

Thank You july,

I have another question.How can i select the dropdown values from range of cell(particualr column).The values are able to see in dropdown, but cant select those values.Can u please lemme know how to select those value.


thanks in advance


Satya

xiaoyu on November 17, 2009 at 9:00 AM said...

Satya,

Perhaps dd.Text will do (dd is the drop down in your excel file).

Anonymous said...

Thank you so much. can you send more about managing excel sheet

thanks
Pradeep
Buildium

Anonymous said...

Thanks for the code, i have added 2 dropdowns and the values are one having country name and other having states. when user selects any country the next dropdown should get filtered by that selected country's states. please help.

Anonymous said...

Here is the code to get the selected value from the Dropdown
for (int j = 1; j < 100; j++)
{
Excel.DropDown xlDropDown1 = (Excel.DropDown)ObjWs.DropDowns("Drop Down "+j.ToString());
string selectedText = xlDropDown1.get_List(xlDropDown1.ListIndex).ToString();
}

--By Jyoti

Votary on September 27, 2010 at 10:11 PM said...

did the DropDown(s) go away with the newest version of Office? I don't have an Excel.DropDown(s) option available to me..

thanks

xiaoyu on September 28, 2010 at 9:18 AM said...

hi votary, which version of excel dll you are using? i think is because of the excel version.

Chetana on July 20, 2011 at 6:49 PM said...

Hi..
Can u plz explain me how to use
Excel.DropDown.OnAction

Unknown on July 21, 2011 at 2:09 PM said...

Really it's very usefull..Nice post

Unknown on July 21, 2011 at 2:12 PM said...

Nice code, really its helpful..

Allaudhin on September 14, 2011 at 5:48 PM said...

HI g,
I used your coding.but i got this
Exception
Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Excel.Range'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{00020846-0000-0000-C000-000000000046}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).


Can u tel how to resolve this one...?
visual studio 2005

allaudhin.be@gmail.com

Allaudhin on September 14, 2011 at 5:49 PM said...

Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Excel.Range'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{00020846-0000-0000-C000-000000000046}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

xiaoyu on September 15, 2011 at 4:13 PM said...

Can you please mention on which line are you getting the exception?

 

Get paid for your opinions! Click on the banner above to join Planet Pulse. Its totally free to sign up, and you can earn UNLIMITED. Find out more by visiting PLANET PULSE.
Sign up for PayPal and start accepting credit card payments instantly. http://www.emailcashpro.com
July Code Blog Copyright © 2010 Blogger Template Designed by Bie Blogger Template