使用EPplus在excel中添加下拉列表

本文关键字：添加 下拉列表 excel EPplus 使用 | 更新日期: 2023-09-27 18:18:06

我需要帮助。如何使用Epplus在excel中添加下拉列表?不需要验证。我只需要把这个添加到我的模板。下拉列表中的记录不是动态的。

使用EPplus在excel中添加下拉列表

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

请按照代码进行参考。按照下面的代码，你可以添加尽可能多的数据，你想在excel下拉列表中显示。

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();
}
}
相关文章：