1

I am using EPPlus with C# to create an excel file.

I want to put a data validation on a column to not except string longer than certain characters in its cells. Find the attached picture to better understand, what I mean.

Screen shot of the validation dialogue box

I can't find how to give the limitation. If someone has the solution or link to their valid documentaton. Please post it.

Uwe Keim
  • 39,551
  • 56
  • 175
  • 291
Babar
  • 1,202
  • 1
  • 12
  • 21
  • 2
    Why not ask the author on Codeplex where the project is hosted ? – PhillipH Dec 23 '16 at 13:52
  • 1
    EPPlus can't do something that Excel itself doesn't provide. There are no such restrictions in Excel. Why do you want *EPPlus* to validate the data instead of eg, your own code? What are you trying to do and why do you expect the Excel export library (that's what EPPlus is) to perform validations? – Panagiotis Kanavos Dec 23 '16 at 14:01
  • You don't mean to set cell-width, right? http://stackoverflow.com/questions/9096176/how-to-set-xlsx-cell-width-with-epplus-in-c-sharp – rene Dec 23 '16 at 14:02
  • 1
    Excel has the particular data validation, I want to use in my original question. – Babar Dec 23 '16 at 14:02
  • 1
    @rene no, I mean to check that the user input in a cell doesnt exceed a certain amount of characters when the file is generated. I just want to know how the validation can be added. – Babar Dec 23 '16 at 14:05

1 Answers1

2

This works for me:

var minLength = 1;
var maxLength = 4;
var textValidation = worksheet
    .DataValidations.AddTextLengthValidation("D:D");
textValidation.ShowErrorMessage = true;
textValidation.ErrorStyle = ExcelDataValidationWarningStyle.warning;
textValidation.ErrorTitle = "The value you entered is not valid";
textValidation.Error = string.Format(
    "This cell must be between {0} and {1} characters in length.",
    minLength, maxLength
);
textValidation.Formula.Value = minLength;
textValidation.Formula2.Value = maxLength;
kuujinbo
  • 9,272
  • 3
  • 44
  • 57