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