使用epplus创建包含下拉列表的Excel工作表

本文介绍了使用epplus创建包含下拉列表的Excel工作表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

亲爱的， 我正在使用Mvc网络应用程序。我想创建新的Excel工作表，其中包含两个下拉列表，其中包含数据库中的数据。我希望第二个下拉列表依赖于第一个选择的更改。我已经创建了文件和填充下拉列表但我找不到让它们依赖。任何线索怎么做？ 这里是我的代码

public void ExportToXlsx（Stream stream） { if （stream == null ） throw new ArgumentNullException（& quot; stream& quot;）; // 好的，我们现在可以运行样本的真实代码 使用（ var xlPackage = new ExcelPackage（ stream）） { // 获取现有工作表#region 的句柄 #region MyRegion创建主文件 var worksheet = xlPackage.Workbook.Worksheets.Add（& quot; Beneficiary& quot;）; var properties = new string [] {& quot; الاسم& quot;，& quot; رقمالهوية& quot; ，&安培; QUOT; رقمالجوال& quot;，& quot; نوعالمستفيد& quot;，& quot; IBAN& quot; ，&安培; QUOT; نوعالوظيفة& quot;，& quot; الرتبة/المرتبة& quot; }; for （ var i = 0 ; i& lt; properties.Length; i ++） { worksheet.Cells [ 1 ，i + 1 ]。Value = properties [i]; worksheet.Cells [ 1 ，i + 1 ]。Style.Fill.PatternType = ExcelFillStyle 。固体; 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（& quot; IsMilitry& quot;）; var ismilitryValues = new string [] {& quot;مدنى& quot;，& quot;عسكرى& quot; ，&安培; QUOT;أخرى&安培; QUOT; }; var valismilitry = worksheet.DataValidations.AddListValidation（worksheet.Cells [ 2 ， 4 ， 10000 ， 4 ]。地址）; for （ int index = 1 ; index& lt; = ismilitryValues.Length; index ++） { isMilitryddList.Cells [ 1 ，index] .Value = ismilitryValues [index - 1 ]; } var addressismilitry = isMilitryddList.Cells [ 1 ， 1 ， 1 ，ismilitryValues.Count（）]。地址; var arrismilitry = addressismilitry.Split（' ：）; var ismilitrychar1 = arrismilitry [ 0 ] [ 0 ]; var ismilitrynum1 = arrismilitry [ 0 ]。修剪（ismilitrychar1）; var ismilitrychar2 = arrismilitry [ 1 ] [ 0 ]; var ismilitrynum2 = arrismilitry [ 1 ]。修剪（ismilitrychar2）; valismilitry.Formula.ExcelFormula = string .Format（& quot; = IsMilitry！$ { 0 } $ { 1 }：$ { 2 } $ { 3 }& quot;，ismilitrychar1，ismilitrynum1，ismilitrychar2，ismilitrynum2）; valismilitry.ShowErrorMessage = true ; valismilitry.Error =& quot;从值列表中选择 ...& quot ;; #endregion #region Ranktype List ExcelWorksheet ddList = xlPackage.Workbook.Worksheets.Add（& quot; DropDownList& quot;）; var brokerBranchs = new RankTypeBl（）。SelectAllRankTypes（）; var val = worksheet.DataValidations.AddListValidation（worksheet.Cells [ 2 ， 6 ， 10000 ， 6 ]。地址）; for （ int index = 1 ; index& lt; = brokerBranchs.Count; index ++） { ddList.Cells [index， 1 ]。Value = brokerBranchs [index - 1 ]。姓名; } var address = ddList.Cells [ 1 ， 1 ，brokerBranchs.Count（）， 1 ]。Address.ToString（）; var arr = address.Split（' ：）; var char1 = arr [ 0 ] [ 0 ]; var num1 = arr [ 0 ]。修剪（char1）; var char2 = arr [ 1 ] [ 0 ]; var num2 = arr [ 1 ]。修剪（char2）; val.Formula.ExcelFormula = string .Format（& quot; = DropDownList！$ { 0 } $ { 1 }：$ { 2 } $ { 3 }& quot;，char1，num1，char2，num2）; val.ShowErrorMessage = true ; val.Error =& quot;从值列表中选择 ...& quot ;; #endregion ＃region rank list ExcelWorksheet ddList1 = xlPackage.Workbook.Worksheets.Add（& quot; ranking& quot;）; var ranking = new BeneficiaryRankBl（）。SelectAllBeneficiaryRank（）; var val1 = worksheet.DataValidations.AddListValidation（worksheet.Cells [ 2 ， 7 ， 10000 ， 7 ]。地址）; for （ int index = 1 ; index& lt; = ranking.Count; index ++） { ddList1.Cells [index， 1 ]。值=等级[index - 1 ]。ArName; } var address1 = ddList1.Cells [ 1 ， 1 ，ranking.Count（）， 1 ]。地址; var arr1 = address1.Split（' ：）; var char11 = arr1 [ 0 ] [ 0 ]; var num11 = arr1 [ 0 ]。修剪（char11）; var char22 = arr1 [ 1 ] [ 0 ]; var num21 = arr1 [ 1 ]。修剪（char22）; val1.Formula.ExcelFormula = string .Format（& quot; = rank！$ { 0 } $ { 1 }：$ { 2 } $ { 3 }& quot;，char11，num11，char22，num21）; val1.ShowErrorMessage = true ; val1.Error =& quot;从值列表中选择 ...& quot ;; #endregion xlPackage.Save（）; } }

解决方案

{ 0 }

{ 1 }：

{ 2 }

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(); } }

解决方案

{0}

{1}:

{2}