public void DataValidation_IntAndDateTime() { using (var package = new ExcelPackage()) { var sheet = package.Workbook.Worksheets.Add("intsAndSuch"); // Integer validation IExcelDataValidationInt intValidation = sheet.DataValidations.AddIntegerValidation("A1"); intValidation.Prompt = "Value between 1 and 5"; intValidation.Operator = ExcelDataValidationOperator.between; intValidation.Formula.Value = 1; intValidation.Formula2.Value = 5; // DateTime validation IExcelDataValidationDateTime dateTimeValidation = sheet.DataValidations.AddDateTimeValidation("A2"); dateTimeValidation.Prompt = "A date greater than today"; dateTimeValidation.Operator = ExcelDataValidationOperator.greaterThan; dateTimeValidation.Formula.Value = DateTime.Now.Date; // Time validation IExcelDataValidationTime timeValidation = sheet.DataValidations.AddTimeValidation("A3"); timeValidation.Operator = ExcelDataValidationOperator.greaterThan; var time = timeValidation.Formula.Value; time.Hour = 13; time.Minute = 30; time.Second = 10; // Existing validations var validations = package.Workbook.Worksheets.SelectMany(sheet1 => sheet1.DataValidations); package.SaveAs(new FileInfo(BinDir.GetPath())); } }
private static void PrintTimeValidationDetails(ExcelWorksheet sheet, IExcelDataValidationTime validation, int row) { var value1 = string.Empty; if (!string.IsNullOrEmpty(validation.Formula.ExcelFormula)) { value1 = validation.Formula.ExcelFormula; } else { value1 = string.Format("{0}:{1}:{2}", validation.Formula.Value.Hour, validation.Formula.Value.Minute, validation.Formula.Value.Second ?? 0); } sheet.Cells["D" + row.ToString()].Value = value1; }