Here I am trying to create Dynamic Dropdown list / Cascading in Excel with Source data present in another sheet which is hidden and protected. Aim is to populate 3 inter-dependent dropdowns i.e
1.Select COUNTRY in first DD.
2.Depending on selection of Country Matching list of STATES will be populated in 2nd DD.
3.Depending on STATE selected , District values will be populated..
I am able to do up to 2nd level where dependent STATE is populated.Here in my below code (Range pick3), need to put formula such that "District" values are correctly listed to select.Right now it just populate list of District
Can someone help me here to populate list of District in 3rd Dropdown? It will be very helpful if someone give me solution. Thanks!!
enter code here
class Program {
static void Main(string[] args)
{
Program p = new Program();
p.PopulateDropdown();
}
//This function is used to store source data
private static Range AddToExcelNamedRange(Worksheet worksheet, List<string> primaryList, char col, int row, string rangeName)
{
Range range = worksheet.Range[col.ToString() + row.ToString(), col.ToString() + primaryList.Count().ToString()];
range.Name = rangeName;
foreach (string item in primaryList)
{
worksheet.Cells[row, col - 64] = item;
row++;
}
return range;
}
public void PopulateDropdown()
{
string temporaryPath = Path.GetTempPath();
string temporaryFile = Path.GetTempFileName();
Application appl = new Application();
appl.Visible = true;
Workbook workbook = appl.Workbooks.Open(temporaryFile, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Worksheet worksheet =
(Worksheet)workbook.Worksheets.Add();
Worksheet worksheet1 = (Worksheet)workbook.Worksheets.Add();
List<string> primaryList = new List<string>();
//primaryList.Add("Select Country");
primaryList.Add("India");
primaryList.Add("USA");
List<string> secondaryListA = new List<string>();
//secondaryListA.Add("Select State");
secondaryListA.Add("MH");
secondaryListA.Add("GJ");
secondaryListA.Add("UP");
secondaryListA.Add("KN");
secondaryListA.Add("DL");
secondaryListA.Add("RJ");
List<string> secondaryListB = new List<string>();
//secondaryListB.Add("Select State");
secondaryListB.Add("AL");
secondaryListB.Add("AK");
secondaryListB.Add("AZ");
secondaryListB.Add("CA");
secondaryListB.Add("DE");
secondaryListB.Add("CT");
List<string> secondaryListA1 = new List<string>();
//secondaryListA1.Add("Select District");
secondaryListA1.Add("Latur");
secondaryListA1.Add("Osmanabad");
secondaryListA1.Add("Pune");
List<string> secondaryListA2 = new List<string>();
//secondaryListA2.Add("Select District");
secondaryListA2.Add("Vadodara");
secondaryListA2.Add("Ahamadabad");
secondaryListA2.Add("Surat");
secondaryListA2.Add("Gndhinagar");
Range primaryRange = AddToExcelNamedRange(worksheet, primaryList, 'A', 1, "PrimaryRange");
Range secondaryRangeA = AddToExcelNamedRange(worksheet, secondaryListA, 'B', 1, "India");
Range secondaryRangeB = AddToExcelNamedRange(worksheet, secondaryListB, 'C', 1, "USA");
Range secondaryRangeA1 = AddToExcelNamedRange(worksheet, secondaryListA1, 'D', 1, "Latur");
Range secondaryRangeA2 = AddToExcelNamedRange(worksheet, secondaryListA2, 'E', 1, "Vadodara");
Range pick1 = worksheet1.Range["A5"];
pick1.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, XlFormatConditionOperator.xlBetween, "=PrimaryRange");
Range pick2 = worksheet1.Range["A6"];
var getpassword = "1234";//Pretection and file hide starts here
worksheet.Protect(getpassword, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true);
worksheet.Visible = XlSheetVisibility.xlSheetVeryHidden;
pick1.Value2 = "India"; //< set the parent to have a value
pick2.Validation.Delete();
pick2.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, XlFormatConditionOperator.xlBetween, "=INDIRECT(A5)");
//pick2.Value2 = "MH";
Range pick3 = worksheet1.Range["A7"];
pick3.Value2 = "Latur";
pick3.Validation.Delete();
pick3.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, XlFormatConditionOperator.xlBetween, "=INDIRECT(A7)");
//Need help here To update logic in Formula } }