CODEWITHSUNDEEP
c#excelepplus
oGuReiSuo
oGuReiSuo

Reputation: 133

Add drop down in excel using EPplus

I need help. How can i add a drop down in excel using Epplus? No need for validation. I just need to add this to my template. Records in the drop down are not dynamic.

Upvotes: 7

Views: 12386

Answers (2)

Muhammad Saqib Majeed
Muhammad Saqib Majeed

Reputation: 31

Please follow the code for reference. By following the below code you add as much data as you want to display in excel dropdown.

using(ExcelPackage p = new ExcelPackage()) {
//Create Sheet and one dummy sheet and hide it.
ExcelWorksheet ws = p.Workbook.Worksheets.Add("Customer Import");
ExcelWorksheet roughSheet = p.Workbook.Worksheets.Add("Dummy_List");
roughSheet.Hidden = OfficeOpenXml.eWorkSheetHidden.Hidden;

DataTable dt = dataSource; //Your data from database.

if (dt.Rows.Count > 0) {
    roughSheet.Cells["F1"].Value = "Town";
    var count = 1;
    //Add data in dummy sheet.
    foreach(DataRow item in dt.Rows) {
        roughSheet.Cells[count + 1, 6].Value = 
    item["GEO_NAME"].ToString().Trim();
        count++;
    }
    //Start from row and column and max row and column based on data filled 
    in that column.

    startFrom = roughSheet.Cells[2, 6].ToString();
    startTo = roughSheet.Cells[count, 6].ToString();

    startFrom = "$" + startFrom.Substring(0, 1) + "$" + 
    startFrom.Substring(1);
    startTo = "$" + startTo.Substring(0, 1) + "$" + startTo.Substring(1);

    var roughSheetRange = startFrom + ":" + startTo;
    var range = ExcelRange.GetAddress(2, 7, ExcelPackage.MaxRows, 7);

    //finally pick the range from dummy sheet and fill in the desired 
    column of your sheet to make dropdown.
    var rangeListExcelDropDown = 
    ws.DataValidations.AddListValidation(range);
    rangeListExcelDropDown.Formula.ExcelFormula = "Dummy_List!" + 
    roughSheetRange.ToString();
}
}

Upvotes: 1

Siddesh
Siddesh

Reputation: 161

using (ExcelPackage p = new ExcelPackage()) {
    ExcelWorksheet ws = obj.CreateSheet(p, "sheetname", 1, true);
    var unitmeasure = ws.DataValidations.AddListValidation("a1");
    unitmeasure.Formula.Values.Add("Sq Ft");
    unitmeasure.Formula.Values.Add("Meter");
}

Upvotes: 14

Related Questions