Open XML with data validation for named range

public static void AddDropdowns(WorkbookPart workbookPart,DataGridViewColumnCollection columns, string sheetName)
        {
            // Get the target worksheet
            var sheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == sheetName);
            if (sheet == null)
            {
                throw new ArgumentException($"Sheet with name '{sheetName}' not found.");
            }

            var worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);

            // Create validation sheet only once if it doesn't exist
            WorksheetPart validationWorksheetPart = CreateValidationSheetIfNotExists(workbookPart, sheetName);


           
            for (var i = 0; i < columns.Count; i++)
            {
                var currentColumn = columns[i];
                if (currentColumn.GetType() != typeof(DataGridViewComboBoxColumn)) { continue; }

                var comboColumn = (DataGridViewComboBoxColumn)currentColumn;
                var dataColNum = i + 1 + additionalPrefixColumns; // Adjust this based on your needs
                var dataColumn = GetExcelColumnName(dataColNum);
                var dataStart = dataColumn + firstDataRow; // Assuming firstDataRow is 3
                var rowCount = worksheetPart.Worksheet.GetFirstChild<SheetData>().Elements<Row>().Count();
                var dataEnd = dataColumn + rowCount;
                var validationName = "validation_" + currentColumn.Name;

                // Populate validation options
                var optionsDt = new VC_ExportToExcelDropdownOptions(worksheetPart, dataStart, dataEnd,
                    validationWorksheetPart, dataColumn, comboColumn.Items, comboColumn.ValueMember, validationName);

                object[,] dataArray = ExtractPropertyPerItem(optionsDt.Options, optionsDt.OptionKey);

                // Define the named range only once
                DefineNamedRange(workbookPart, worksheetPart, validationName, sheetName, dataStart, dataEnd);

                // Add the dropdown options using Open XML
                AddDataValidation(workbookPart, worksheetPart, dataStart, dataEnd, dataArray, validationName, validationWorksheetPart);
            }
        }

        // This method ensures that the validation sheet is created once and reused.
        private static WorksheetPart CreateValidationSheetIfNotExists(WorkbookPart workbookPart, string validationName)
        {
            // Create or get the hidden validation sheet to store dropdown values
            const string validationSheetName = "ValidationSheet";

            var validationSheet = workbookPart.Workbook.Descendants<Sheet>()
                .FirstOrDefault(s => s.Name == validationSheetName);

            WorksheetPart hiddenSheetPart;
            if (validationSheet == null)
            {
                // Create new hidden sheet for validation
                hiddenSheetPart = workbookPart.AddNewPart<WorksheetPart>();
                hiddenSheetPart.Worksheet = new Worksheet(new SheetData());
                Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
                uint sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;

                sheets.Append(new Sheet
                {
                    Id = workbookPart.GetIdOfPart(hiddenSheetPart),
                    SheetId = sheetId,
                    Name = validationSheetName,
                    State = SheetStateValues.Hidden // Hide the sheet
                });

                workbookPart.Workbook.Save();
                Console.WriteLine($"Created new validation sheet: {validationSheetName}");
            }
            else
            {
                hiddenSheetPart = (WorksheetPart)workbookPart.GetPartById(validationSheet.Id);
                Console.WriteLine($"Using existing validation sheet: {validationSheetName}");
            }
            return hiddenSheetPart;
        }

        // AddDataValidation is responsible for adding the dropdown data validation
        private static void AddDataValidation(
            WorkbookPart workbookPart,
            WorksheetPart worksheetPart,
            string startCell,
            string endCell,
            object[,] options,
            string validationName,
            WorksheetPart hiddenSheetPart)
        {
            var worksheet = worksheetPart.Worksheet;
            var dataValidations = worksheet.Elements<DataValidations>().FirstOrDefault();

            // Create DataValidations if not exists
            if (dataValidations == null)
            {
                dataValidations = new DataValidations();
                worksheet.Append(dataValidations);
            }

            // Step 2: Add options to the hidden validation sheet
            //var hiddenSheetPart = CreateValidationSheetIfNotExists(workbookPart, validationName);  // Ensure validation sheet exists
            var sheetData = hiddenSheetPart.Worksheet.GetFirstChild<SheetData>();
            sheetData.RemoveAllChildren(); // Clean existing rows
            
            var optionList = new List<string>();
            for (int i = 0; i < options.GetLength(0); i++)
            {
                if (options[i, 0] != null)
                {
                    optionList.Add(options[i, 0].ToString());
                }
            }

            // Define the list of dropdown values
            //var dropDownOptions = options.Select(o => o.Value).ToList();
            //string formula = "\"" + string.Join(",", optionList) + "\"";


            //// Populate the validation sheet
            for (int i = 0; i < options.GetLength(0); i++)
            {
                if (options[i, 0] != null)
                {
                    var row = new Row();
                    row.Append(new Cell { CellValue = new CellValue(options[i, 0].ToString()), DataType = CellValues.String });
                    sheetData.Append(row);
                }
            }
            //hiddenSheetPart.Worksheet.Save();
            var range = $"{startCell}:{endCell}";
            // Create data validation referencing the named range
            var dataValidation = new DataValidation
            {
                Type = DataValidationValues.List,
                AllowBlank = true,
                ShowInputMessage = true,
                ShowErrorMessage = true,
                SequenceOfReferences = new ListValue<StringValue> { InnerText = $"{startCell}:{endCell}" },
                Formula1 = new Formula1($"={validationName}"), // Reference the named range
                //Formula1 = new Formula1(formula),
                ErrorTitle = "Invalid Option",
                Error = "Please select a value from the dropdown"
            };

            dataValidations.Append(dataValidation);
            dataValidations.Count = (uint)dataValidations.Count();
            //worksheet.Append(dataValidations);

            // Save the worksheet after making changes
            worksheet.Save();
        }


        private static void DefineNamedRange(
            WorkbookPart workbookPart,
            WorksheetPart worksheetPart,
            string rangeName,
            string sheetName,
            string startCell,
            string endCell)
        {
            var definedNames = workbookPart.Workbook.GetFirstChild<DefinedNames>();
            if (definedNames == null)
            {
                definedNames = new DefinedNames();
                workbookPart.Workbook.Append(definedNames);
            }

            var definedName = new DefinedName
            {
                Name = rangeName,
                Text = $"{sheetName}!{startCell}:{endCell}"
            };
            definedNames.Append(definedName);
            workbookPart.Workbook.Save();
        }

        private static bool NamedRangeExists(WorkbookPart workbookPart, string rangeName)
        {
            return workbookPart.Workbook.GetFirstChild<DefinedNames>()
                   ?.Elements<DefinedName>()
                   .Any(dn => dn.Name == rangeName) ?? false;
        }



        public static object[,] ExtractPropertyPerItem(IList optionList, string propertyName)
        {
            int validationEndCellNum = optionList.Count;
            var dataArray = new object[validationEndCellNum, 1];
            const int column = 0;
            for (int i = 0; i < validationEndCellNum; i++)
            {
                object currentObj = optionList[i];
                Type type = currentObj.GetType();
                if (type == typeof(string))
                {
                    dataArray[i, column] = currentObj;
                    continue;
                }
                PropertyInfo propertyInfo = type.GetProperty(propertyName);
                if (propertyInfo.PropertyType == typeof(String) || propertyInfo.PropertyType == typeof(int))
                {
                    dataArray[i, column] = propertyInfo.GetValue(currentObj, null);
                }
            }
            return dataArray;
        }