private DVConstraint CreateConstraint(int operatorType, String firstFormula,
String secondFormula, String[] explicitListValues)
{
if (_validationType == DVConstraint.ValidationType.LIST)
{
if (explicitListValues != null)
{
return(DVConstraint.CreateExplicitListConstraint(explicitListValues));
}
return(DVConstraint.CreateFormulaListConstraint(firstFormula));
}
if (_validationType == DVConstraint.ValidationType.TIME)
{
return(DVConstraint.CreateTimeConstraint(operatorType, firstFormula, secondFormula));
}
if (_validationType == DVConstraint.ValidationType.DATE)
{
return(DVConstraint.CreateDateConstraint(operatorType, firstFormula, secondFormula, null));
}
if (_validationType == DVConstraint.ValidationType.FORMULA)
{
return(DVConstraint.CreateCustomFormulaConstraint(firstFormula));
}
return(DVConstraint.CreateNumericConstraint(_validationType, operatorType, firstFormula, secondFormula));
}
/// <summary>
/// 验证与提示
/// </summary>
/// <param name="validItem"></param>
/// <param name="colIndex"></param>
/// <param name="sheet"></param>
private void ValidAndMessage(ColumnValidItem validItem, int colIndex, ISheet sheet)
{
DVConstraint constraint = null;
HSSFDataValidation dataValidate = null;
switch (validItem.ValidType)//验证类型
{
case EnumValidType.Integer:
constraint = DVConstraint.CreateNumericConstraint(ValidationType.INTEGER, (int)validItem.ValidQualifier, validItem.ValidMin, validItem.ValidMax);
break;
case EnumValidType.Decimal:
constraint = DVConstraint.CreateNumericConstraint(ValidationType.DECIMAL, (int)validItem.ValidQualifier, validItem.ValidMin, validItem.ValidMax);
break;
case EnumValidType.DateTime:
constraint = DVConstraint.CreateDateConstraint((int)validItem.ValidQualifier, validItem.ValidMin, validItem.ValidMax, "yyyy-MM-dd");
break;
case EnumValidType.TextLength:
constraint = DVConstraint.CreateNumericConstraint(ValidationType.TEXT_LENGTH, (int)validItem.ValidQualifier, validItem.ValidMin ?? "1", validItem.ValidMax ?? "200");
break;
case EnumValidType.Customize:
constraint = DVConstraint.CreateCustomFormulaConstraint(string.Format(validItem.ValidFormula, Chr(colIndex)));
break;
}
if (constraint != null)
{
CellRangeAddressList regions = null;
if (validItem.ValidType == EnumValidType.Customize && validItem.ValidFormula == "COUNTIF({0}:{0},\"*@*.*\")=1") //邮件格式验证
{
int rowCount = 2000; //只支持前2000行邮件格式验证
for (int i = 0; i < rowCount; i++)
{
constraint = DVConstraint.CreateCustomFormulaConstraint(string.Format("COUNTIF({0}{1},\"*@*.*\")=1", Chr(colIndex), i + 2));
regions = new CellRangeAddressList(i + 1, i + 1, colIndex, colIndex);
dataValidate = new HSSFDataValidation(regions, constraint);
dataValidate.CreatePromptBox(validItem.Name, validItem.InputMessage);
dataValidate.CreateErrorBox(validItem.Name, validItem.ErrorMessage);
((HSSFSheet)sheet).AddValidationData(dataValidate);
}
}
else
{
regions = new CellRangeAddressList(1, 65535, colIndex, colIndex);
dataValidate = new HSSFDataValidation(regions, constraint);
dataValidate.CreatePromptBox(validItem.Name, validItem.InputMessage);
dataValidate.CreateErrorBox(validItem.Name, validItem.ErrorMessage);
((HSSFSheet)sheet).AddValidationData(dataValidate);
}
}
}
public static HSSFDataValidation CreateDateConstraint(this HSSFWorkbook book, int columnIndex)
{
ISheet sheet1 = book.GetSheetAt(0);
CellRangeAddressList cellRegions = new CellRangeAddressList(1, 65535, columnIndex, columnIndex);
DVConstraint constraint = DVConstraint.CreateDateConstraint(OperatorType.BETWEEN, "1900-01-01", "2999-12-31", "yyyy-MM-dd");
HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
dataValidate.CreateErrorBox("error", "You must input a date.");
sheet1.AddValidationData(dataValidate);
return(new HSSFDataValidation(cellRegions, constraint));
}
/// <summary>
/// 设置单元格为日期
/// </summary>
/// <param name="sheet"></param>
/// <param name="index"></param>
public static void SetCellDate(this ISheet sheet, int index)
{
//设置生成下拉框的行和列
var cellRegions = new CellRangeAddressList(1, 65535, index, index);
//设置 下拉框内容
DVConstraint constraint = DVConstraint.CreateDateConstraint(OperatorType.BETWEEN, "1900-01-01", "2999-12-31", "yyyy-MM-dd");
//绑定下拉框和作用区域，并设置错误提示信息
HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
dataValidate.CreateErrorBox("输入不合法", "请输入正确的时间格式");
dataValidate.ShowPromptBox = true;
sheet.AddValidationData(dataValidate);
}
//数据有效性以及下拉框的设置
public static HSSFDataValidation CreateDataValidation(int index, Tk5FieldInfoEx fieldInfo,
ICellStyle styleContent, HSSFWorkbook workbook)
{
IDataFormat format = workbook.CreateDataFormat();
CellRangeAddressList region = new CellRangeAddressList(1, 65535, index, index);
DVConstraint constraint = null;
HSSFDataValidation dataValidation = null;
if (fieldInfo.Decoder != null && fieldInfo.Decoder.Type == DecoderType.CodeTable)
{
IEnumerable <IDecoderItem> data = GetDecoderItem(fieldInfo);
if (data != null)
{
List <string> optionList = new List <string>();
foreach (IDecoderItem item in data)
{
if (item != null)
{
TkDebug.AssertArgumentNullOrEmpty(item.Name, "item.Name", null);
optionList.Add(item.Name);
}
}
constraint = DVConstraint.CreateExplicitListConstraint(optionList.ToArray());
dataValidation = DisplayMsg(region, constraint, "请从下拉框选项中选择");
}
}
else
{
if (fieldInfo.InternalControl != null && fieldInfo.InternalControl.SrcControl == ControlType.CheckBox)
{
constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "√" });
dataValidation = DisplayMsg(region, constraint, "请在下拉框选项中进行选择");
}
else
{
switch (fieldInfo.DataType)
{
case TkDataType.DateTime:
case TkDataType.Date:
if (fieldInfo.DataType == TkDataType.DateTime)
{
styleContent.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm");
}
else
{
styleContent.DataFormat = format.GetFormat("yyyy-MM-dd");
}
constraint = DVConstraint.CreateDateConstraint(6, "1900-01-01", null, "yyyy-MM-dd");
dataValidation = DisplayMsg(region, constraint, "请输入一个日期类型的值");
break;
case TkDataType.Double:
case TkDataType.Decimal:
case TkDataType.Money:
constraint = DVConstraint.CreateNumericConstraint(2, 1, "1", "0");
dataValidation = DisplayMsg(region, constraint, "请输入数值类型的值");
styleContent.DataFormat = format.GetFormat("0");
break;
case TkDataType.Long:
case TkDataType.Int:
case TkDataType.Short:
case TkDataType.Byte:
case TkDataType.Bit:
constraint = DVConstraint.CreateNumericConstraint(1, 1, "1", "0");
dataValidation = DisplayMsg(region, constraint, "请输入一个整数");
styleContent.DataFormat = format.GetFormat("0");
break;
default:
styleContent.DataFormat = format.GetFormat("@");
break;
}
}
}
return(dataValidation);
}
/// <summary>
/// 设置Excel单元格样式（标题），数据格式
/// </summary>
/// <param name="dateType">数据类型</param>
/// <param name="porpetyIndex">单元格索引</param>
/// <param name="sheet">Sheet页</param>
/// <param name="dataSheet">数据Sheet页</param>
/// <param name="dataStyle">样式</param>
/// <param name="dataFormat">格式</param>
public void SetColumnFormat(ColumnDataType dateType, int porpetyIndex, HSSFSheet sheet, HSSFSheet dataSheet, ICellStyle dataStyle, IDataFormat dataFormat)
{
HSSFDataValidation dataValidation = null;
switch (dateType)
{
case ColumnDataType.Date:
this.MinValueOrLength = DateTime.Parse("1950/01/01").ToString("yyyy/MM/dd");
this.MaxValuseOrLength = string.IsNullOrEmpty(this.MaxValuseOrLength) ? DateTime.MaxValue.ToString("yyyy/MM/dd") : this.MaxValuseOrLength;
dataValidation = new HSSFDataValidation(new CellRangeAddressList(1, 65535, porpetyIndex, porpetyIndex),
DVConstraint.CreateDateConstraint(OperatorType.BETWEEN, this.MinValueOrLength, this.MaxValuseOrLength, "yyyy/MM/dd"));
dataValidation.CreateErrorBox("错误", "请输入日期");
dataValidation.CreatePromptBox("请输入日期格式 yyyy/mm/dd", "在" + MinValueOrLength + " 到 " + MaxValuseOrLength + "之间");
//dataStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("yyyy/MM/dd");
dataStyle.DataFormat = dataFormat.GetFormat("yyyy/mm/dd");
break;
case ColumnDataType.Number:
this.MinValueOrLength = string.IsNullOrEmpty(this.MinValueOrLength) ? long.MinValue.ToString() : this.MinValueOrLength;
this.MaxValuseOrLength = string.IsNullOrEmpty(this.MaxValuseOrLength) ? long.MaxValue.ToString() : this.MaxValuseOrLength;
dataValidation = new HSSFDataValidation(new CellRangeAddressList(1, 65535, porpetyIndex, porpetyIndex),
DVConstraint.CreateNumericConstraint(ValidationType.INTEGER, OperatorType.BETWEEN, this.MinValueOrLength, this.MaxValuseOrLength));
dataValidation.CreateErrorBox("错误", "请输入数字");
dataStyle.DataFormat = dataFormat.GetFormat("0");
dataValidation.CreatePromptBox("请输入数字格式", "在" + MinValueOrLength + " 到 " + MaxValuseOrLength + "之间");
break;
case ColumnDataType.Float:
this.MinValueOrLength = string.IsNullOrEmpty(this.MinValueOrLength) ? decimal.MinValue.ToString() : this.MinValueOrLength;
this.MaxValuseOrLength = string.IsNullOrEmpty(this.MaxValuseOrLength) ? decimal.MaxValue.ToString() : this.MaxValuseOrLength;
dataValidation = new HSSFDataValidation(new CellRangeAddressList(1, 65535, porpetyIndex, porpetyIndex),
DVConstraint.CreateNumericConstraint(ValidationType.DECIMAL, OperatorType.BETWEEN, this.MinValueOrLength, this.MaxValuseOrLength));
dataValidation.CreateErrorBox("错误", "请输入小数");
dataStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
dataValidation.CreatePromptBox("请输入小数", "在" + MinValueOrLength + " 到 " + MaxValuseOrLength + "之间");
break;
case ColumnDataType.Bool:
dataValidation = new HSSFDataValidation(new CellRangeAddressList(1, 65535, porpetyIndex, porpetyIndex),
DVConstraint.CreateFormulaListConstraint("Sheet1!$A$1:$B$1"));
dataValidation.CreateErrorBox("错误", "请输入下拉菜单中存在的数据");
sheet.AddValidationData(dataValidation);
dataValidation.CreatePromptBox("下拉菜单", "请输入下拉菜单中存在的数据");
break;
case ColumnDataType.Text:
this.MinValueOrLength = string.IsNullOrEmpty(this.MinValueOrLength) ? "0" : this.MinValueOrLength;
this.MaxValuseOrLength = string.IsNullOrEmpty(this.MaxValuseOrLength) ? "2000" : this.MaxValuseOrLength;
dataValidation = new HSSFDataValidation(new CellRangeAddressList(1, 65535, porpetyIndex, porpetyIndex),
DVConstraint.CreateNumericConstraint(ValidationType.TEXT_LENGTH, OperatorType.BETWEEN, MinValueOrLength, MaxValuseOrLength));
dataValidation.CreateErrorBox("错误", "文本长度不符合要求");
dataStyle.DataFormat = dataFormat.GetFormat("@");
dataValidation.CreatePromptBox("请输入文本", "在" + MinValueOrLength + " 到 " + MaxValuseOrLength + "之间");
break;
case ColumnDataType.ComboBox:
case ColumnDataType.Enum:
int count = this.ListItems.Count() == 0 ? 1 : this.ListItems.Count();
string cloIndex = "";
if (porpetyIndex > 25)
{
cloIndex += Convert.ToChar((int)(Math.Floor(porpetyIndex / 26d)) - 1 + 65);
}
cloIndex += Convert.ToChar(65 + porpetyIndex % 26).ToString();
//定义名称
IName range = sheet.Workbook.CreateName();
range.RefersToFormula = "Sheet2!$" + cloIndex + "$1:$" + cloIndex + "$" + count;
range.NameName = "dicRange" + porpetyIndex;
//dataValidation = new HSSFDataValidation(new CellRangeAddressList(1, 65535, porpetyIndex, porpetyIndex),
// DVConstraint.CreateFormulaListConstraint("Sheet2!$" + cloIndex + "$1:$" + cloIndex + "$" + count));
dataValidation = new HSSFDataValidation(new CellRangeAddressList(1, 65535, porpetyIndex, porpetyIndex),
DVConstraint.CreateFormulaListConstraint("dicRange" + porpetyIndex));
dataValidation.CreateErrorBox("错误", "请输入下拉菜单中存在的数据");
var listItemsTemp = this.ListItems.ToList();
for (int rowIndex = 0; rowIndex < this.ListItems.Count(); rowIndex++)
{
//HSSFRow dataSheetRow = (HSSFRow)dataSheet.CreateRow(rowIndex);
HSSFRow dataSheetRow = (HSSFRow)dataSheet.GetRow(rowIndex);
if (dataSheetRow == null)
{
dataSheetRow = (HSSFRow)dataSheet.CreateRow(rowIndex);
}
//dataSheetRow.CreateCell(porpetyIndex).SetCellValue(this.ListItems.ToList()[rowIndex].Text);
dataSheetRow.CreateCell(porpetyIndex).SetCellValue(listItemsTemp[rowIndex].Text);
dataStyle.DataFormat = dataFormat.GetFormat("@");
dataSheetRow.Cells.Where(x => x.ColumnIndex == porpetyIndex).FirstOrDefault().CellStyle = dataStyle;
}
sheet.AddValidationData(dataValidation);
dataValidation.CreatePromptBox("下拉菜单", "请输入下拉菜单中存在的数据");
break;
default:
dataValidation = new HSSFDataValidation(new CellRangeAddressList(1, 65535, porpetyIndex, porpetyIndex),
DVConstraint.CreateNumericConstraint(ValidationType.TEXT_LENGTH, OperatorType.BETWEEN, this.MinValueOrLength, this.MaxValuseOrLength));
dataValidation.CreateErrorBox("错误", "文本长度不符合要求");
dataStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
break;
}
if (!this.IsNullAble)
{
dataValidation.EmptyCellAllowed = false;
}
sheet.SetDefaultColumnStyle(porpetyIndex, dataStyle);
sheet.AddValidationData(dataValidation);
}
