将列表验证添加到列，除了前两行 [英] Add List Validation to Column except the first two rows

问题描述

我试图在创建excel时添加一个下拉列表（列表验证），我已经找到了将它添加到整列的方法，但我的方案是不同的，因为我只添加验证到整列，除了第一行和第二

I am trying to add a dropdown (list validation) upon creating the excel, I already found the way to add it to whole column but my scenario is different because I only add the validation to whole column except the first row and second row.

这是我到目前为止所尝试的：

This is what I have tried so far:

 public MemoryStream GetExcelSheet()
    {
        using (var package = new ExcelPackage())
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Test");

            var val = worksheet.DataValidations.AddListValidation("A:A");

            val.Formula.Values.Add("Male");
            val.Formula.Values.Add("Female");
            val.ShowErrorMessage = true;

            worksheet.Cells["A1"].Formula = null;


            var stream = new MemoryStream(package.GetAsByteArray());
            return stream;
        }
    }

我要取消它：

worksheet.Cells["A1"].Formula = null;

希望它会删除某个单元格的验证，但不起作用。

hoping that it will remove the validation on a certain cell but it's not working.

我也尝试了

var val = worksheet.DataValidations.AddListValidation("A2:A");

指定起始行号，但生成的Excel文件已损坏。

specifying the starting row number but the generated Excel file is corrupted.

有任何帮助吗？

推荐答案

AFAIK没有办法设置excel 第2行到无限不幸。所以像A2：A这样的东西不会工作。

AFAIK there is no way to set a range in excel of "Row 2 to Infinity" unfortunately. So something like "A2:A" will not work.

要演示，请尝试使用验证列表手动创建工作表并保存（与代码无关）。设置所有列A上的val列表，然后单击A1并从中删除列表。如果你把xlsx重命名为.zip，打开它，看看sheet1.xml文件你会看到：

To demo, try creating a worksheet in Excel manually with a validation list and save it (nothing to do with code). Set the val list on all of column A and then click A1 and remove the list from it only. If you then rename the xlsx to .zip, open it, and look at the sheet1.xml file you will see this:

<dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="A2:A1048576">
    <formula1>"Male,Female"</formula1>
</dataValidation>

注意 sqref 。基本上，excel将从A2到最大行数的范围设置为excel 2007格式的1,048,576。所以没有理由你不能这样做。

Note the sqref. Basically, excel sets the range from A2 to the max number of rows in the excel 2007 format of 1,048,576. So no reason you couldn't do the same.

这篇关于将列表验证添加到列，除了前两行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！