CODEWITHSUNDEEP
c#.netexcelclosedxml
Gaurav Parek
Gaurav Parek

Reputation: 327

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

Upvotes: 2

Views: 8179

Answers (2)

user4911404
user4911404

Reputation:

Because official document has few examples, so I post some detail examples here to show how to add dropdown list into excel.

        //set data validation, dropdown list
        // if we want list abcd in a cell, we should add ""
        var validateList = "\"A,B,C,D\"";

        //1.use column index
        worksheet.Column(3).SetDataValidation().List(validateList, true);

         //2.use column name
        worksheet.Column("D").SetDataValidation().List(validateList, true);

        //3.use column range
        worksheet.Range("E1:E20").SetDataValidation().List(validateList, true);

closedxml/wiki/Data-Validation

Upvotes: 3

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131483

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

Upvotes: 6

Related Questions