This project has moved and is read-only. For the latest updates, please go here.

Set DataValidation for entire columns

May 1, 2012 at 12:56 PM


I want to set a DataValidation for some columns in my sheet. I have tried to go for the second example from the documentation wiki, but ranges don't seem to have a DataValidation property anymore.

I have tried to work around this by getting all cells in the row (from the first one to the 2^10th one, or whatever the limit is), but that runs rather slow, naturally, since it has to "touch" every cell in that range.

Ideally I'd like the following to work:

IXLDataValidation ValidationObject = CurCell.WorksheetColumn().DataValidation;

... or whatever other validation I would want. 

Thanks! JC

Sep 6, 2012 at 9:53 PM

I second this.  I am generating a spreadsheet and I want to set list validation for whole columns at a time (or whole blocks of cells).  I am having to save my ClosedXML document, reopen it using OpenXML, and setting it that way.  Big pain!

Sep 7, 2012 at 6:01 PM

Got it.  You can use the SetDataValidation() method on a range.  Observe:

IXLDataValidation validation = column.AsRange().SetDataValidation();
validation.IgnoreBlanks = true;