none
create Excel Sheet with drop down list Using EPplus RRS feed

  • Question

  • 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 the 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 a way 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[]
                {
                    "  Nme  ","  Id  " ," mob  ","type  ","  IBAN   " ,"  ranktype  ","   rank   " 
                };
            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[]
                {
                    "civil","militiray" ,"other"
                };
            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();
        }
    }

    • Moved by CoolDadTx Monday, August 22, 2016 1:59 PM Office related
    Saturday, August 20, 2016 1:37 PM

Answers

  • You have to set a filter to the contents of the second drop down when the selection changed on the first box. Makes sense?

    Create an event handler that handles the SelectionChanged event of your 1st dropdown box, and on that event handler you set the contents of your second textbox.


    My Technet Articles

    If you like this or another reply, vote it up!
    If you think this or another reply answers the original question, mark it or propose it as an answer.


    Mauricio Feijo
    www.mauriciofeijo.com

    • Proposed as answer by David_JunFeng Tuesday, August 30, 2016 8:56 AM
    • Marked as answer by David_JunFeng Tuesday, August 30, 2016 8:56 AM
    Saturday, August 20, 2016 7:30 PM
  • Hi Anaguib,

    According to your description, as far as I know that EPPlus supports VBA, so you could write VBA code to Excel file by using EPPlus. For more information, you could refer to below link:

    https://github.com/pruiz/EPPlus/blob/master/SampleApp/Sample15.cs

    In addition you could seek more help from EPPlus:

    http://epplus.codeplex.com/discussions

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.
    Thanks for your understanding.

    • Proposed as answer by David_JunFeng Tuesday, August 30, 2016 1:46 AM
    • Marked as answer by David_JunFeng Tuesday, August 30, 2016 8:56 AM
    Friday, August 26, 2016 8:39 AM

All replies

  • You have to set a filter to the contents of the second drop down when the selection changed on the first box. Makes sense?

    Create an event handler that handles the SelectionChanged event of your 1st dropdown box, and on that event handler you set the contents of your second textbox.


    My Technet Articles

    If you like this or another reply, vote it up!
    If you think this or another reply answers the original question, mark it or propose it as an answer.


    Mauricio Feijo
    www.mauriciofeijo.com

    • Proposed as answer by David_JunFeng Tuesday, August 30, 2016 8:56 AM
    • Marked as answer by David_JunFeng Tuesday, August 30, 2016 8:56 AM
    Saturday, August 20, 2016 7:30 PM
  • Hi Anaguib,

    Thank you for posting here.

    >>I'm working on Mvc web app,

    For your issue is related to ASP.NET forum. So to get more professional support. I would suggest you re-post your issue on there.http://forums.asp.net/

    Please remember to close your thread by marking useful posts as answer.

    I will move the thread to off-topic forum

    Best Regards,

    Hart


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place. Click HERE to participate the survey.

    Monday, August 22, 2016 5:33 AM
  • Hi Anaguib,

    Did you have a chance to read my answer? Did you try it my solution? Do you need more explanations? Do you have any feedback?


    My Technet Articles

    If you like this or another reply, vote it up!
    If you think this or another reply answers the original question, mark it or propose it as an answer.


    Mauricio Feijo
    www.mauriciofeijo.com

    Monday, August 22, 2016 2:22 PM
  • Hi Anaguib,

    According to your description, since EPPlus is third-party library, I suggest that you could contact with EPPlus developers:

    http://epplus.codeplex.com/discussions

    In addition you could refer to Considerations for server-side Automation of Office

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Thanks for your understanding.

    Tuesday, August 23, 2016 5:59 AM
  • Hi Mauricio,

    Thanks for you 

     yes please i need more explanation 

    Tuesday, August 23, 2016 9:48 AM
  • Hi Anaguib,

    According to your description, as far as I know that EPPlus supports VBA, so you could write VBA code to Excel file by using EPPlus. For more information, you could refer to below link:

    https://github.com/pruiz/EPPlus/blob/master/SampleApp/Sample15.cs

    In addition you could seek more help from EPPlus:

    http://epplus.codeplex.com/discussions

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.
    Thanks for your understanding.

    • Proposed as answer by David_JunFeng Tuesday, August 30, 2016 1:46 AM
    • Marked as answer by David_JunFeng Tuesday, August 30, 2016 8:56 AM
    Friday, August 26, 2016 8:39 AM