public void TestCreateMultipleRegionsValidation() { XSSFWorkbook wb = new XSSFWorkbook(); try { XSSFSheet sheet = wb.CreateSheet() as XSSFSheet; IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper(); IDataValidationConstraint constraint = dataValidationHelper.CreateExplicitListConstraint(new string[] { "A" }); CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(); cellRangeAddressList.AddCellRangeAddress(0, 0, 0, 0); cellRangeAddressList.AddCellRangeAddress(0, 1, 0, 1); cellRangeAddressList.AddCellRangeAddress(0, 2, 0, 2); XSSFDataValidation dataValidation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList) as XSSFDataValidation; sheet.AddValidationData(dataValidation); Assert.AreEqual(new CellRangeAddress(0, 0, 0, 0), sheet.GetDataValidations()[0].Regions.CellRangeAddresses[0]); Assert.AreEqual(new CellRangeAddress(0, 0, 1, 1), sheet.GetDataValidations()[0].Regions.CellRangeAddresses[1]); Assert.AreEqual(new CellRangeAddress(0, 0, 2, 2), sheet.GetDataValidations()[0].Regions.CellRangeAddresses[2]); Assert.AreEqual("A1 B1 C1", dataValidation.GetCTDataValidation().sqref); } finally { wb.Close(); } }
static void Main(string[] args) { InitializeWorkbook(); Sheet sheet1 = hssfworkbook.CreateSheet("Sheet1"); Sheet sheet2 = hssfworkbook.CreateSheet("Sheet2"); //create three items in Sheet2 Row row0 = sheet2.CreateRow(0); Cell cell0 = row0.CreateCell(4); cell0.SetCellValue("Product1"); row0 = sheet2.CreateRow(1); cell0 = row0.CreateCell(4); cell0.SetCellValue("Product2"); row0 = sheet2.CreateRow(2); cell0 = row0.CreateCell(4); cell0.SetCellValue("Product3"); CellRangeAddressList rangeList = new CellRangeAddressList(); //add the data validation to the first column (1-100 rows) rangeList.AddCellRangeAddress(new CellRangeAddress(1, 100, 0, 0)); DVConstraint dvconstraint = DVConstraint.CreateFormulaListConstraint("Sheet2!$E1:$E3"); HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList, dvconstraint); //add the data validation to sheet1 ((HSSFSheet)sheet1).AddValidationData(dataValidation); WriteToFile(); }
public void TestAddValidations() { XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("DataValidations-49244.xlsx"); ISheet sheet = workbook.GetSheetAt(0); List <IDataValidation> dataValidations = ((XSSFSheet)sheet).GetDataValidations(); /** * For each validation type, there are two cells with the same validation. This Tests * application of a single validation defInition to multiple cells. * * For list ( 3 validations for explicit and 3 for formula ) * - one validation that allows blank. * - one that does not allow blank. * - one that does not show the drop down arrow. * = 2 * * For number validations ( integer/decimal and text length ) with 8 different types of operators. * = 50 * * = 52 ( Total ) */ Assert.AreEqual(52, dataValidations.Count); IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper(); int[] validationTypes = new int[] { ValidationType.INTEGER, ValidationType.DECIMAL, ValidationType.TEXT_LENGTH }; int[] SingleOperandOperatorTypes = new int[] { OperatorType.LESS_THAN, OperatorType.LESS_OR_EQUAL, OperatorType.GREATER_THAN, OperatorType.GREATER_OR_EQUAL, OperatorType.EQUAL, OperatorType.NOT_EQUAL }; int[] doubleOperandOperatorTypes = new int[] { OperatorType.BETWEEN, OperatorType.NOT_BETWEEN }; decimal value = (decimal)10, value2 = (decimal)20; double dvalue = (double)10.001, dvalue2 = (double)19.999; int lastRow = sheet.LastRowNum; int offset = lastRow + 3; int lastKnownNumValidations = dataValidations.Count; IRow row = sheet.CreateRow(offset++); ICell cell = row.CreateCell(0); IDataValidationConstraint explicitListValidation = dataValidationHelper.CreateExplicitListConstraint(new String[] { "MA", "MI", "CA" }); CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(); cellRangeAddressList.AddCellRangeAddress(cell.RowIndex, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex); IDataValidation dataValidation = dataValidationHelper.CreateValidation(explicitListValidation, cellRangeAddressList); SetOtherValidationParameters(dataValidation); sheet.AddValidationData(dataValidation); lastKnownNumValidations++; row = sheet.CreateRow(offset++); cell = row.CreateCell(0); cellRangeAddressList = new CellRangeAddressList(); cellRangeAddressList.AddCellRangeAddress(cell.RowIndex, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex); ICell firstCell = row.CreateCell(1); firstCell.SetCellValue("UT"); ICell secondCell = row.CreateCell(2); secondCell.SetCellValue("MN"); ICell thirdCell = row.CreateCell(3); thirdCell.SetCellValue("IL"); int rowNum = row.RowNum + 1; String listFormula = new StringBuilder("$B$").Append(rowNum).Append(":").Append("$D$").Append(rowNum).ToString(); IDataValidationConstraint formulaListValidation = dataValidationHelper.CreateFormulaListConstraint(listFormula); dataValidation = dataValidationHelper.CreateValidation(formulaListValidation, cellRangeAddressList); SetOtherValidationParameters(dataValidation); sheet.AddValidationData(dataValidation); lastKnownNumValidations++; offset++; offset++; for (int i = 0; i < validationTypes.Length; i++) { int validationType = validationTypes[i]; offset = offset + 2; IRow row0 = sheet.CreateRow(offset++); ICell cell_10 = row0.CreateCell(0); cell_10.SetCellValue(validationType == ValidationType.DECIMAL ? "Decimal " : validationType == ValidationType.INTEGER ? "int" : "Text Length"); offset++; for (int j = 0; j < SingleOperandOperatorTypes.Length; j++) { int operatorType = SingleOperandOperatorTypes[j]; IRow row1 = sheet.CreateRow(offset++); //For int (> and >=) we add 1 extra cell for validations whose formulae reference other cells. IRow row2 = i == 0 && j < 2 ? sheet.CreateRow(offset++) : null; cell_10 = row1.CreateCell(0); cell_10.SetCellValue(XSSFDataValidation.operatorTypeMappings[operatorType].ToString()); ICell cell_11 = row1.CreateCell(1); ICell cell_21 = row1.CreateCell(2); ICell cell_22 = i == 0 && j < 2 ? row2.CreateCell(2) : null; ICell cell_13 = row1.CreateCell(3); cell_13.SetCellType(CellType.Numeric); cell_13.SetCellValue(validationType == ValidationType.DECIMAL ? dvalue : (double)value); //First create value based validation; IDataValidationConstraint constraint = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, value.ToString(), null); cellRangeAddressList = new CellRangeAddressList(); cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_11.RowIndex, cell_11.RowIndex, cell_11.ColumnIndex, cell_11.ColumnIndex)); IDataValidation validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList); SetOtherValidationParameters(validation); sheet.AddValidationData(validation); Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count); //Now create real formula based validation. String formula1 = new CellReference(cell_13.RowIndex, cell_13.ColumnIndex).FormatAsString(); constraint = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, formula1, null); if (i == 0 && j == 0) { cellRangeAddressList = new CellRangeAddressList(); cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex)); validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList); SetOtherValidationParameters(validation); sheet.AddValidationData(validation); Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count); cellRangeAddressList = new CellRangeAddressList(); cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_22.RowIndex, cell_22.RowIndex, cell_22.ColumnIndex, cell_22.ColumnIndex)); validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList); SetOtherValidationParameters(validation); sheet.AddValidationData(validation); Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count); } else if (i == 0 && j == 1) { cellRangeAddressList = new CellRangeAddressList(); cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex)); cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_22.RowIndex, cell_22.RowIndex, cell_22.ColumnIndex, cell_22.ColumnIndex)); validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList); SetOtherValidationParameters(validation); sheet.AddValidationData(validation); Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count); } else { cellRangeAddressList = new CellRangeAddressList(); cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex)); validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList); SetOtherValidationParameters(validation); sheet.AddValidationData(validation); Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count); } } for (int j = 0; j < doubleOperandOperatorTypes.Length; j++) { int operatorType = doubleOperandOperatorTypes[j]; IRow row1 = sheet.CreateRow(offset++); cell_10 = row1.CreateCell(0); cell_10.SetCellValue(XSSFDataValidation.operatorTypeMappings[operatorType].ToString()); ICell cell_11 = row1.CreateCell(1); ICell cell_21 = row1.CreateCell(2); ICell cell_13 = row1.CreateCell(3); ICell cell_14 = row1.CreateCell(4); String value1String = validationType == ValidationType.DECIMAL ? dvalue.ToString() : value.ToString(); cell_13.SetCellType(CellType.Numeric); cell_13.SetCellValue(validationType == ValidationType.DECIMAL ? dvalue : (int)value); String value2String = validationType == ValidationType.DECIMAL ? dvalue2.ToString() : value2.ToString(); cell_14.SetCellType(CellType.Numeric); cell_14.SetCellValue(validationType == ValidationType.DECIMAL ? dvalue2 : (int)value2); //First create value based validation; IDataValidationConstraint constraint = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, value1String, value2String); cellRangeAddressList = new CellRangeAddressList(); cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_11.RowIndex, cell_11.RowIndex, cell_11.ColumnIndex, cell_11.ColumnIndex)); IDataValidation validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList); SetOtherValidationParameters(validation); sheet.AddValidationData(validation); Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count); //Now create real formula based validation. String formula1 = new CellReference(cell_13.RowIndex, cell_13.ColumnIndex).FormatAsString(); String formula2 = new CellReference(cell_14.RowIndex, cell_14.ColumnIndex).FormatAsString(); constraint = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, formula1, formula2); cellRangeAddressList = new CellRangeAddressList(); cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex)); validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList); SetOtherValidationParameters(validation); sheet.AddValidationData(validation); Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count); } } workbook = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook); ISheet sheetAt = workbook.GetSheetAt(0); Assert.AreEqual(lastKnownNumValidations, ((XSSFSheet)sheetAt).GetDataValidations().Count); }
/// <summary> /// 设置每列数据 /// </summary> /// <param name="option"></param> /// <param name="cell0"></param> /// <param name="row0"></param> /// <param name="sheet1"></param> /// <param name="patr"></param> /// <param name="RowNuber"></param> private void SetColumn(List <excle_option> option, ref ICell cell0, ref IRow row0, ref ISheet sheet1, ref HSSFPatriarch patr, ref int RowNuber) { int ColumnNum = 0; if (option != null) { for (int i = 0; i < option.Count; i++) { if (option[i].index != null) { ColumnNum = int.Parse(option[i].index.ToString()); } else { ColumnNum = i; } cell0 = row0.CreateCell(ColumnNum); //设置批注和 if (option[i].Description == null || option[i].Description == "") { switch (option[i].TypeOfValue) { case 0: cell0.SetCellValue(option[i].value); break; case 1: bool result = false; if (bool.TryParse(option[i].value, out result)) { cell0.SetCellValue(result); } else { cell0.SetCellValue(option[i].value); } break; case 2: DateTime DateResult = new DateTime(); if (DateTime.TryParse(option[i].value, out DateResult)) { cell0.SetCellValue(DateResult); } else { cell0.SetCellValue(option[i].value); } break; case 3: double DoubleResult = new double(); if (double.TryParse(option[i].value, out DoubleResult)) { cell0.SetCellValue(DoubleResult); } else { cell0.SetCellValue(option[i].value); } break; case 4: cell0.SetCellFormula(option[i].value); break; default: cell0.SetCellValue(option[i].value); break; } } else { cell0.SetCellValue(new HSSFRichTextString(option[i].value)); cell0.CellComment = (addPiZhu(patr, option[i].Description, "")); } //赋值单元格样式 if (option[i].ICellStyle != null) { cell0.CellStyle = option[i].ICellStyle; } if (option[i].option != null) { CellRangeAddressList rangeList = new CellRangeAddressList(); rangeList.AddCellRangeAddress(new CellRangeAddress(1, 100, ColumnNum, ColumnNum)); DVConstraint dvconstraint = DVConstraint.CreateExplicitListConstraint(option[i].option); HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList, dvconstraint); //add the data validation to sheet1 ((HSSFSheet)sheet1).AddValidationData(dataValidation); } //ColumnNum++; } } RowNuber++; }