private void AddCustomValidations(WorkbookFormatter wf) { wf.CreateSheet("Custom"); wf.CreateHeaderRow(); ValidationAdder va = wf.CreateValidationAdder(null, ValidationType.FORMULA); va.AddValidation(OperatorType.BETWEEN, "ISNUMBER($A2)", null, ERRORSTYLE.STOP, "ISNUMBER(A2)", "Error box type = STOP", true, true, true); va.AddValidation(OperatorType.BETWEEN, "IF(SUM(A2:A3)=5,TRUE,FALSE)", null, ERRORSTYLE.WARNING, "IF(SUM(A2:A3)=5,TRUE,FALSE)", "Error box type = WARNING", false, false, true); }
private static void AddTextLengthValidations(WorkbookFormatter wf) { wf.CreateSheet("Text lengths"); wf.CreateHeaderRow(); ValidationAdder va = wf.CreateValidationAdder(null, ValidationType.TEXT_LENGTH); va.AddValidation(OperatorType.BETWEEN, "2", "6", ERRORSTYLE.STOP, "Between 2 and 6 ", "Error box type = STOP", true, true, true); va.AddValidation(OperatorType.NOT_BETWEEN, "2", "6", ERRORSTYLE.INFO, "Not between 2 and 6 ", "Error box type = INFO", false, true, true); va.AddValidation(OperatorType.EQUAL, "3", null, ERRORSTYLE.WARNING, "Equal to 3", "Error box type = WARNING", false, false, true); va.AddValidation(OperatorType.NOT_EQUAL, "3", null, ERRORSTYLE.WARNING, "Not equal to 3", "-", false, false, false); va.AddValidation(OperatorType.GREATER_THAN, "3", null, ERRORSTYLE.WARNING, "Greater than 3", "-", true, false, false); va.AddValidation(OperatorType.LESS_THAN, "3", null, ERRORSTYLE.WARNING, "Less than 3", "-", true, true, false); va.AddValidation(OperatorType.GREATER_OR_EQUAL, "4", null, ERRORSTYLE.STOP, "Greater than or equal to 4", "Error box type = STOP", true, false, true); va.AddValidation(OperatorType.LESS_OR_EQUAL, "4", null, ERRORSTYLE.STOP, "Less than or equal to 4", "-", false, true, false); }
private static void AddDateTimeValidations(WorkbookFormatter wf, IWorkbook wb) { wf.CreateSheet("Dates and Times"); IDataFormat dataFormat = wb.CreateDataFormat(); short fmtDate = dataFormat.GetFormat("m/d/yyyy"); short fmtTime = dataFormat.GetFormat("h:mm"); ICellStyle cellStyle_date = wb.CreateCellStyle(); cellStyle_date.DataFormat = (/*setter*/ fmtDate); ICellStyle cellStyle_time = wb.CreateCellStyle(); cellStyle_time.DataFormat = (/*setter*/ fmtTime); wf.CreateDVTypeRow("Date ( cells are already formated as date - m/d/yyyy)"); wf.CreateHeaderRow(); ValidationAdder va = wf.CreateValidationAdder(cellStyle_date, ValidationType.DATE); va.AddValidation(OperatorType.BETWEEN, "2004/01/02", "2004/01/06", ERRORSTYLE.STOP, "Between 1/2/2004 and 1/6/2004 ", "Error box type = STOP", true, true, true); va.AddValidation(OperatorType.NOT_BETWEEN, "2004/01/01", "2004/01/06", ERRORSTYLE.INFO, "Not between 1/2/2004 and 1/6/2004 ", "Error box type = INFO", false, true, true); va.AddValidation(OperatorType.EQUAL, "2004/03/02", null, ERRORSTYLE.WARNING, "Equal to 3/2/2004", "Error box type = WARNING", false, false, true); va.AddValidation(OperatorType.NOT_EQUAL, "2004/03/02", null, ERRORSTYLE.WARNING, "Not equal to 3/2/2004", "-", false, false, false); va.AddValidation(OperatorType.GREATER_THAN, "=DATEVALUE(\"4-Jul-2001\")", null, ERRORSTYLE.WARNING, "Greater than DATEVALUE('4-Jul-2001')", "-", true, false, false); va.AddValidation(OperatorType.LESS_THAN, "2004/03/02", null, ERRORSTYLE.WARNING, "Less than 3/2/2004", "-", true, true, false); va.AddValidation(OperatorType.GREATER_OR_EQUAL, "2004/03/02", null, ERRORSTYLE.STOP, "Greater than or equal to 3/2/2004", "Error box type = STOP", true, false, true); va.AddValidation(OperatorType.LESS_OR_EQUAL, "2004/03/04", null, ERRORSTYLE.STOP, "Less than or equal to 3/4/2004", "-", false, true, false); // "Time" validation type wf.CreateDVTypeRow("Time ( cells are already formated as time - h:mm)"); wf.CreateHeaderRow(); va = wf.CreateValidationAdder(cellStyle_time, ValidationType.TIME); va.AddValidation(OperatorType.BETWEEN, "12:00", "16:00", ERRORSTYLE.STOP, "Between 12:00 and 16:00 ", "Error box type = STOP", true, true, true); va.AddValidation(OperatorType.NOT_BETWEEN, "12:00", "16:00", ERRORSTYLE.INFO, "Not between 12:00 and 16:00 ", "Error box type = INFO", false, true, true); va.AddValidation(OperatorType.EQUAL, "13:35", null, ERRORSTYLE.WARNING, "Equal to 13:35", "Error box type = WARNING", false, false, true); va.AddValidation(OperatorType.NOT_EQUAL, "13:35", null, ERRORSTYLE.WARNING, "Not equal to 13:35", "-", false, false, false); va.AddValidation(OperatorType.GREATER_THAN, "12:00", null, ERRORSTYLE.WARNING, "Greater than 12:00", "-", true, false, false); va.AddValidation(OperatorType.LESS_THAN, "=1/2", null, ERRORSTYLE.WARNING, "Less than (1/2) -> 12:00", "-", true, true, false); va.AddValidation(OperatorType.GREATER_OR_EQUAL, "14:00", null, ERRORSTYLE.STOP, "Greater than or equal to 14:00", "Error box type = STOP", true, false, true); va.AddValidation(OperatorType.LESS_OR_EQUAL, "14:00", null, ERRORSTYLE.STOP, "Less than or equal to 14:00", "-", false, true, false); }
private static void AddSimpleNumericValidations(WorkbookFormatter wf) { // data validation's number types wf.CreateSheet("Numbers"); // "Whole number" validation type wf.CreateDVTypeRow("Whole number"); wf.CreateHeaderRow(); ValidationAdder va = wf.CreateValidationAdder(null, ValidationType.INTEGER); va.AddValidation(OperatorType.BETWEEN, "2", "6", ERRORSTYLE.STOP, "Between 2 and 6 ", "Error box type = STOP", true, true, true); va.AddValidation(OperatorType.NOT_BETWEEN, "2", "6", ERRORSTYLE.INFO, "Not between 2 and 6 ", "Error box type = INFO", false, true, true); va.AddValidation(OperatorType.EQUAL, "=3+2", null, ERRORSTYLE.WARNING, "Equal to (3+2)", "Error box type = WARNING", false, false, true); va.AddValidation(OperatorType.NOT_EQUAL, "3", null, ERRORSTYLE.WARNING, "Not equal to 3", "-", false, false, false); va.AddValidation(OperatorType.GREATER_THAN, "3", null, ERRORSTYLE.WARNING, "Greater than 3", "-", true, false, false); va.AddValidation(OperatorType.LESS_THAN, "3", null, ERRORSTYLE.WARNING, "Less than 3", "-", true, true, false); va.AddValidation(OperatorType.GREATER_OR_EQUAL, "4", null, ERRORSTYLE.STOP, "Greater than or equal to 4", "Error box type = STOP", true, false, true); va.AddValidation(OperatorType.LESS_OR_EQUAL, "4", null, ERRORSTYLE.STOP, "Less than or equal to 4", "-", false, true, false); // "Decimal" validation type wf.CreateDVTypeRow("Decimal"); wf.CreateHeaderRow(); va = wf.CreateValidationAdder(null, ValidationType.DECIMAL); va.AddValidation(OperatorType.BETWEEN, "2", "6", ERRORSTYLE.STOP, "Between 2 and 6 ", "Error box type = STOP", true, true, true); va.AddValidation(OperatorType.NOT_BETWEEN, "2", "6", ERRORSTYLE.INFO, "Not between 2 and 6 ", "Error box type = INFO", false, true, true); va.AddValidation(OperatorType.EQUAL, "3", null, ERRORSTYLE.WARNING, "Equal to 3", "Error box type = WARNING", false, false, true); va.AddValidation(OperatorType.NOT_EQUAL, "3", null, ERRORSTYLE.WARNING, "Not equal to 3", "-", false, false, false); va.AddValidation(OperatorType.GREATER_THAN, "=12/6", null, ERRORSTYLE.WARNING, "Greater than (12/6)", "-", true, false, false); va.AddValidation(OperatorType.LESS_THAN, "3", null, ERRORSTYLE.WARNING, "Less than 3", "-", true, true, false); va.AddValidation(OperatorType.GREATER_OR_EQUAL, "4", null, ERRORSTYLE.STOP, "Greater than or equal to 4", "Error box type = STOP", true, false, true); va.AddValidation(OperatorType.LESS_OR_EQUAL, "4", null, ERRORSTYLE.STOP, "Less than or equal to 4", "-", false, true, false); }