apache-poi
NPOI: подход к проверке данных для файла excel XSSF (.xslx) с использованием c #

Вступление

Когда сумма суммарного количества сумм всех элементов списка меньше 256

Вы можете прочитать все элементы либо из любого конфигурационного файла, либо ввести его в строку.

Учитывая, сохранено ли в файле конфигурации

// 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);

Сумма суммарного количества сумм всех элементов списка составляет более 256.

Подход в этом случае будет отличаться от предыдущего примера, потому что файл Excel поддерживает проверку данных для списка элементов с общим количеством символов менее 256, если все элементы привязаны напрямую, как в предыдущем примере. Но во многих ситуациях элементы списка могут быть длиннее 256 символов, и в этом случае прямая привязка не будет работать.

Однако файл excel поддерживает более 256 символов элемента списка, если он указан из разных столбцов одного и того же файла excel. Таким образом, как обходной путь один раз может считывать все значения из файла базы данных или настроек и сохранять его скрытым от текущего представления в один из удаленных столбцов, а проверка данных может читать этот скрытый столбец по формуле для создания элемента списка. Ниже код покажет этот подход, прочитав значения данных через файл настроек.

// Read all list items from config file
string[] countryDV = ConfigurationManager.AppSettings["countryDV"].Split(',').Select(s => s.Trim().ToUpper()).ToArray();
// Get the column name where you want to hide the list items, assume distant  column is "ZZ"
string countryDVDataCellColumn = ConfigurationManager.AppSettings["countryDVDataCellColumn"].Trim().ToString(); 

  
int DVRowLimit = (Int16.MaxValue); 
// Creating and Assigning Settings for Data Validation
CreateDropDownUsingCellReference(workbook, countryDV, "CountryConstraint", countryDVDataCellColumn);
CellRangeAddressList countryDVAddList = new CellRangeAddressList(1, DVRowLimit, targetFirstCol, targetLastCol);
dvConstraint = (XSSFDataValidationConstraint)validationHelper.CreateFormulaListConstraint("=CountryConstraint");
dvConstraint.Validate();
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);



private void CreateDropDownUsingCellReference(XSSFWorkbook wb, string[] csvListOfValues, string listName, string headerName)
{
    int columnIndex = CellReference.ConvertColStringToIndex(headerName);
    try
    {
    XSSFName namedCell = (XSSFName)wb.CreateName();
    namedCell.NameName = listName;
    //Creating Cell and Assigning Values from CSVListOfValues;
    for (int i = 0; i < csvListOfValues.Length; i++)
    {
            var namedRow = wb.GetSheetAt(0).CreateRow(i + 1);
            namedRow.CreateCell(columnIndex).SetCellValue(csvListOfValues[i]);
    }

    //Assigning the Reference for sheet 0 With Cell Range, where list items iscopied 
    String reference = wb.GetSheetAt(0).SheetName + "!$" + headerName + "$2:$" + headerName + "$" + (csvListOfValues.Length + 1).ToString();
    namedCell.RefersToFormula = reference;

    //Hiding the Column  now;
    wb.GetSheetAt(0).SetColumnHidden(columnIndex, true);
    }
    catch (Exception Ex)
    { }
}