Add Dropdown Validation to an entire Excel Column using OpenXML

Question

In Excel, I am able to add validation rules to a range of cells and limit the accepted input to a list of values displayed in a dropdown. This is done using the Data Validation tool, as pictured below:

I have some C# code that generates an excel sheet, and I would like to add this same kind of validation to one of the columns.

Using Microsoft.Office.Interop.Excel, I am able to add this kind of dropdown validation to an entire column:

string flatList = "FirstChoice,SecondChoice,ThirdChoice";

//select the entire first row as the range
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A1").EntireColumn;

//remove any previously existing validation           
range.Validation.Delete();               

//add new validation
range.Validation.Add(
    Microsoft.Office.Interop.Excel.XlDVType.xlValidateList,
    Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertInformation,
    Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlBetween,
    flatList,
    Type.Missing);

range.Validation.IgnoreBlank = true;
range.Validation.InCellDropdown = true;

The problem is, I can't guarantee that my user has Microsoft Office installed. I therefore want to instead use DocumentFormat.OpenXML.

Is it possible to add this same type of dropdown validation using OpenXML?

I've seen some posts using DataValidation, yet have not been able to figure how to get that to work, and if that will solve my problem.

Answer 1

With a bit more digging, I was able to figure out how to use DataValidation to add dropdown validation to entire column in my Excel sheet using DocumentFormat.OpenXml:

string flatList = "FirstChoice,SecondChoice,ThirdChoice";

DataValidation dataValidation = new DataValidation
{
    Type = DataValidationValues.List,
    AllowBlank = true,

    //Use A:A or A1:A1048576 to select the entire column A
    //1048576 (2^20) is the max row number since Excel 2007.
    SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A:A" },

    //Set the formula to the list of dropdown values. Escape the double quotes.
    Formula1 = new Formula1("\"" + flatList + "\"")
};

//Check if there are any other DataValidations already in the worksheet
DataValidations dvs = worksheet.GetFirstChild<DataValidations>();
if (dvs != null)
{
    //If you already have existing validation for column A, you may need to Remove()
    //or Replace() the current validation to get the new validation to show.          

    //Add the new DataValidation to the list of DataValidations
    dvs.Count = dvs.Count + 1;
    dvs.Append(dataValidation);
}
else
{
    DataValidations newDVs = new DataValidations();
    newDVs.Append(dataValidation);
    newDVs.Count = 1;

    //Append the validation to the DocumentFormat.OpenXml.SpreadSheet.Worksheet variable
    worksheet.Append(newDVs);
}