/// <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);
}
public static IExcelDataValidationCustom AddUniqueValidation(this ExcelDataValidationCollection collection,
String address, ExcelDataValidationWarningStyle errorStyle = ExcelDataValidationWarningStyle.stop,
Boolean allowBlank = true, Boolean showErrorMessage = true,
String error = null)
{
String dataRangeColumn = address.Substring(0, 1);
Int32 indexOfSplit = address.IndexOf(":");
String startRange = address.Substring(0, indexOfSplit == -1 ? address.Length : indexOfSplit);
var customValidation = collection.AddCustomValidation(address);
customValidation.Formula.ExcelFormula = String.Format("=COUNTIF({0}:{0},{1})=1", dataRangeColumn, startRange);
customValidation.AllowBlank = allowBlank;
customValidation.ShowErrorMessage = showErrorMessage;
customValidation.Error = "数据项重复";
return(customValidation);
}
/// <summary>
/// 添加ID数据验证
/// </summary>
/// <param name="collection">数据验证集合</param>
/// <param name="address">有效作用域，如：A:A，A2:A65535</param>
/// <param name="errorStyle">验证失败提示样式</param>
/// <param name="allowBlank">数据是否允许为空</param>
/// <param name="showErrorMessage">是否显示验证失败的提示消息</param>
/// <param name="error">验证失败提示消息</param>
/// <returns></returns>
public static IExcelDataValidationCustom AddIdentityValidation(this ExcelDataValidationCollection collection,
String address, ExcelDataValidationWarningStyle errorStyle = ExcelDataValidationWarningStyle.stop,
Boolean allowBlank = true, Boolean showErrorMessage = true,
String error = null)
{
String dataRangeColumn = address.Substring(0, 1);
Int32 indexOfSplit = address.IndexOf(":");
String startRange = address.Substring(0, indexOfSplit == -1 ? address.Length : indexOfSplit);
var customValidation = collection.AddCustomValidation(address);
customValidation.Formula.ExcelFormula = String.Format("=AND(COUNTIF({0}:{0},{1})=1,ISNUMBER(SUMPRODUCT(SEARCH(MID({1},ROW(INDIRECT(\"1:\"&LEN({1}))),1),\"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'-_\"))))", dataRangeColumn, startRange);
customValidation.AllowBlank = allowBlank;
customValidation.ShowErrorMessage = showErrorMessage;
customValidation.Error = "ID格式不正确或重复，格式必须是[a-z,0-9,A-Z,_-]的组合";
return(customValidation);
}
/// <summary>
/// 添加范围小数的数据验证
/// </summary>
/// <param name="collection"></param>
/// <param name="address"></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 IExcelDataValidationDecimal AddDoubleRangeValidation(this ExcelDataValidationCollection collection,
String address, Double minValue, Double maxValue,
ExcelDataValidationWarningStyle errorStyle = ExcelDataValidationWarningStyle.stop,
Boolean allowBlank = true, Boolean showErrorMessage = true,
String error = null)
{
IExcelDataValidationDecimal excelDataValidation = collection.AddDecimalValidation(address);
excelDataValidation.Operator = ExcelDataValidationOperator.between;
excelDataValidation.Formula.Value = minValue;
excelDataValidation.Formula2.Value = maxValue;
excelDataValidation.AllowBlank = allowBlank;
excelDataValidation.ShowErrorMessage = showErrorMessage;
excelDataValidation.Error = error ?? String.Format("数值范围[{0},{1}]", excelDataValidation.Formula.Value, excelDataValidation.Formula2.Value);
return(excelDataValidation);
}
/// <summary>
/// 添加列表数据验证
/// </summary>
/// <param name="collection">数据验证集合</param>
/// <param name="address">有效作用域，如：A:A，A2:A65535</param>
/// <param name="listData">列表数据集合</param>
/// <param name="errorStyle">验证失败提示样式</param>
/// <param name="allowBlank">数据是否允许为空</param>
/// <param name="showErrorMessage">是否显示验证失败的提示消息</param>
/// <param name="error">验证失败提示消息</param>
/// <returns>返回列表数据验证对象</returns>
public static IExcelDataValidationList AddListValidation(this ExcelDataValidationCollection collection,
String address, String [] listData, ExcelDataValidationWarningStyle errorStyle = ExcelDataValidationWarningStyle.stop,
Boolean allowBlank = true, Boolean showErrorMessage = true,
String error = null)
{
IExcelDataValidationList validationList = collection.AddListValidation(address);
foreach (var data in listData)
{
validationList.Formula.Values.Add(data);
}
validationList.AllowBlank = allowBlank;
validationList.ErrorStyle = errorStyle;
validationList.ShowErrorMessage = showErrorMessage;
validationList.Error = error ?? String.Format("只允许数据内容[{0}]", listData.ToSqlDataString());
return(validationList);
}
