apache-poi
NPOI: Metod för validering av datavalidering för Date, Time, List Item, e-post etc. för XSSF (.xslx) excel-fil med c #

Introduktion

Ställ in datumbegränsningar för datumfältvärden mellan 01/01/1900 till 31/31/2119 med datumformat mm / dd // åååå;

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

Ställ in tidsbegränsningar för fältvärden mellan 00:00 till 23:59 med datumformat HH: MM;

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

Skapa ett listobjekt Begränsning

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

Telefonnummer begränsning

// prova samma sätt för valutatyper med format som "$ #, ###. 00" och datum "m / d / åååå: 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);

E-postadressbegränsning för e-postkolumner

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

// få e-postadressens valideringsmönster privatsträng 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)))";

}