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

I have successfully created a drop down list in excel using c# with the following code:

Excel.Application app = new Excel.Application();
app.Visible = true; // Makes Excel visible to the user.
string sWbFileName = HttpContext.Current.Server.MapPath("~/Files/CreateEventTemplate.xlsx");
Excel.Workbook xWb = null;
Excel.Worksheet xlWorkSheet;
try
{
xWb = app.Workbooks.Open(
sWbFileName,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing);
}
catch
{
//Create a new workbook if the existing workbook failed to open.
xWb = app.Workbooks.Add();
}
xlWorkSheet = (Excel.Worksheet)xWb.Sheets["Sheet1"];
// Call Populate DropDown sheet
xlWorkSheet.Range["H2:H10"].Validation.Add(Excel.XlDVType.xlValidateList, Type.Missing,
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.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nantha Kishor10y ago
Hi Praveen,

Thanks for the reply. But I am not sure how to use vlookup function to generate cascading dropdown in excel using C#. Could you explain it a bit? with code would be more helpful.
Praveen Kumar10y ago
At first store country and its related state on separate column. Use vlookup function to search states when country is selected from dropdown list.
https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.worksheetfunction.vlookup.aspx