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");
using (var xlPackage = new ExcelPackage(stream))
{
#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();
}
}