private void AddValidationInternal(int operatorType, String firstFormula, String secondFormula, HSSFDataValidation.ERRORSTYLE errorStyle, String ruleDescr, String promptDescr, bool allowEmpty, bool inputBox, bool errorBox, bool suppressDropDown, String[] explicitListValues) { int rowNum = _currentRowIndex++; DVConstraint dc = CreateConstraint(operatorType, firstFormula, secondFormula, explicitListValues); HSSFDataValidation dv = new HSSFDataValidation(new CellRangeAddressList(rowNum, rowNum, 0, 0), dc); dv.EmptyCellAllowed = (allowEmpty); dv.ErrorStyle = (errorStyle); dv.CreateErrorBox("Invalid Input", "Something is wrong - Check condition!"); dv.CreatePromptBox("Validated Cell", "Allowable values have been restricted"); dv.ShowPromptBox = (inputBox); dv.ShowErrorBox = (errorBox); dv.SuppressDropDownArrow = (suppressDropDown); _sheet.AddValidationData(dv); WriteDataValidationSettings(_sheet, _style_1, _style_2, ruleDescr, allowEmpty, inputBox, errorBox); if (_cellStyle != null) { Row row = _sheet.GetRow(_sheet.PhysicalNumberOfRows - 1); Cell cell = row.CreateCell(0); cell.CellStyle = (_cellStyle); } WriteOtherSettings(_sheet, _style_1, promptDescr); }
/// <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); } } }
/// <summary> /// Sheet中引用校验引用区域 /// </summary> /// <param name="workbook"></param> /// <param name="sheet"></param> /// <param name="item"></param> private void SheetAddDataValidation(IWorkbook workbook, ISheet sheet, ColumnProperty item) { if (item == null || string.IsNullOrWhiteSpace(item.ConstraintReference)) //如果没有引用区域, 则退出 { return; } CellRangeAddressList regions = new CellRangeAddressList(2, 65535, item.ColumnIndex, item.ColumnIndex); IDataValidation dataValidate = null; if (excelVersion == ExcelVersion.XLSX) { XSSFSheet xssfSheet = sheet as XSSFSheet; XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(xssfSheet); XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint(item.ConstraintReference); dataValidate = dvHelper.CreateValidation(dvConstraint, regions); dataValidate.EmptyCellAllowed = true; dataValidate.ShowErrorBox = true; dataValidate.CreateErrorBox("系统提示", "请选择指定的" + item.ColumnHeader + "选项"); dataValidate.ShowPromptBox = true; dataValidate.CreatePromptBox("", item.ColumnHeader); } else { IName range = workbook.CreateName(); range.RefersToFormula = item.ConstraintReference; range.NameName = item.ConstraintName; DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(item.ConstraintName); dataValidate = new HSSFDataValidation(regions, constraint); dataValidate.EmptyCellAllowed = true; dataValidate.ShowErrorBox = true; dataValidate.CreateErrorBox("系统提示", "请选择指定的" + item.ColumnHeader + "选项"); dataValidate.ShowPromptBox = true; dataValidate.CreatePromptBox("", item.ColumnHeader); } sheet.AddValidationData(dataValidate); }
public void VisitRecord(Record r) { if (!(r is DVRecord)) { return; } DVRecord dvRecord = (DVRecord)r; CellRangeAddressList regions = dvRecord.CellRangeAddress.Copy(); DVConstraint constraint = DVConstraint.CreateDVConstraint(dvRecord, book); HSSFDataValidation hssfDataValidation = new HSSFDataValidation(regions, constraint); hssfDataValidation.ErrorStyle = (dvRecord.ErrorStyle); hssfDataValidation.EmptyCellAllowed = (dvRecord.EmptyCellAllowed); hssfDataValidation.SuppressDropDownArrow = (dvRecord.SuppressDropdownArrow); hssfDataValidation.CreatePromptBox(dvRecord.PromptTitle, dvRecord.PromptText); hssfDataValidation.ShowPromptBox = (dvRecord.ShowPromptOnCellSelected); hssfDataValidation.CreateErrorBox(dvRecord.ErrorTitle, dvRecord.ErrorText); hssfDataValidation.ShowErrorBox = (dvRecord.ShowErrorOnInvalidValue); hssfValidations.Add(hssfDataValidation); }
/// <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); }