Create excel sheet with drop down list using epplus

See more:

C#

MVC4

Dears,

i'm working on Mvc web app. I want to create new excel sheet with two drop down lists filled with data from database. I want second drop down list to be dependent on selection changed of first one. I already create file and fill drop down list but i can't find away to make them dependent. Any clue how to do this?

here's my code

public void ExportToXlsx(Stream stream)
    {
        if (stream == null)
            throw new ArgumentNullException("stream");
        // ok, we can run the real code of the sample now
        using (var xlPackage = new ExcelPackage(stream))
        {
            // get handle to the existing worksheet#region

            #region MyRegion Create Main File
            var worksheet = xlPackage.Workbook.Worksheets.Add("Beneficiary");
            var properties = new string[]
                {
                    "  الاسم  ","  رقم الهوية  " ,"  رقم الجوال  ","  نوع المستفيد  ","  IBAN   " ,"  نوع الوظيفة  ","   الرتبة/ المرتبة   " 
                };
            for (var i = 0; i < properties.Length; i++)
            {
                worksheet.Cells[1, i + 1].Value = properties[i];
                worksheet.Cells[1, i + 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
                worksheet.Cells[1, i + 1].Style.Fill.BackgroundColor.SetColor(Color.AliceBlue);
                worksheet.Cells[1, i + 1].Style.Font.Bold = true;
            }
            worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
            worksheet.View.RightToLeft = true;
            #endregion

            #region Ismilitry List
            ExcelWorksheet isMilitryddList = xlPackage.Workbook.Worksheets.Add("IsMilitry");
            var ismilitryValues = new string[]
                {
                    "مدنى","عسكرى" ,"أخرى"
                };
            var valismilitry = worksheet.DataValidations.AddListValidation(worksheet.Cells[2, 4, 10000, 4].Address);
            for (int index = 1; index <= ismilitryValues.Length; index++)
            {
                isMilitryddList.Cells[1,index].Value = ismilitryValues[index - 1];
            }
            var addressismilitry = isMilitryddList.Cells[1, 1, 1, ismilitryValues.Count()].Address;
            var arrismilitry = addressismilitry.Split(':');
            var ismilitrychar1 = arrismilitry[0][0];
            var ismilitrynum1 = arrismilitry[0].Trim(ismilitrychar1);
            var ismilitrychar2 = arrismilitry[1][0];
            var ismilitrynum2 = arrismilitry[1].Trim(ismilitrychar2);
            valismilitry.Formula.ExcelFormula = string.Format("=IsMilitry!${0}${1}:${2}${3}", ismilitrychar1, ismilitrynum1, ismilitrychar2, ismilitrynum2);
            valismilitry.ShowErrorMessage = true;
            valismilitry.Error = "Select from List of Values ...";


            #endregion

            #region Ranktype List
            ExcelWorksheet ddList = xlPackage.Workbook.Worksheets.Add("DropDownList");
            var brokerBranchs = new RankTypeBl().SelectAllRankTypes();
            var val = worksheet.DataValidations.AddListValidation(worksheet.Cells[2, 6, 10000, 6].Address);
            for (int index = 1; index <= brokerBranchs.Count; index++)
            {
                ddList.Cells[index, 1].Value = brokerBranchs[index - 1].Name;
            }
            var address = ddList.Cells[1, 1, brokerBranchs.Count(), 1].Address.ToString();
            var arr = address.Split(':');
            var char1 = arr[0][0];
            var num1 = arr[0].Trim(char1);
            var char2 = arr[1][0];
            var num2 = arr[1].Trim(char2);
            val.Formula.ExcelFormula = string.Format("=DropDownList!${0}${1}:${2}${3}", char1, num1, char2, num2);
            val.ShowErrorMessage = true;
            val.Error = "Select from List of Values ..."; 
            #endregion

            #region rank list
            ExcelWorksheet ddList1 = xlPackage.Workbook.Worksheets.Add("ranks");
            var ranks = new BeneficiaryRankBl().SelectAllBeneficiaryRank();
            var val1 = worksheet.DataValidations.AddListValidation(worksheet.Cells[2, 7, 10000, 7].Address);
            for (int index = 1; index <= ranks.Count; index++)
            {
                ddList1.Cells[index, 1].Value = ranks[index - 1].ArName;
            }
            var address1 = ddList1.Cells[1, 1, ranks.Count(), 1].Address;
            var arr1 = address1.Split(':');
            var char11 = arr1[0][0];
            var num11 = arr1[0].Trim(char11);
            var char22 = arr1[1][0];
            var num21 = arr1[1].Trim(char22);
            val1.Formula.ExcelFormula = string.Format("=ranks!${0}${1}:${2}${3}", char11, num11, char22, num21);
            val1.ShowErrorMessage = true;
            val1.Error = "Select from List of Values ...";
            #endregion

            xlPackage.Save();
        }
    }