How to set validation for drop down in Excel using c# ClosedXML using method List(String list, Boolean inCellDropdown)

Hi All I am trying to create a dropdown list for a column in my excel using ClosedXML library. I am able to create it successfully using the below code.

      using (XLWorkbook wb = new XLWorkbook())
      {
        wb.Worksheets.Add(dt);
        wb.Worksheets.Add(dt2);
        var worksheet2 = wb.Worksheet(2);
        //wb.Worksheet(1).Column(11).SetDataValidation().List("one,two,three", true); This does not work fine
        wb.Worksheet(1).Column(11).SetDataValidation().List(worksheet2.Range("A2:A12"), true);// Works fine
        wb.Worksheet(1).Column(11).SetDataValidation().IgnoreBlanks = true;
        wb.Worksheet(1).Column(11).SetDataValidation().InCellDropdown = true;
        wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        wb.Style.Font.Bold = true;
        wb.SaveAs(targetFile);
      }

But I want to do the same thing with another overloaded method of List in SetDataValidation() but that is creating the excel but when I am trying to open it, it says its corrupted. Could you please help me understand why the other overloaded method is not working.

The method in question is public void List(String list, Boolean inCellDropdown). According to the Data Validation wiki page using a string list is possible :

//Pass a string in this format: "Option1,Option2,Option3"
var options = new List<string>{"Option1","Option2","Option3"};
var validOptions = $""{String.Join(",", options)}"";
ws.Cell(1,1).DataValidation.List(validOptions, true);

Answer

According to the Data Validation wiki page, the list list must include the double quotes.

In the doc example :

var options = new List<string>{"Option1","Option2","Option3"};
var validOptions = $""{String.Join(",", options)}"";
ws.Cell(1,1).DataValidation.List(validOptions, true);

validOptions contains :

"Option1,Option2,Option3"`

Your own code should change to :

.List(""one,two,three"", true);

Leave a Reply

Your email address will not be published. Required fields are marked *