Add drop down in excel using EPplus

Question

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.

Soapstone

See #20260192 – Snappy

Thanks but i need a simplier one – Soapstone

Answer 1

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

Gob

I tried this code and it is working (with approx. 10 rows or less ), but when I tried to populate a dropdown with 40 rows the Excel that Im producing is having a problem. – Angrist

This code works perfectly for me with hundreds of cells. I'd like to add that the values could also be populated in one shot like this: unitmeasure.Formula.Values.Add("square feet,square meter,坪") – Contumelious

It is not true if your string lenght is more than 255 – Fowler

Answer 2

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

Salutatory

Instead of doing all the string manipulation to calculate "roughSheetRange", just use the EPPlus built-in ExcelRange. If you only do this instead, this includes the sheet name and $ and everything: ws.DataValidations.AddListValidation(range).Formula.ExcelFormula = roughSheet.Cells[2, 6, count, 6].FullAddressAbsolute; – Ejaculation