使用OpenXML，如何关联列表进行数据验证 [英] Using OpenXML, how can I associate a list for data validation

问题描述

我正在处理一个.xlsm文件，需要知道如何在另一张表上使用列表进行数据验证，并使用openXML和C＃。

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#.

要开始，我有一个.xlsm文件，其中有两个空白的表格和宏。在我的程序中，我打开文件，在Sheet1上创建列标题，然后在sheet2上创建验证列表。所以，在我运行我的程序Sheet1A1包含文本颜色和Sheet2A1：A4包含蓝色，绿色，红色，黄色。我得到这个很好。

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.

我想要这样做，所以在sheet1上的列A的所有单元格中都有一个下拉列表，其中包含4个颜色并强制它们作为唯一的输入。在Microsoft Excel中，通过转到数据选项卡，选择数据验证选择列表并突出显示要使用的单元格。我需要以编程方式使这个关联。

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.

如果我手动执行，Microsoft Excel创建的（Desired）XML是这样的：

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.

这里，我传入'Sheet2'！$ A $ 1：$ A $ 4作为validationListCells参数。这表示Sheet2中的单元格，在此示例中，将包含颜色名称红色，绿色等。

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.

我传入A2：A1048576作为cellsToValidate参数。这表示Sheet1列A的所有单元格，我要强制验证。

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

我将Sheet1作为工作表名称参数传递。

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();
    }
}

它导致Sheet1.xml中的这个XML。这导致Excel中的错误。

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>

看起来我可能在正确的轨道上，因为它开始类似于Excel创建的xml ，但我完全是openXML新手，我在网上找不到这个话题。

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.

提前感谢！

推荐答案

对于任何需要这个的代码，下面的代码为我工作。
我放在那里user3251089的变量名称。

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

一般来说，当我尝试以编程方式创建一个excel功能时，我手动使一个真正的基本excel在它的功能（也删除额外的表）。然后我反映代码，并尝试使其变得更漂亮。

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();

这篇关于使用OpenXML，如何关联列表进行数据验证的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！