apache-poi NPOI: Data validation approach for XSSF(.xslx) excel file using c# When Sum of all list item's total character count less than 256


Example

You can read all items either from any config file or type it inline.

Considering if its saved in Config File

// Read all list items from config file
string[] countryDV = ConfigurationManager.AppSettings["countryDV"].Split(',').Select(s => s.Trim().ToUpper()).ToArray();
int DVRowLimit = (Int16.MaxValue); 
CellRangeAddressList countryDVAddList = new CellRangeAddressList(1, DVRowLimit, 0, 0);
dvConstraint = (XSSFDataValidationConstraint)validationHelper.CreateExplicitListConstraint(countryDV);
// In case of Inline list values 
// use this approach:  dvConstraint = (XSSFDataValidationConstraint)validationHelper.CreateExplicitListConstraint(new string[] { "USA", "CANADA"});
dataValidation = (XSSFDataValidation)validationHelper.CreateValidation(dvConstraint, countryDVAddList);
dataValidation.ShowErrorBox = true;
dataValidation.SuppressDropDownArrow = true;
dataValidation.ErrorStyle = 0;
dataValidation.CreateErrorBox("InvalidValue", "Select Valid country.");
dataValidation.ShowErrorBox = true;
dataValidation.CreatePromptBox("country Data Validation", "Enter country.");
dataValidation.ShowPromptBox = true;
sheet.AddValidationData(dataValidation);