apache-poi
NPOI: Podejście do sprawdzania poprawności danych dla pliku Excel XSSF (.xslx) przy użyciu c #

Wprowadzenie

Gdy suma wszystkich znaków wszystkich elementów listy jest mniejsza niż 256

Możesz odczytać wszystkie elementy z dowolnego pliku konfiguracyjnego lub wpisać go bezpośrednio.

Rozważanie, czy jest zapisany w pliku konfiguracyjnym

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

Gdy suma wszystkich znaków elementu listy liczy ponad 256.

Podejście w tym przypadku będzie inne niż w poprzednim przykładzie, ponieważ plik Excel obsługuje sprawdzanie poprawności danych dla listy elementów o całkowitej liczbie znaków mniejszej niż 256, jeśli wszystkie elementy są powiązane bezpośrednio, jak w poprzednim przykładzie. Ale w wielu sytuacjach elementy listy mogą być dłuższe niż 256 znaków, w takim przypadku bezpośrednie wiązanie nie będzie działać.

Jednak plik programu Excel obsługuje więcej niż 256 znaków elementu listy, jeśli jest on odwoływany z innej kolumny tego samego pliku programu Excel. Aby obejść ten problem, można odczytać wszystkie wartości z bazy danych lub pliku ustawień i ukryć go przed bieżącym widokiem w jednej z odległych kolumn, a sprawdzanie poprawności danych może odczytać tę ukrytą kolumnę za pomocą formuły, aby utworzyć element listy. Poniższy kod pokaże to podejście poprzez odczyt wartości danych poprzez plik ustawień.

// 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)
    { }
}