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()));
}
}
/// <summary>
///
/// </summary>
/// <param name="collection"></param>
/// <param name="address"></param>
/// <param name="maxLength"></param>
/// <param name="errorStyle"></param>
/// <param name="allowBlank"></param>
/// <param name="showErrorMessage"></param>
/// <param name="error"></param>
/// <returns></returns>
public static IExcelDataValidationInt AddTextMaxLengthValidation(this ExcelDataValidationCollection collection,
String address, Int32 maxLength, ExcelDataValidationWarningStyle errorStyle = ExcelDataValidationWarningStyle.stop,
Boolean allowBlank = true, Boolean showErrorMessage = true,
String error = null)
{
IExcelDataValidationInt excelDataValidation = collection.AddTextLengthValidation(address);
excelDataValidation.Operator = ExcelDataValidationOperator.lessThanOrEqual;
excelDataValidation.Formula.Value = maxLength;
excelDataValidation.AllowBlank = allowBlank;
excelDataValidation.ShowErrorMessage = showErrorMessage;
excelDataValidation.Error = error ?? String.Format("数值长度必须小于{0}.", excelDataValidation.Formula.Value);
return(excelDataValidation);
}
/// <summary>
/// 添加范围整数的数据验证
/// </summary>
/// <param name="collection">数据验证集合</param>
/// <param name="address">有效作用域，如：A:A，A2:A65535</param>
/// <param name="minValue">最小值</param>
/// <param name="maxValue">最大值</param>
/// <param name="errorStyle">验证失败提示样式</param>
/// <param name="allowBlank">数据是否允许为空</param>
/// <param name="showErrorMessage">是否显示验证失败的提示消息</param>
/// <param name="error">验证失败提示消息</param>
/// <returns>返回范围整数的数据验证对象</returns>
public static IExcelDataValidationInt AddIntegerRangeValidation(this ExcelDataValidationCollection collection,
String address, Int32 minValue, Int32 maxValue, ExcelDataValidationWarningStyle errorStyle = ExcelDataValidationWarningStyle.stop,
Boolean allowBlank = true, Boolean showErrorMessage = true,
String error = null)
{
IExcelDataValidationInt intValidation = collection.AddIntegerValidation(address);
intValidation.Operator = ExcelDataValidationOperator.between;
intValidation.Formula.Value = minValue;
intValidation.Formula2.Value = maxValue;
intValidation.AllowBlank = allowBlank;
intValidation.ShowErrorMessage = showErrorMessage;
intValidation.Error = error ?? String.Format("数值范围[{0},{1}]", intValidation.Formula.Value, intValidation.Formula2.Value);
return(intValidation);
}
private static void PrintWholeValidationDetails(ExcelWorksheet sheet, IExcelDataValidationInt wholeValidation, int row)
{
sheet.Cells["D" + row.ToString()].Value = wholeValidation.Formula.Value.HasValue ? wholeValidation.Formula.Value.Value.ToString() : wholeValidation.Formula.ExcelFormula;
sheet.Cells["E" + row.ToString()].Value = wholeValidation.Formula2.Value.HasValue ? wholeValidation.Formula2.Value.Value.ToString() : wholeValidation.Formula2.ExcelFormula;
}
private static void PrintWholeValidationDetails(ExcelWorksheet sheet, IExcelDataValidationInt wholeValidation, int row)
{
sheet.Cells["D" + row.ToString()].Value = wholeValidation.Formula.Value.HasValue ? wholeValidation.Formula.Value.Value.ToString() : wholeValidation.Formula.ExcelFormula;
sheet.Cells["E" + row.ToString()].Value = wholeValidation.Formula2.Value.HasValue ? wholeValidation.Formula2.Value.Value.ToString() : wholeValidation.Formula2.ExcelFormula;
}
