OpenXml DataValidation set predefined List for columns
I am using OpenXml to create Excel file and export table data. One of the scenario is I want a column to have dropdown of predefined values, say like true and false. I followed this question and wrote code as below
DataValidation dataValidation = new DataValidation
{
Type = DataValidationValues.List,
AllowBlank = true,
SequenceOfReferences = new ListValue<StringValue>() { InnerText = "B1" },
//Formula1 = new Formula1("'SheetName'!$A$1:$A$3") // this was used in mentioned question
Formula1 = new Formula1("True,False") // I need predefined values.
};
DataValidations dvs = worksheet.GetFirstChild<DataValidations>(); //worksheet type => Worksheet
if (dvs != null)
{
dvs.Count = dvs.Count + 1;
dvs.Append(dataValidation);
}
else
{
DataValidations newDVs = new DataValidations();
newDVs.Append(dataValidation);
newDVs.Count = 1;
worksheet.Append(newDVs);
}
If I use it with SheetName with cell values range, it works fine, but if I add string, it throws me error "Unreadable content found" and removes datavalidation node.
How to add values for list dropdown validation in formula itself. XML it creates for manually added(by editing in excel application) list values is <formula1>"One,Two"</formula1> (observed xml for excel file)
Okay I got this solved. Added escaped double quotes to formula and done.
DataValidation dataValidation = new DataValidation
{
Type = DataValidationValues.List,
AllowBlank = true,
SequenceOfReferences = new ListValue<StringValue>() { InnerText = "B1" },
Formula1 = new Formula1(""True,False"") // escape double quotes, this is what I was missing
};
DataValidations dvs = worksheet.GetFirstChild<DataValidations>(); //worksheet type => Worksheet
if (dvs != null)
{
dvs.Count = dvs.Count + 1;
dvs.Append(dataValidation);
}
else
{
DataValidations newDVs = new DataValidations();
newDVs.Append(dataValidation);
newDVs.Count = 1;
worksheet.Append(newDVs);
}