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