apache-poi
NPOI: approccio dei vincoli di convalida dei dati per data, ora, elemento della lista, e-mail ecc. Per XSSF (.xslx) file excel usando c #

introduzione

Impostare i vincoli di data per i valori del campo data tra 01/01/1900 e 31/12/2119 con formato data mm / gg // aaaaa;

  int DVRowLimit = (Int16.MaxValue); 
        CellRangeAddressList cellRangeFieldsType1 = new CellRangeAddressList(1, DVRowLimit, targetFirstCol, targetLastCol);
        XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)validationHelper.CreateDateConstraint(OperatorType.BETWEEN, "=DATE(1900,1,1)", "=DATE(2119,12,31)", "mm/dd/yyyyy");
    //dvConstraint = (XSSFDataValidationConstraint)validationHelper.CreateDateConstraint(OperatorType.IGNORED, "", "", "m/d/yy h:mm");
        XSSFDataValidation dataValidation = (XSSFDataValidation)validationHelper.CreateValidation(dvConstraint, cellRangeFieldsType1);
        dataValidation.ShowErrorBox = true;
        dataValidation.ErrorStyle = 0;
        dataValidation.CreateErrorBox("InvalidDate", "Allowed Format is MM/DD/YYYY");
        dataValidation.ShowErrorBox = true;
        dataValidation.CreatePromptBox("Date Data Validation", "Enter Date in Format MM/DD/YYYY.");
        dataValidation.ShowPromptBox = true;
        sheet.AddValidationData(dataValidation);

Impostare i vincoli temporali per i valori dei campi tra 00:00 e 23:59 con il formato data HH: MM;

  dvConstraint = (XSSFDataValidationConstraint)validationHelper.CreateTimeConstraint(OperatorType.BETWEEN, "=TIME(0,0,0)", "=TIME(23,59,59)");

Crea un elemento di elenco Vincolo

 dvConstraint = (XSSFDataValidationConstraint)validationHelper.CreateExplicitListConstraint(new string[] { "MON", "TUE" , "WED", "THU", "FRI"});

Numero di telefono

// prova lo stesso approccio per i tipi di valuta con formato come "$ #, ###. 00" e data "m / g / aa h: mm"

XSSFFont defaultFont = (XSSFFont)workbook.CreateFont();
defaultFont.FontHeightInPoints = (short)10;
defaultFont.FontName = "Arial";
XSSFCellStyle phoneCellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat phoneDataFormat = (XSSFDataFormat)workbook.CreateDataFormat();
phoneCellStyle.SetDataFormat(phoneDataFormat.GetFormat("000-000-0000"));
phoneCellStyle.FillBackgroundColor = IndexedColors.LightYellow.Index;
dvConstraint = (XSSFDataValidationConstraint)validationHelper.CreateintConstraint(OperatorType.BETWEEN, "1000000000", "9999999999");
sheet.AddValidationData(dataValidation);
sheet.SetDefaultColumnStyle(headerCount, phoneCellStyle);

Vincolo dell'indirizzo email per colonne email

 string emailValidationFormula = GetEmailValidationFormula(targetFirstCol);
 CellRangeAddressList cellRangeFieldsType5 = new CellRangeAddressList(1, DVRowLimit, targetFirstCol, targetLastCol);
     dvConstraint = (XSSFDataValidationConstraint)validationHelper.CreateCustomConstraint(emailValidationFormula)

// ottiene il modello di convalida dell'indirizzo di posta elettronica stringa privata 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)))";

}