This project has moved. For the latest updates, please go here.

How to create DropDown list

Oct 11, 2011 at 12:33 PM

Hi,

     Am new to EPPlus and wrote some sample programs on this. I need to create a DropDown list and Dependency DropDown. Please let me know how to do using EPPlus.

 

Thanks in advance.

 

Shrikanth

Coordinator
Oct 19, 2011 at 5:23 AM

You can use datavalidation to create a simple in-cell dropdown. You cant create a dependency dropbox in EPPlus. You have to use VBA for that.

Oct 19, 2011 at 6:07 AM

 

Hi,

     Able to create dropdown as well Dependency drop down list. Took time to understand EPPlus on how to implement it. Posting the sample code here. One of my colleque helped me on creating worksheet.Cells object. This is working fine.

-------------------------------------------------------------------------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.DataValidation;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            FileInfo newFile = new FileInfo(@"D:\Learning\DotNet\Test\mynewfile.xlsx");
            using (ExcelPackage excelPackage = new ExcelPackage(newFile))
            {
                ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Test");
                // Add a date time validation
                var validation = worksheet.DataValidations.AddDateTimeValidation("A1");
                // set validation properties
                validation.ShowErrorMessage = true;
                validation.ErrorTitle = "An invalid date was entered";
                validation.Error = "The date must be between 2011-01-31 and 2011-12-31";
                validation.Prompt = "Enter date here";
                validation.Formula.Value = DateTime.Parse("2011-01-01");
                validation.Formula2.Value = DateTime.Parse("2011-12-31");
                validation.Operator = ExcelDataValidationOperator.between;
               
                var val = worksheet.DataValidations.AddListValidation("A2");
                val.Formula.Values.Add("Test1");
                val.Formula.Values.Add("Test2");
                val.Formula.Values.Add("Test3");
                val.Formula.Values.Add("Test4");
                val.Formula.Values.Add("Test5");
                val.Formula.Values.Add("Test6");
                val.Formula.Values.Add("Test7");
                val.Formula.Values.Add("Test8");
                val.Formula.Values.Add("Test9");
                val.Formula.Values.Add("Test10");
                worksheet.Cells["B2"].Value = "Books";
                worksheet.Cells["B3"].Value = "Magazines";
                worksheet.Cells["C2"].Value = "Don Quixote";
                worksheet.Cells["C3"].Value = "Robinson Carusoe";
                worksheet.Cells["C4"].Value = "Jane Eyre";
                worksheet.Cells["C5"].Value = "A Study In Scarlet";
                worksheet.Cells["D2"].Value = "Time";
                worksheet.Cells["D3"].Value = "Newsweek";
                worksheet.Cells["D4"].Value = "MAD";
                worksheet.Cells["D5"].Value = "PC World";
                worksheet.Names.Add("Publications", worksheet.Cells["B2:B3"]);
                worksheet.Names.Add("Books", worksheet.Cells["C2:C5"]);
                worksheet.Names.Add("Magazines", worksheet.Cells["D2:D5"]);
                var val2 = worksheet.DataValidations.AddListValidation("F2");
                val2.Formula.ExcelFormula = "=Publications";

                var val3 = worksheet.DataValidations.AddListValidation("G2");
                val3.Formula.ExcelFormula ="=INDIRECT(F2)";
                val.ShowErrorMessage = true;
                val.Error = "Select from List of Values ...";
                excelPackage.Save();
                excelPackage.Dispose();
            }
        }
    }
}

-------------------------------------------------------------------------------------------------------------------------------

Jun 18, 2013 at 3:46 PM
Thanks for this! You just saved me a lot of effort web searching and reaching the documentation!
Jun 18, 2013 at 8:03 PM
Thanks for sharing! This seems like a great addition to the code samples.
Oct 27, 2015 at 4:42 AM
Edited Oct 27, 2015 at 5:36 AM
Hi Shrikanth,
Thanks for sharing the code here, I just tried the same code in our web application but it's not working. 
This is how the data looks in excel sheet in individual cells-
B2- > Books
B3-> Magazines
C2-> Don Quixote
C3-> Robinson Carusoe
C4-> Jane Eyre
C5-> A Study In Scarlet
D2-> Time
D3-> Newsweek
D4-> MAD
D5-> PC World

Could you please guide me on this if you have any idea?