Cascading Dropdownlist in excel using ASP.NET MVC With Example

In this article, I will show you how we will create a cascading or dependent dropdown list using ASP.NET or MVC. Before that, I want to tell you something, In the excel sheet when we are adding cascading dropdown in a column, then we have to use the "=INDIRECT" formula. Here also we will use this formula in ASP.NET and MVC.

Here I am using the ClosedXML Library package in ASP.NET and MVC for excel. For using this package we have to install this in our project. Follow these below steps:

• Open your project and then open Solution Explorer,  Right-click on the Project and select 'Manage NuGet Packages'.
• Now you will need to look for ClosedXML package and once found, you need to click the Install Button.
• The reference of ClosedXML library automatically added in your reference file, You can check it.

Now we will create an ActionResult for Generating Excel Sheet. Write the following code in your controller's ActionResult.

public IActionResult DownloadSampleAssignToOther()
        {
  var wb = new XLWorkbook();
            // var wsPresentation = wb.Worksheets.Add("Presentation");
            var wsData = wb.Worksheets.Add("Data");

            // Fill up some data
            var assignbydepartmentcls = commonMethods.GetDepartmentsByEmployeeID(userId);
            var Priorities = todoRepository.GetAllTodoPriority();
            var departments = commonMethods.GetDepartments();
            var employeecls = commonMethods.GetAllDeptwithEmployee();
            var employees = employeeRepository.GetAllEmployee();


            wsData.Cell(1, 1).Value = "My Department";
            int i = 1;
            foreach (var a in assignbydepartmentcls)
            {
                wsData.Cell(++i, 1).Value = a.name;
            }
            wsData.Range("A2:A" + i).AddToNamed("My Department");
            wsData.Cell(1, 2).Value = "Priority";
            i = 1;
            foreach (var a in Priorities)
            {
                wsData.Cell(++i, 2).Value = a.Name;
            }
            wsData.Range("B2:B" + i).AddToNamed("Priority");
            wsData.Cell(1, 3).Value = "Status";
            i = 1;
            //foreach (var a in departments)
            //{
            wsData.Cell(++i, 3).Value = "PENDING";
            wsData.Cell(++i, 3).Value = "ON-HOLD";
            wsData.Cell(++i, 3).Value = "IN-PROGRESS";
            wsData.Cell(++i, 3).Value = "COMPLETED";
            //}
            wsData.Range("C2:C" + i).AddToNamed("Status");


            wsData.Cell(1, 4).Value = "Assign Department";
            i = 1;
            foreach (var a in departments)
            {
                wsData.Cell(++i, 4).Value = a.Name;
            }
            wsData.Range("D2:D" + i).AddToNamed("Assign Department");
            int j = 4;
            foreach (var a in departments)
            {
                wsData.Cell(1, ++j).Value = a.Name;
                int k = 1;
                foreach (var b in employeecls.Where(m => m.DepartmentID == a.ID).ToList())
                {
                    wsData.Cell(++k, j).Value = employees.Where(h => h.ID == b.EmployeeID).Select(m => (m.FirstName + " " + m.LastName + "(Emp. code-" + m.EmpCode + ")")).FirstOrDefault();
                }
                wsData.Range(wsData.Cell(2, j), wsData.Cell(k, j)).AddToNamed(a.Name);
            }


            DataTable validationTable = new DataTable();
            validationTable.Columns.Add("My Department");
            validationTable.Columns.Add("Name");
            validationTable.Columns.Add("Description");
            validationTable.Columns.Add("Due Date(yyyy-MM-dd)");
            validationTable.Columns.Add("Priority");
            validationTable.Columns.Add("Assign Department");
            validationTable.Columns.Add("Employee");
            validationTable.Columns.Add("Status");
            validationTable.TableName = "My_Task_Details";
            var worksheet = wb.AddWorksheet(validationTable);
            //worksheet.Column(3).Cell(1).SetDataType(XLDataType.Text);
            //worksheet.Range("C3:C2").SetDataType(XLDataType.DateTime);

            worksheet.Column(1).SetDataValidation().List(wsData.Range("My Department"), true);
            worksheet.Column(4).SetDataValidation().Date.EqualOrGreaterThan(DateTime.Now.Date);
            worksheet.Column(5).SetDataValidation().List(wsData.Range("Priority"), true);
            worksheet.Column(6).SetDataValidation().List(wsData.Range("Assign Department"), true);
            worksheet.Column(7).SetDataValidation().InCellDropdown = true;
            worksheet.Column(7).SetDataValidation().Operator = XLOperator.Between;
            worksheet.Column(7).SetDataValidation().AllowedValues = XLAllowedValues.List;
            worksheet.Column(7).SetDataValidation().List("=INDIRECT(SUBSTITUTE(F1," + '"' + " " + '"' + "," + '"' + "_" + '"' + "))");
            worksheet.Column(8).SetDataValidation().List(wsData.Range("Status"), true);
            //wsData.Hide();
            //worksheet2.Hide();



            Byte[] workbookBytes;
            MemoryStream ms = GetStream(wb);
            workbookBytes = ms.ToArray();

            return File(workbookBytes, "application/ms-excel", $"Assign_Task_Details.xlsx");
        }

In this method, I showed you how employees dependant dropdown will filter based on assign department wise.

output response:

That's it. I hope you have found this article helpful. Do let me know via comments and likes.