apache-poi
NPOI: Gegevensvalidatiebenadering voor het excell-bestand van XSSF (.xslx) met c #

Invoering

Wanneer Som van alle tekens van het lijstitem minder dan 256 telt

U kunt alle items uit elk configuratiebestand lezen of inline typen.

Overweegt of het is opgeslagen 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);

Wanneer Som van het totale karakter van alle lijstitems meer dan 256 telt.

De aanpak is in dit geval anders dan in het vorige voorbeeld, omdat het Excel-bestand de gegevensvalidatie ondersteunt voor de lijst met items met een totaal aantal tekens van minder dan 256 als alle items direct zijn verbonden zoals in het vorige voorbeeld. Maar in veel situaties kunnen lijstitems langer zijn dan 256 tekens en in dat geval werkt directe binding niet.

Excel-bestand ondersteunt echter meer dan 256 tekens van een lijstitem als het wordt doorverwezen vanuit een andere kolom van hetzelfde excel-bestand. Als tijdelijke oplossing kunnen dus eenmaal alle waarden uit de database of het instellingenbestand lezen en deze voor de huidige weergave verborgen houden in een van de verre kolommen en gegevensvalidatie deze verborgen kolom door de formule lezen om een lijstitem te maken. Onderstaande code toont deze aanpak door gegevenswaarden te lezen via het instellingenbestand.

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