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

Copy validation

Jul 20, 2016 at 5:26 AM
Hi. I copy cells
 ws.Cells[rowIdxStart, 1, rowIdxEnd, colCount].Copy(ws.Cells[rowIdx, 1]);
How to copy the data validation of the "base" cells to the copied cells?
Jul 20, 2016 at 2:07 PM
Hi,

unfortunately datavalidations are not copied in this function. You have to recreate them for the new cells.

Datavalidations are always on worksheetlevel, so you have to find out if there are any datavalidation colliding with your cellrange and then you have to decide how you want to handle collisions of types exact match(Equal), Partly and Inside the range your copying.

We could probably make this easier to copy in future releases, but for the moment the only thing you can do is to copy the data validations "the hard way". In this example I only copy datavalidations if the range of the datavalidations are exactly the same as the range you are copying. You could easily change this behaviour in the DataValidations.FindAll call.
var validations = sheet1.DataValidations.FindAll(
    x => x.Address.Collide(new ExcelAddressBase("A1:C3")) == ExcelAddressBase.eAddressCollition.Equal);
foreach (var validation in validations)
{
    switch (validation.ValidationType.Type)
    {
        case eDataValidationType.List:
            var sourceListValidation = (IExcelDataValidationList) validation;
            var newListValidation = sheet1.DataValidations.AddListValidation("D1:F3");
            // copy field for field
            newListValidation.ErrorStyle = sourceListValidation.ErrorStyle;
            newListValidation.Error = sourceListValidation.Error;
            // and so on...
            break;
    }
}