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