This project has moved. For the latest updates, please go here.

Validation of cell using fixed List of values

Apr 18, 2014 at 10:22 PM
In Excel I can do something like this:
Image

In ClosedXML I did not find a way. I tried:
workSheet.Cell(rowIndex, 1).DataValidation.InCellDropdown= true;
workSheet.Cell(rowIndex, 1).DataValidation.AllowedValues = XLAllowedValues.List;
workSheet.Cell(rowIndex, 1).DataValidation.IgnoreBlanks = true;
workSheet.Cell(rowIndex, 1).DataValidation.Value = "yes;no";
what am I missing?
Apr 28, 2014 at 4:49 PM
Check the documentation
https://closedxml.codeplex.com/wikipage?title=Data%20Validation
            ws.Cell("C1").Value = "Yes";
            ws.Cell("C2").Value = "No";
            ws.Cell("A5").DataValidation.List(ws.Range("C1:C2"));
Apr 28, 2014 at 5:58 PM
Thanks, but this is not what I want to achieve. I do not want to specify a range inside the sheet as valid values, but just a list of fixed strings. As you can see in the screenshot it is possible in Excel, so I expect this can be done in ClosedXML as well. Unfortunately the documentation does not show how.
Sep 1, 2014 at 2:51 PM
After experiencing a similar issue and after much unsuccessful Googling, with the aid of the Microsoft Open XML SDK managed to get to the bottom of this one and the syntax required where you do not want to use ranges:

workSheet.Cell(rowIndex, 1).DataValidation.Value = "\"yes,no\"";

i.e. Comma separated list but requires additional quotes surrounding it to avoid being treated as a formula.

Hope this helps others.

Marie
Oct 15, 2014 at 10:47 PM
Obviously it took some time to find a little bit of spare time for this pet project of me, but:
thank you Marie, it worked.