Using OpenXML, how can I associate a list for data

Question

I am processing an .xlsm file and need to know how to use a list on another sheet for data validation using openXML and C#.

To start, I have a .xlsm file with two empty sheets and macros in it. In my program I open the file, Create the column header on Sheet1 then create the validation list on sheet2. So, after I run my program Sheet1 "A1" contains the text "Color" and Sheet2 "A1:A4" contains "Blue","Green","Red","Yellow". I get this far just fine.

I would like to make it so there is a dropdown list in all cells of column "A" on sheet1 that contains each of the 4 colors and enforces them as the only input. In Microsoft Excel this is done by going to the "Data" tab, selecting "Data Validation" selecting "List" and highlighting the cells you want to use. I need to make this association programmatically.

The (Desired) XML that Microsoft Excel creates if I do it manually is this:

<extLst>
    <ext uri="{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">

        <x14:dataValidations count="1" xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main">

            <x14:dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1">

                <x14:formula1>
                    <xm:f>'Validation Data'!$A$1:$A$4</xm:f>
                </x14:formula1>

                <xm:sqref>A1:A1048576</xm:sqref>

            </x14:dataValidation>

        </x14:dataValidations>

    </ext>
</extLst>

The following method and results is something I tried. It may give a better Idea of what I'm trying to do.

Here, I pass in "'Sheet2'!$A$1:$A$4" as the "validationListCells" parameter. This represents the cells in "Sheet2" that, in this example, would contain the color names "Red", "Green"...etc.

I pass in "A2:A1048576" as the "cellsToValidate" parameter. This represents all cells of Sheet1 column "A", on which I want to enforce validation.

I pass "Sheet1" as the worksheetName parameter.

private void InsertValidation(String worksheetName, String validationListCells, String cellsToValidate)
{

    DataValidations dataValidations1 = new DataValidations() { Count = (UInt32Value)1U };
    DataValidation dataValidation1 = new DataValidation() 
    { 

        Formula1 = new Formula1(validationListCells),
        Type = DataValidationValues.List, 
        ShowInputMessage = true, 
        ShowErrorMessage = true, 
        SequenceOfReferences = new ListValue<StringValue>()  { InnerText = cellsToValidate } 
    };

    dataValidations1.Append(dataValidation1);
    using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(_documentPath, true))
    {
        WorksheetPart worksheetPart = GetWorksheetPartByName(spreadSheet, worksheetName);
        worksheetPart.Worksheet.Append(dataValidations1);
        worksheetPart.Worksheet.Save();
    }
}

It results in this XML in Sheet1.xml. Which causes an error in Excel.

<x:dataValidations count="1">

    <x:dataValidation type="list" showInputMessage="1" showErrorMessage="1" sqref="A2: A1048576">
        <x:formula1>'Sheet2'!$A$1:$A$5</x:formula1>
    </x:dataValidation>

</x:dataValidations>

It looks like I may be on the right track since it is beginning to resemble the xml created by Excel, but I'm completely new to openXML and I'm finding little about this topic on the net.

Thanks in advance!

Answer 1

For anyone else in need of this..the code below worked for me. I put in there user3251089's variable names.

In general, when I try to programmatically create an excel "feature" I manually make a really basic excel that has in it that feature (delete extra sheets too). Then I reflect the code and try to make it prettier.

hope it serves to someone!

using Excel = DocumentFormat.OpenXml.Office.Excel;
using X14 = DocumentFormat.OpenXml.Office2010.Excel;

.....

Worksheet worksheet = worksheetPart.Worksheet;
WorksheetExtensionList worksheetExtensionList = new WorksheetExtensionList();
WorksheetExtension worksheetExtension = new WorksheetExtension() { Uri = "{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}" };
worksheetExtension.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");

X14.DataValidations dataValidations = new X14.DataValidations() { Count = (UInt32Value)3U };
dataValidations.AddNamespaceDeclaration("xm", "http://schemas.microsoft.com/office/excel/2006/main");

//sites validation
dataValidations.Append(new X14.DataValidation()
{
    Type = DataValidationValues.List,
    AllowBlank = true,
    ShowInputMessage = true,
    ShowErrorMessage = true,
    DataValidationForumla1 = new X14.DataValidationForumla1() { Formula = new Excel.Formula(validationListCells) },
    ReferenceSequence = new Excel.ReferenceSequence(cellsToValidate)
});

worksheetExtension.Append(dataValidations);
worksheetExtensionList.Append(worksheetExtension);
worksheet.Append(worksheetExtensionList);
worksheet.Save();