Open xml with data validation for Named Range
What I have tried:
public static void AddDropdowns(WorkbookPart workbookPart,DataGridViewColumnCollection columns, string sheetName)
{
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);
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;
var dataColumn = GetExcelColumnName(dataColNum);
var dataStart = dataColumn + firstDataRow;
var rowCount = worksheetPart.Worksheet.GetFirstChild<SheetData>().Elements<Row>().Count();
var dataEnd = dataColumn + rowCount;
var validationName = "validation_" + currentColumn.Name;
var optionsDt = new VC_ExportToExcelDropdownOptions(worksheetPart, dataStart, dataEnd,
validationWorksheetPart, dataColumn, comboColumn.Items, comboColumn.ValueMember, validationName);
object[,] dataArray = ExtractPropertyPerItem(optionsDt.Options, optionsDt.OptionKey);
DefineNamedRange(workbookPart, worksheetPart, validationName, sheetName, dataStart, dataEnd);
AddDataValidation(workbookPart, worksheetPart, dataStart, dataEnd, dataArray, validationName, validationWorksheetPart);
}
}
private static WorksheetPart CreateValidationSheetIfNotExists(WorkbookPart workbookPart, string validationName)
{
const string validationSheetName = "ValidationSheet";
var validationSheet = workbookPart.Workbook.Descendants<Sheet>()
.FirstOrDefault(s => s.Name == validationSheetName);
WorksheetPart hiddenSheetPart;
if (validationSheet == null)
{
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
});
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;
}
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();
if (dataValidations == null)
{
dataValidations = new DataValidations();
worksheet.Append(dataValidations);
}
var sheetData = hiddenSheetPart.Worksheet.GetFirstChild<SheetData>();
sheetData.RemoveAllChildren();
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());
}
}
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);
}
}
var range = $"{startCell}:{endCell}";
var dataValidation = new DataValidation
{
Type = DataValidationValues.List,
AllowBlank = true,
ShowInputMessage = true,
ShowErrorMessage = true,
SequenceOfReferences = new ListValue<StringValue> { InnerText = $"{startCell}:{endCell}" },
Formula1 = new Formula1($"={validationName}"),
ErrorTitle = "Invalid Option",
Error = "Please select a value from the dropdown"
};
dataValidations.Append(dataValidation);
dataValidations.Count = (uint)dataValidations.Count();
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;
}