用OpenXML库在excel中添加下拉列表

using (SpreadsheetDocument document = SpreadsheetDocument.Create("sample.xlsx", SpreadsheetDocumentType.Workbook))
{
    // 创建WorkbookPart和WorksheetPart
    WorkbookPart workbookPart = document.AddWorkbookPart();
    workbookPart.Workbook = new Workbook();

    WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
    worksheetPart.Worksheet = new Worksheet(new SheetData());

    // 创建Sheet和SheetData
    Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
    Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" };
    sheets.Append(sheet);

    // 保存文档
    workbookPart.Workbook.Save();
}

using (SpreadsheetDocument document = SpreadsheetDocument.Open("sample.xlsx", true))
{
    WorkbookPart workbookPart = document.WorkbookPart;
    WorksheetPart worksheetPart = workbookPart.WorksheetParts.FirstOrDefault();

    // 获取要添加下拉列表的单元格
    Cell cell = worksheetPart.Worksheet.GetFirstChild<SheetData>().Elements<Row>().FirstOrDefault().Elements<Cell>().FirstOrDefault();

    // 创建数据验证规则
    DataValidations dataValidations = worksheetPart.Worksheet.Elements<DataValidations>().FirstOrDefault();
    if (dataValidations == null)
    {
        dataValidations = new DataValidations();
        worksheetPart.Worksheet.AppendChild(dataValidations);
    }

    DataValidation dataValidation = new DataValidation()
    {
        Type = DataValidationValues.List,
        ShowDropDown = true,
        SequenceOfReferences = new ListValue<StringValue> { InnerText = cell.CellReference },
    };

    Formula1 formula1 = new Formula1();
    formula1.Text = "\"Option1,Option2,Option3\""; // 下拉列表的选项

    dataValidation.Append(formula1);
    dataValidations.Append(dataValidation);

    // 保存文档
    worksheetPart.Worksheet.Save();
}