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="sheet"></param> /// <param name="firstRow"></param> /// <param name="lastRow"></param> /// <param name="firstCol"></param> /// <param name="lastCol"></param> public static void SetCellInputNumber(ISheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) { var cellRegions = new CellRangeAddressList(firstRow, lastRow, firstCol, firstCol); DVConstraint constraint = DVConstraint.CreateNumericConstraint(ValidationType.DECIMAL, OperatorType.BETWEEN, "0", "999999999"); HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint); dataValidate.CreateErrorBox("", "经过语言"); sheet.AddValidationData(dataValidate); }
/// <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 CreateNumericConstraint(this HSSFWorkbook book, int columnIndex, string minvalue, string maxvalue, bool isInteger = false) { ISheet sheet1 = book.GetSheetAt(0); CellRangeAddressList cellRegions = new CellRangeAddressList(1, 65535, columnIndex, columnIndex); DVConstraint constraint = DVConstraint.CreateNumericConstraint(isInteger ? ValidationType.INTEGER : ValidationType.DECIMAL, OperatorType.BETWEEN, minvalue, maxvalue); HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint); dataValidate.CreateErrorBox("error", "You must input a numeric between " + minvalue + " and " + maxvalue + "."); sheet1.AddValidationData(dataValidate); return(new HSSFDataValidation(cellRegions, constraint)); }
/// <summary> /// 设置单元格只能输入数字 /// </summary> /// <param name="sheet"></param> public static void SetCellInputNumber(this ISheet sheet, int index, string min, string max, string msg) { //设置生成下拉框的行和列 var cellRegions = new CellRangeAddressList(1, 65535, index, index); //第二个参数int comparisonOperator 参考源码获取 //https://github.com/tonyqus/npoi //NPOITest项目 DVConstraint constraint = DVConstraint.CreateNumericConstraint( ValidationType.INTEGER, OperatorType.BETWEEN, min, max); HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint); dataValidate.CreateErrorBox("输入不合法", msg); //dataValidate.PromptBoxTitle = "ErrorInput"; sheet.AddValidationData(dataValidate); }
public void CreateDataValidation(string filePath, int sheetIndex, decimal fromNumber, decimal toNumber, int fromRow, int fromColumn, int toRow, int toColumn) { FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite); HSSFWorkbook hssfworkbook = new HSSFWorkbook(file); if (hssfworkbook != null) { CellRangeAddressList rangeList = new CellRangeAddressList(fromRow - 1, toRow - 1, fromColumn - 1, toColumn - 1); // Vì NPOI sử dụng row và column bắt đầu từ 0 DVConstraint dvconstraint = DVConstraint.CreateNumericConstraint(2, OperatorType.BETWEEN, fromNumber.ToString(), toNumber.ToString()); HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList, dvconstraint); HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheetIndex - 1); if (sheet != null) { sheet.AddValidationData(dataValidation); file = new FileStream(filePath, FileMode.Create); hssfworkbook.Write(file); file.Close(); } } }
/// <summary> /// Create data validation /// Hungnd 04/04/2013 /// </summary> /// <param name="sheetIndex"></param> /// <param name="fromRow"></param> /// <param name="fromCol"></param> /// <param name="toRow"></param> /// <param name="toCol"></param> /// <param name="validationType">The value in VTValidationType</param> /// <param name="fromValue"></param> /// <param name="toValue"></param> /// <param name="errorTitle">Default "Error value"</param> /// <param name="errorMessage">Default "Error input value"</param> /// <param name="errorStyle">Default ErrorStyle.STOP</param> /// <param name="lstContrains">Default null</param> public void CreateCellValidation(int sheetIndex, int fromRow, int fromColumn, int toRow, int toColumn, int validationType, string fromValue, string toValue, string[] lstContrains = null, string errorTitle = "Error value", string errorMessage = "Error input value", int errorStyle = ErrorStyle.STOP ) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(sfile); if (hssfworkbook != null) { CellRangeAddressList rangeList = new CellRangeAddressList(fromRow - 1, toRow - 1, fromColumn - 1, toColumn - 1); // Vì NPOI sử dụng row và column bắt đầu từ 0 DVConstraint dvconstraint = null; if (validationType.Equals(VTValidationType.LIST)) { dvconstraint = DVConstraint.CreateExplicitListConstraint(lstContrains); } else { dvconstraint = DVConstraint.CreateNumericConstraint(validationType, OperatorType.BETWEEN, fromValue, toValue); } HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList, dvconstraint); if (!validationType.Equals(VTValidationType.LIST)) { dataValidation.CreateErrorBox(errorTitle, errorMessage); dataValidation.ErrorStyle = errorStyle; } HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheetIndex); if (sheet != null) { sheet.AddValidationData(dataValidation); sfile = new FileStream(FilePath, FileMode.Create); hssfworkbook.Write(sfile); } } }
//数据有效性以及下拉框的设置 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); }
public void TestAddToExistingSheet() { // dvEmpty.xls is a simple one sheet workbook. With a DataValidations header record but no // DataValidations. It's important that the example has one SHEETPROTECTION record. // Such a workbook can be Created in Excel (2007) by Adding datavalidation for one cell // and then deleting the row that contains the cell. HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("dvEmpty.xls"); int dvRow = 0; HSSFSheet sheet = (HSSFSheet)wb.GetSheetAt(0); DVConstraint dc = DVConstraint.CreateNumericConstraint(DVConstraint.ValidationType.INTEGER, DVConstraint.OperatorType.EQUAL, "42", null); HSSFDataValidation dv = new HSSFDataValidation(new CellRangeAddressList(dvRow, dvRow, 0, 0), dc); dv.EmptyCellAllowed = (false); dv.ErrorStyle = (HSSFDataValidation.ERRORSTYLE.STOP); dv.ShowPromptBox = (true); dv.CreateErrorBox("Xxx", "Yyy"); dv.SuppressDropDownArrow = (true); sheet.AddValidationData(dv); MemoryStream baos = new MemoryStream(); try { wb.Write(baos); } catch (IOException) { throw; } byte[] wbData = baos.ToArray(); //if (false) //{ // TODO (Jul 2008) fix EventRecordFactory to Process unknown records, (and DV records for that matter) // ERFListener erfListener = null; // new MyERFListener(); // EventRecordFactory erf = new EventRecordFactory(erfListener, null); // try // { // POIFSFileSystem fs = new POIFSFileSystem(new MemoryStream(baos.ToArray())); // erf.ProcessRecords(fs.CreatePOIFSDocumentReader("Workbook")); // } // catch (RecordFormatException) // { // throw; // } // catch (IOException) // { // throw; // } //} // else verify record ordering by navigating the raw bytes byte[] dvHeaderRecStart = { (byte)0xB2, 0x01, 0x12, 0x00, }; int dvHeaderOffset = FindIndex(wbData, dvHeaderRecStart); Assert.IsTrue(dvHeaderOffset > 0); int nextRecIndex = dvHeaderOffset + 22; int nextSid = ((wbData[nextRecIndex + 0] << 0) & 0x00FF) + ((wbData[nextRecIndex + 1] << 8) & 0xFF00) ; // nextSid should be for a DVRecord. If anything comes between the DV header record // and the DV records, Excel will not be able to Open the workbook without error. if (nextSid == 0x0867) { throw new AssertFailedException("Identified bug 45519"); } Assert.AreEqual(DVRecord.sid, nextSid); }