使用OpenXML库在excel中添加下拉列表(ddl)

Question

我目前正在使用Interop库向用户导出excel模板，但客户要求更改为openxml，因为它不需要在服务器上安装excel

使用interop im能够在专用小区中使用interop添加下拉列表，如下

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 = "---";

我的代码使用openxml导出，需要在单元格B9中添加下拉列表作为示例

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

请帮助将下拉列表添加到我的excel文件中

Answer 1

搜索后，我发现了这个解决方案，可以将下拉列表添加到预定义列表中的excel单元格

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