Add drop down list (ddl) in excel using OpenXML library

Question

Im currently using Interop library in order to export excel templates to user but the client requested to change to openxml because it doesn't required excel to be installed on the server

using interop im able to add drop down list using interop in dedicated cell as following

worksheet.get_Range("A9", "A9").Value = "Region";
var Regions = _iFunctionalRepository.GetRegions(GetCurrentNeutralCultureId(), -1);
var RegionsArray = Regions.Select(x => x.Value + "- " + x.Text).ToArray();
var RegionsList = string.Join(",", RegionsArray);
//sectors ddl 
worksheet.get_Range("B9", "B9").Validation.Delete();
worksheet.get_Range("B9", "B9").Validation.Add(
    Microsoft.Office.Interop.Excel.XlDVType.xlValidateList,
    Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertInformation,
    Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlBetween,
    RegionsList,
    Type.Missing);
worksheet.get_Range("B9", "B9").Validation.IgnoreBlank = true;
worksheet.get_Range("B9", "B9").Validation.InCellDropdown = true;
worksheet.get_Range("B9", "B9").Value = "---";

my code to export using open xml which need to add drop down list in cell B9 as example

using (ClosedXML.Excel.XLWorkbook wb = new ClosedXML.Excel.XLWorkbook())
{
    var worksheet = wb.Worksheets.Add("GeneralInformation");
    var worksheetHiddenSheet = wb.Worksheets.Add("generalHidden");

    worksheet.Range("A1", "A1").Value = "title";
    worksheet.Range("A1", "A1").Style.Font.SetFontSize(12);
    worksheet.Range("A1", "A1").Style.Font.FontColor = ClosedXML.Excel.XLColor.White;
    worksheet.Range("A2", "A1").Style.Fill.BackgroundColor = ClosedXML.Excel.XLColor.DodgerBlue;
    worksheet.Range("A1", "A1").Style.Font.SetBold();
    worksheet.Range("A1", "B1").Merge();


    worksheet.Range("A9", "A9").Value = "regions";
    var Regions = _iFunctionalRepository.GetRegions(GetCurrentNeutralCultureId(), -1);
    var RegionsArray = Regions.Select(x => x.Value + "- " + x.Text).ToArray();
    var RegionsList = string.Join(",", RegionsArray);

    //drop down code 
     

    string randomFileName = "Project Template " + DateTime.Now.ToString("dd-MM-yyyy HH.mm.ss") + ".xlsx";
    //string randomFileName = Guid.NewGuid() + ".xlsx";
    string FilePath = Server.MapPath("~/PDFReports/") + randomFileName;
    //Here saving the file in xlsx
    wb.SaveAs(FilePath);
    wb.Save();

    byte[] filedata = System.IO.File.ReadAllBytes(FilePath);
    string contentType = MimeMapping.GetMimeMapping(FilePath);

    var cd = new System.Net.Mime.ContentDisposition
    {
        FileName = randomFileName,
        Inline = true,
    };
    Response.AppendHeader("Content-Disposition", cd.ToString());
    return File(filedata, contentType);
}

please help in adding drop down lists to my excel file

Answer 1

after searching i found this solution to add drop down to excel cells from predefined list

var Regions = _dbContext.GetRegions(GetCurrentNeutralCultureId(), -1);
var RegionsfromDb = Regions.Select(x => x.Value + "- " + x.Text).ToList(); //extract needed data
var RegionsList = $"\"{String.Join(",", RegionsfromDb)}\""; //seperate items by comma
worksheet.Cell("B9").DataValidation.IgnoreBlanks = true;
worksheet.Cell("B9").DataValidation.InCellDropdown = true;
worksheet.Cell("B9").Value = "---";
worksheet.Cell("B9").DataValidation.List(RegionsList, true);