Nantha Kishor

Nantha Kishor

  • NA
  • 3
  • 4.3k

How to create a dependent drop down in excel using C#?

Apr 13 2016 4:50 AM
I have successfully created a drop down list in excel using c# with the following code:
 
  1. Excel.Application app = new Excel.Application();  
  2.         app.Visible = true;  // Makes Excel visible to the user.  
  3.         string sWbFileName = HttpContext.Current.Server.MapPath("~/Files/CreateEventTemplate.xlsx");  
  4.         Excel.Workbook xWb = null;  
  5.         Excel.Worksheet xlWorkSheet;  
  6.         try  
  7.         {  
  8.             xWb = app.Workbooks.Open(  
  9.                             sWbFileName,  
  10.                             Type.Missing,  
  11.                             Type.Missing,  
  12.                             Type.Missing,  
  13.                             Type.Missing,  
  14.                             Type.Missing,  
  15.                             Type.Missing,  
  16.                             Type.Missing,  
  17.                             Type.Missing,  
  18.                             Type.Missing,  
  19.                             Type.Missing,  
  20.                             Type.Missing,  
  21.                             Type.Missing,  
  22.                             Type.Missing,  
  23.                             Type.Missing);  
  24.         }  
  25.         catch  
  26.         {  
  27.             //Create a new workbook if the existing workbook failed to open.  
  28.             xWb = app.Workbooks.Add();  
  29.         }  
  30.   
  31.         xlWorkSheet = (Excel.Worksheet)xWb.Sheets["Sheet1"];  
  32.   
  33.         // Call Populate DropDown sheet  
  34.         xlWorkSheet.Range["H2:H10"].Validation.Add(Excel.XlDVType.xlValidateList, Type.Missing,  
  35.             Excel.XlFormatConditionOperator.xlBetween, country);  
Here 'country' is a string separated by comma. Now I want to create a dependent drop down in range 'I2:I10'. 'I' column cells should be a drop down whose values should populate based on the value chosen in the corresponding row of column 'H'.
 

Example:

In excel column 'H' is a Country field and 'I' is State field. Both the fields are drop down fields. Now user can choose any one country from the drop down, based on that state drop down values will be populated. Now say if user chooses 'India' from the drop down in the cell 'H3', drop down in 'I3' will be populated with the states in India.

This can be done in excel using data validation and Indirect() function. But how to achieve the same in excel using c#? Any help would be appreciated.

 

Answers (2)