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

Set DataValidation for entire columns

May 1, 2012 at 11:56 AM

Hi!

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;
ValidationObject.TextLength.EqualTo(0);

... or whatever other validation I would want. 

Thanks! JC

Sep 6, 2012 at 8: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 5:01 PM

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

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