private void AddValidationInternal(int operatorType, string firstFormula, string secondFormula, int errorStyle, string ruleDescr, string promptDescr, bool allowEmpty, bool inputBox, bool errorBox, bool suppressDropDown, string[] explicitListValues) { int rowNum = _currentRowIndex++; IDataValidationHelper dataValidationHelper = _sheet.GetDataValidationHelper(); IDataValidationConstraint dc = CreateConstraint(dataValidationHelper, operatorType, firstFormula, secondFormula, explicitListValues); IDataValidation dv = dataValidationHelper.CreateValidation(dc, new CellRangeAddressList(rowNum, rowNum, 0, 0)); dv.EmptyCellAllowed = (/*setter*/ allowEmpty); dv.ErrorStyle = (/*setter*/ errorStyle); dv.CreateErrorBox("Invalid Input", "Something is wrong - check condition!"); dv.CreatePromptBox("Validated Cell", "Allowable values have been restricted"); dv.ShowPromptBox = (/*setter*/ inputBox); dv.ShowErrorBox = (/*setter*/ errorBox); dv.SuppressDropDownArrow = (/*setter*/ suppressDropDown); _sheet.AddValidationData(dv); WriteDataValidationSettings(_sheet, _style_1, _style_2, ruleDescr, allowEmpty, inputBox, errorBox); if (_cellStyle != null) { IRow row = _sheet.GetRow(_sheet.PhysicalNumberOfRows - 1); ICell cell = row.CreateCell(0); cell.CellStyle = (/*setter*/ _cellStyle); } WriteOtherSettings(_sheet, _style_1, promptDescr); }
protected void SetOtherValidationParameters(IDataValidation validation) { bool yesNo = true; validation.EmptyCellAllowed = yesNo; validation.ShowErrorBox = yesNo; validation.ShowPromptBox = yesNo; validation.CreateErrorBox("Error Message Title", "Error Message"); validation.CreatePromptBox("Prompt", "Enter some value"); validation.SuppressDropDownArrow = yesNo; }
public void TestGetDataValidationsAny() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.CreateSheet() as HSSFSheet; List <IDataValidation> list = sheet.GetDataValidations(); Assert.AreEqual(0, list.Count); IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper(); IDataValidationConstraint constraint = dataValidationHelper.CreateNumericConstraint(ValidationType.ANY, OperatorType.IGNORED, null, null); CellRangeAddressList AddressList = new CellRangeAddressList(1, 2, 3, 4); IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList); validation.EmptyCellAllowed = (/*setter*/ true); validation.CreateErrorBox("error-title", "error-text"); validation.CreatePromptBox("prompt-title", "prompt-text"); sheet.AddValidationData(validation); list = sheet.GetDataValidations(); // <-- works Assert.AreEqual(1, list.Count); HSSFDataValidation dv = list[(0)] as HSSFDataValidation; { CellRangeAddressList regions = dv.Regions; Assert.AreEqual(1, regions.CountRanges()); CellRangeAddress Address = regions.GetCellRangeAddress(0); Assert.AreEqual(1, Address.FirstRow); Assert.AreEqual(2, Address.LastRow); Assert.AreEqual(3, Address.FirstColumn); Assert.AreEqual(4, Address.LastColumn); } Assert.AreEqual(true, dv.EmptyCellAllowed); Assert.AreEqual(false, dv.SuppressDropDownArrow); Assert.AreEqual(true, dv.ShowErrorBox); Assert.AreEqual("error-title", dv.ErrorBoxTitle); Assert.AreEqual("error-text", dv.ErrorBoxText); Assert.AreEqual(true, dv.ShowPromptBox); Assert.AreEqual("prompt-title", dv.PromptBoxTitle); Assert.AreEqual("prompt-text", dv.PromptBoxText); IDataValidationConstraint c = dv.ValidationConstraint; Assert.AreEqual(ValidationType.ANY, c.GetValidationType()); Assert.AreEqual(OperatorType.IGNORED, c.Operator); }
/// <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); }
//刷新表注释 private void RefreshNote(string fileFullName) { bool change = false; for (int i = 0; i < mFields.Count; ++i) { var filed = mFields[i]; if (filed.Enum) { change = true; break; } } if (!change) { return; } IWorkbook workbook = new HSSFWorkbook(new FileStream(fileFullName, FileMode.Open, FileAccess.ReadWrite)); ISheet sheet = workbook.GetSheetAt(0); string sheetName = sheet.SheetName; ISheet sheetBack = workbook.CreateSheet("__sheetBackup"); sheetBack.CreateFreezePane(2, START_ROW); int columnNum = 0; for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; ++i) { IRow row = sheet.GetRow(i); if (row == null) { continue; } if (row.LastCellNum > columnNum) { columnNum = row.LastCellNum; } IRow rowBack = sheetBack.CreateRow(i); for (int j = 0; j < (mMaxColumn == -1 ? row.LastCellNum : mMaxColumn); ++j) { string val = Util.GetCellString(row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK)); var cell = rowBack.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK); Util.SetCellString(cell, val); } } for (int i = 0; i < columnNum; ++i) { sheetBack.SetColumnWidth(i, sheet.GetColumnWidth(i)); } for (int i = 0; i < mFields.Count; ++i) { var filed = mFields[i]; if (filed.Enum) { IDataValidationHelper helper = sheet.GetDataValidationHelper(); CellRangeAddressList cellRange = new CellRangeAddressList(START_ROW, 65535, i, i); IDataValidationConstraint constraint = helper.CreateExplicitListConstraint(GetEnumList(filed.Type)); IDataValidation dataValidation = helper.CreateValidation(constraint, cellRange); dataValidation.SuppressDropDownArrow = false; dataValidation.CreatePromptBox(filed.Type, GetEnumComment(filed.Type)); sheetBack.AddValidationData(dataValidation); } } workbook.RemoveSheetAt(0); workbook.SetSheetOrder(sheetBack.SheetName, 0); workbook.SetSheetName(0, sheetName); using (FileStream stream = new FileStream(fileFullName, FileMode.Create)) { workbook.Write(stream); } }