apache-poi NPOI: Data validation constraint approach for Date, Time , List Item , email etc. for XSSF(.xslx) excel file using c# Email Address constraint For Email Columns
Example
string emailValidationFormula = GetEmailValidationFormula(targetFirstCol);
CellRangeAddressList cellRangeFieldsType5 = new CellRangeAddressList(1, DVRowLimit, targetFirstCol, targetLastCol);
dvConstraint = (XSSFDataValidationConstraint)validationHelper.CreateCustomConstraint(emailValidationFormula)
// get the email address validation pattern private string GetEmailValidationFormula(int targetColumn) {
int div = headerCount + 1;
string colLetter = String.Empty;
int mod = 0;
try
{
while (div > 0)
{
mod = (div - 1) % 26;
colLetter = (char)(65 + mod) + colLetter;
div = (int)((div - mod) / 26);
}
}
catch (Exception Ex)
{
logger.Error("Error ", Ex);
}
return "=AND(FIND(\"@\"," + colLetter + "2),FIND(\".\"," + colLetter + "2),ISERROR(FIND(\" \"," + colLetter + "2)))";
}
