如何在c＃中使用OpenXml在excel文件的列/列中设置数据验证列表？ [英] How to set a List for data validations in column/columns of excel file using OpenXml in c#?

问题描述

我需要创建一个下拉列表，使用特定的列表作为源在使用openXml的excel文件的列/列中。

I need to create a dropdown using a particular list as source in column/columns of an excel file using openXml.

我使用以下代码为目的，

I use the following code for the purpose,

     SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open("C:\\Users\\Harun.TV\\Desktop\\OpenXml\\1.xlsx",true);

        WorkbookPart workbookpart = spreadSheetDocument.WorkbookPart;
        Workbook workbook=workbookpart.Workbook;


        WorksheetPart worksheetPart=workbookpart.WorksheetParts.First();            


        DataValidations dataValidations1 = new DataValidations();
        DataValidation dataValidation2 = new DataValidation() { Formula1 = new Formula1("'mySheet'!$A$1:$A$4"), Type = DataValidationValues.List, AllowBlank = true, ShowInputMessage = true, ShowErrorMessage = true, SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A4:B4" } };
        Formula1 formula12 = new Formula1();
        formula12.Text = "$A$1:$A$3";
        dataValidations1.Append(dataValidation2);
        worksheetPart.Worksheet.Append(dataValidations1);

         workbookpart.Workbook.Save();


        spreadSheetDocument.Close();

并且在打开excel时抛出一个错误。日志如下，

And it throws throws an error while opening the excel. the log is as follows,

      <?xml version="1.0" encoding="UTF-8" standalone="true"?>
     -<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">     <logFileName>error055840_01.xml</logFileName>
      <summary>Errors were detected in file  'C:\Users\Harun.TV\Desktop\OpenXml\6.xlsx'</summary>
      -<removedParts summary="Following is a  list of removed parts:">     
       <removedPart>Replaced Part: /xl/worksheets/sheet3.xml part with XML error. Load error. Line 1, column 467.</removedPart></removedParts></recoveryLog>

此外，如何将逗号分隔值的列表分配给DataValidations并获得所需的结果它手动为excel列？

Also how can i assign a list of comma separated values to DataValidations and attain the desired result as we do it manually for an excel column?

推荐答案

问题可能是已经有一个DataValidatoins节点。
这适用于我：

The problem may be that there is already a "DataValidatoins" node. This works for me:

        DataValidation dataValidation = new DataValidation
        {
            Type = DataValidationValues.List,
            AllowBlank = true,
            SequenceOfReferences = new ListValue<StringValue>() { InnerText = "B1" },
            Formula1 = new Formula1("'SheetName'!$A$1:$A$3")
        };

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

这篇关于如何在c＃中使用OpenXml在excel文件的列/列中设置数据验证列表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！