public void TestGetDataValidationsListFormula() { 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.CreateFormulaListConstraint("A2"); CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0); IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList); validation.SuppressDropDownArrow = (/*setter*/true); sheet.AddValidationData(validation); list = sheet.GetDataValidations(); // <-- works Assert.AreEqual(1, list.Count); HSSFDataValidation dv = list[(0)] as HSSFDataValidation; Assert.AreEqual(true, dv.SuppressDropDownArrow); DVConstraint c = dv.Constraint; Assert.AreEqual(ValidationType.LIST, c.GetValidationType()); Assert.AreEqual("A2", c.Formula1); Assert.AreEqual(null, c.Formula2); Assert.AreEqual(double.NaN, c.Value1); Assert.AreEqual(double.NaN, c.Value2); }
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); }
public void TestGetDataValidationsDecimal(){ 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.CreateDecimalConstraint(OperatorType.BETWEEN, "=A2", "200"); CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0); IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList); sheet.AddValidationData(validation); list = sheet.GetDataValidations(); // <-- works Assert.AreEqual(1, list.Count); HSSFDataValidation dv = list[(0)] as HSSFDataValidation; DVConstraint c = dv.Constraint; Assert.AreEqual(ValidationType.DECIMAL, c.GetValidationType()); Assert.AreEqual(OperatorType.BETWEEN, c.Operator); Assert.AreEqual("A2", c.Formula1); Assert.AreEqual(null, c.Formula2); Assert.AreEqual(double.NaN, c.Value1); Assert.AreEqual(200, c.Value2); }
public void TestGetDataValidationsFormula() { 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.CreateCustomConstraint("A2:A3"); CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0); IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList); sheet.AddValidationData(validation); list = sheet.GetDataValidations(); // <-- works Assert.AreEqual(1, list.Count); HSSFDataValidation dv = list[(0)] as HSSFDataValidation; DVConstraint c = dv.Constraint; Assert.AreEqual(ValidationType.FORMULA, c.GetValidationType()); Assert.AreEqual("A2:A3", c.Formula1); Assert.AreEqual(null, c.Formula2); Assert.AreEqual(double.NaN, c.Value1); Assert.AreEqual(double.NaN, c.Value2); }
/// <summary> /// Used to fill the attendance details /// </summary> /// <param name="worksheet">worksheet used to get the range and fill attendance details</param> private void FillAttendanceDetails(IWorksheet worksheet) { int rowIndex = 2; foreach (EmployeeDetails empDetails in _employeeAttendanceList) { worksheet["A" + rowIndex].Text = empDetails.Name; worksheet["B" + rowIndex].Text = empDetails.Supervisor; for (int colIndex = 0; colIndex < empDetails.Attendances.Count; colIndex++) { worksheet[rowIndex, colIndex + 8].Text = empDetails.Attendances[colIndex]; } rowIndex++; } //Data validation for list IDataValidation validation = worksheet.Range["H2:AL31"].DataValidation; validation.ListOfValues = new string[] { "P", "A", "L", "WE" }; worksheet["C2:C31"].Formula = "=CountIf('H2:AL2',\"P\")"; worksheet["D2:D31"].Formula = "=CountIf('H2:AL2',\"L\")"; worksheet["E2:E31"].Formula = "=CountIf('H2:AL2',\"A\")"; worksheet["F2:F31"].Formula = "=E2/(C2+D2+E2)"; worksheet["G2:G31"].Formula = "=D2/(C2+D2+E2)"; worksheet["F2:G31"].NumberFormat = ".00 %"; }
public void TestGetDataValidationsListExplicit() { 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.CreateExplicitListConstraint(new String[] { "aaa", "bbb", "ccc" }); CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0); IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList); validation.SuppressDropDownArrow = (/*setter*/true); sheet.AddValidationData(validation); list = sheet.GetDataValidations(); // <-- works Assert.AreEqual(1, list.Count); HSSFDataValidation dv = list[(0)] as HSSFDataValidation; Assert.AreEqual(true, dv.SuppressDropDownArrow); DVConstraint c = dv.Constraint; Assert.AreEqual(ValidationType.LIST, c.GetValidationType()); Assert.AreEqual(null, c.Formula1); Assert.AreEqual(null, c.Formula2); Assert.AreEqual(double.NaN, c.Value1); Assert.AreEqual(double.NaN, c.Value2); String[] values = c.ExplicitListValues; Assert.AreEqual(3, values.Length); Assert.AreEqual("aaa", values[0]); Assert.AreEqual("bbb", values[1]); Assert.AreEqual("ccc", values[2]); }
static void AddValidations(ISheet sheet, ExcelVersion version, params DataValidation[] validations) { IDataValidationHelper helper = sheet.GetDataValidationHelper(); foreach (DataValidation validation in validations) { if ((validation.List == null || validation.List.Count == 0) && validation.Name == null) { throw new InvalidOperationException("Validation is invalid"); } IDataValidationConstraint constraint = validation.Name != null? helper.CreateFormulaListConstraint(validation.Name) : helper.CreateExplicitListConstraint(validation.List.ToArray()); var range = new CellRangeAddressList( validation.Range.RowStart ?? 0, validation.Range.RowEnd ?? ExcelHelper.GetRowMax(version) - 1, validation.Range.ColumnStart ?? 0, validation.Range.ColumnEnd ?? ExcelHelper.GetColumnMax(version) - 1); IDataValidation dataValidation = helper.CreateValidation(constraint, range); sheet.AddValidationData(dataValidation); } }
public void TestGetDataValidationsDate() { 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.CreateDateConstraint(OperatorType.EQUAL, "2014/10/25", null, null); CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0); IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList); sheet.AddValidationData(validation); list = sheet.GetDataValidations(); // <-- works Assert.AreEqual(1, list.Count); HSSFDataValidation dv = list[(0)] as HSSFDataValidation; DVConstraint c = dv.Constraint; Assert.AreEqual(ValidationType.DATE, c.GetValidationType()); Assert.AreEqual(OperatorType.EQUAL, c.Operator); Assert.AreEqual(null, c.Formula1); Assert.AreEqual(null, c.Formula2); Assert.AreEqual(DateUtil.GetExcelDate(DateUtil.ParseYYYYMMDDDate("2014/10/25")), c.Value1); Assert.AreEqual(double.NaN, c.Value2); }
public void Test53965() { XSSFWorkbook wb = new XSSFWorkbook(); try { XSSFSheet sheet = wb.CreateSheet() as XSSFSheet; List <IDataValidation> lst = sheet.GetDataValidations(); //<-- works Assert.AreEqual(0, lst.Count); //create the cell that will have the validation applied sheet.CreateRow(0).CreateCell(0); IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper(); IDataValidationConstraint constraint = dataValidationHelper.CreateCustomConstraint("SUM($A$1:$A$1) <= 3500"); CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0); IDataValidation validation = dataValidationHelper.CreateValidation(constraint, addressList); sheet.AddValidationData(validation); // this line caused XmlValueOutOfRangeException , see Bugzilla 3965 lst = sheet.GetDataValidations(); Assert.AreEqual(1, lst.Count); } finally { wb.Close(); } }
static void Main(string[] args) { string workbookName = "test.xlsx"; IWorkbook workbook = null; ISheet sheet = null; IDataValidationHelper dvHelper = null; IDataValidationConstraint dvConstraint = null; IDataValidation validation = null; CellRangeAddressList addressList = null; // Using the ss.usermodel allows this class to support both binary // and xml based workbooks. The choice of which one to create is // made by checking the file extension. if (workbookName.EndsWith(".xlsx")) { workbook = new XSSFWorkbook(); } else { workbook = new HSSFWorkbook(); } // Build the sheet that will hold the data for the validations. This // must be done first as it will create names that are referenced // later. sheet = workbook.CreateSheet("Linked Validations"); BuildDataSheet(sheet); // Build the first data validation to occupy cell A1. Note // that it retrieves it's data from the named area or region called // CHOICES. Further information about this can be found in the // static buildDataSheet() method below. addressList = new CellRangeAddressList(0, 0, 0, 0); dvHelper = sheet.GetDataValidationHelper(); dvConstraint = dvHelper.CreateFormulaListConstraint("CHOICES"); validation = dvHelper.CreateValidation(dvConstraint, addressList); sheet.AddValidationData(validation); // Now, build the linked or dependent drop down list that will // occupy cell B1. The key to the whole process is the use of the // INDIRECT() function. In the buildDataSheet(0 method, a series of // named regions are created and the names of three of them mirror // the options available to the user in the first drop down list // (in cell A1). Using the INDIRECT() function makes it possible // to convert the selection the user makes in that first drop down // into the addresses of a named region of cells and then to use // those cells to populate the second drop down list. addressList = new CellRangeAddressList(0, 0, 1, 1); dvConstraint = dvHelper.CreateFormulaListConstraint( "INDIRECT(UPPER($A$1))"); validation = dvHelper.CreateValidation(dvConstraint, addressList); sheet.AddValidationData(validation); FileStream sw = File.OpenWrite(workbookName); workbook.Write(sw); sw.Close(); }
public App(IDBAccess dbAccess, IStager stager, IScriptRunner scriptRunner, IMessageBroker messageBroker, IDataValidation dataValidation, ILogger <App> log) { Stager = stager; ScriptRunner = scriptRunner; DBAccess = dbAccess; MessageBroker = messageBroker; DataValidation = dataValidation; _log = log; }
/// <summary> /// 创建列表 /// </summary> /// <param name="helper"></param> /// <param name="ListData"></param> /// <returns></returns> internal static IDataValidation getValidationExplicitList(IDataValidationHelper helper, CellRangeAddressList range, string[] ListData) { IDataValidationConstraint constraint = helper.CreateExplicitListConstraint(ListData); IDataValidation validation = helper.CreateValidation(constraint, range); //validation.ShowPromptBox = true; validation.ShowErrorBox = true; return(validation); }
public static void Test() { var filepath = "E:/test.xlsx"; if (File.Exists(filepath)) { File.Delete(filepath); } using (var ms = new FileStream(filepath, FileMode.OpenOrCreate)) { IWorkbook workbook = new XSSFWorkbook(); try { ISheet sheetRef = workbook.CreateSheet("ref");//名为ref的工作表 var items = new dynamic[] { new { code = "1", name = "项目" }, new { code = "2", name = "标段" }, new { code = "3", name = "桥梁" }, new { code = "4", name = "隧道" } }; for (int i = 0; i < items.Length; i++)//A1到A4格子里存放0001到0004,这是下拉框可以选择的4个选项 { var r = sheetRef.CreateRow(i); r.CreateCell(0).SetCellValue(items[i].code); r.CreateCell(1).SetCellValue(items[i].name); //sheetRef.GetRow(i); } IName range = workbook.CreateName(); //创建一个命名公式 range.RefersToFormula = "ref!$A$1:$A$" + items.Length; //公式内容,就是上面的区域 range.NameName = "sectionName"; //公式名称,可以在"公式"-->"名称管理器"中看到 ISheet sheet1 = workbook.CreateSheet("data"); //获得第一个工作表 IRow row = sheet1.CreateRow(0); row.CreateCell(0).SetCellValue("项目名称"); row.CreateCell(1).SetCellValue("地图名称"); row.CreateCell(2).SetCellValue("地图类型-代码"); row.CreateCell(3).SetCellValue("地图类型-名称"); row.CreateCell(4).SetCellValue("经纬度"); //设定公式 row.GetCell(3).SetCellFormula("VLOOKUP(C2,ref!A:B,2,FALSE)"); CellRangeAddressList regions = new CellRangeAddressList(1, 65535, 2, 3); //约束范围:B1到B65535 XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet)sheet1); //获得一个数据验证Helper IDataValidation validation = helper.CreateValidation(helper.CreateFormulaListConstraint("sectionName"), regions); //创建一个特定约束范围内的公式列表约束(即第一节里说的"自定义"方式) validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!"); //不符合约束时的提示 validation.ShowErrorBox = true; //显示上面提示 = True sheet1.AddValidationData(validation); //添加进去 sheet1.ForceFormulaRecalculation = true; workbook.Write(ms); } finally { workbook.Close(); } } }
internal static IDataValidation getValidationYearMonth(IDataValidationHelper helper, CellRangeAddressList range) { IDataValidationConstraint constraint = helper.CreateintConstraint( ST_DataValidationOperator.between.GetHashCode(), "200001", "202012"); IDataValidation validation = helper.CreateValidation(constraint, range); validation.CreateErrorBox("error", "请输入YYYYMM样式的年月,如2012年8月为【201208】"); validation.ShowErrorBox = true; return(validation); }
public IActionResult ExportToExcel() { //创建EXCEL工作薄 IWorkbook workBook = new XSSFWorkbook(); //创建sheet文件表 ISheet sheet = workBook.CreateSheet("上下料点信息"); var expDir = string.Format("{0}Export\\{1}", System.AppDomain.CurrentDomain.BaseDirectory, DateTime.Now.ToString("yyyyMM")); if (!Directory.Exists(expDir)) { Directory.CreateDirectory(expDir); } string filePath = string.Format("{0}\\CD{1}.xlsx", expDir, DateTime.Now.ToString("yyyyMMddHHmmss")); #region 创建Excel表头 //创建表头 IRow header = sheet.CreateRow(0); ICell cell = header.CreateCell(0); cell.SetCellValue("料点编号"); cell = header.CreateCell(1); cell.SetCellValue("料点名称"); cell = header.CreateCell(2); cell.SetCellValue("仓库编号"); cell = header.CreateCell(3); cell.SetCellValue("巷道编号"); cell = header.CreateCell(4); cell.SetCellValue("料点类型"); ISheet sheet1 = workBook.GetSheetAt(0); //获得第一个工作表 CellRangeAddressList regions = new CellRangeAddressList(1, 65535, 4, 4); //设定位置 行起,行止,列起,列终 XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet)sheet1); //获得一个数据验证Helper IDataValidation validation = helper.CreateValidation(helper.CreateExplicitListConstraint(new string[] { "In", "Out", "InOut", "OutBack" }), regions); //创建一个特定约束范围内的公式列表约束(即第一节里说的"自定义"方式) validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!"); //不符合约束时的提示 validation.ShowErrorBox = true; //显示上面提示 = True sheet1.AddValidationData(validation); //添加进去 sheet1.ForceFormulaRecalculation = true; #endregion //工作流写入,通过流的方式进行创建生成文件 using (MemoryStream stream = new MemoryStream()) { workBook.Write(stream); byte[] buffer = stream.ToArray(); return(File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", string.Format("上下料点信息表_{0}.xlsx", DateTime.Now.ToString("yyyyMMddHHmmss")))); } }
/// <summary> /// 设置单元格验证(数字) /// 创建者:戚鹏 /// 创建日期:2013.5.20 /// </summary> /// <param name="RowCount"></param> /// <param name="ColNum"></param> /// <param name="FirstValue"></param> /// <param name="LastValue"></param> /// <returns></returns> internal static IDataValidation getValidationInt(IDataValidationHelper helper, CellRangeAddressList range, int from = 0, int to = 99999999) { IDataValidationConstraint constraint = helper.CreateintConstraint( ST_DataValidationOperator.between.GetHashCode(), from.ToString(), to.ToString()); IDataValidation validation = helper.CreateValidation(constraint, range); //validation.CreateErrorBox("error", "You must input a numeric between 1 and 50."); validation.ShowErrorBox = true; return(validation); }
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. IWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("dvEmpty.xls"); int dvRow = 0; ISheet sheet = wb.GetSheetAt(0); IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper(); IDataValidationConstraint dc = dataValidationHelper.CreateintConstraint(OperatorType.EQUAL, "42", null); IDataValidation dv = dataValidationHelper.CreateValidation(dc, new CellRangeAddressList(dvRow, dvRow, 0, 0)); dv.EmptyCellAllowed = (/*setter*/ false); dv.ErrorStyle = (/*setter*/ ERRORSTYLE.STOP); dv.ShowPromptBox = (/*setter*/ true); dv.CreateErrorBox("Xxx", "Yyy"); dv.SuppressDropDownArrow = (/*setter*/ true); sheet.AddValidationData(dv); MemoryStream baos = new MemoryStream(); try { wb.Write(baos); } catch (IOException e) { throw new RuntimeException(e); } byte[] wbData = baos.ToArray(); 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 AssertionException("Identified bug 45519"); } Assert.AreEqual(DVRecord.sid, nextSid); }
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> /// 设置excel下拉列表 /// </summary> /// <param name="sheet">excel工作表</param> /// <param name="sheetName">下拉列表使用数据的sheet的名字</param> /// <param name="rangeName">下拉列表使用数据的域</param> /// <param name="names">待处理的姓名字符串</param> /// <param name="count">数据表中数据行数,ref引用格式</param> /// <param name="Row">所要设置的下拉列表所在行</param> /// <param name="Col">所要设置的下拉列表所在列</param> public static void SetDropDownList(ISheet sheet, string sheetName, string rangeName, string names, ref int count, int Row, int Col) { ISheet dataSheet = null; IRow row = null; ICell cell = null; IName name = null; IDataValidationHelper dvHelper = null; IDataValidationConstraint dvConstraint = null; IDataValidation validation = null; CellRangeAddressList addressList = null; dataSheet = sheet.Workbook.GetSheet(sheetName); //获取存储下拉数据的sheet if (dataSheet == null) //若不存在,则创建 { dataSheet = sheet.Workbook.CreateSheet(sheetName); } //string[] list = new string[] { "123", "456", "789" }; string[] list = BuildDropData(names); //获取下拉数据 row = dataSheet.CreateRow(count++); for (int i = 0; i < list.Count(); i++) //将数据写入sheet中 { cell = row.CreateCell(i); cell.SetCellValue(list[i]); } //生成一个列表引用区域,并唯一标识它 name = sheet.Workbook.CreateName(); name.RefersToFormula = string.Format("'{0}'!$A${1}:${2}${3}", sheetName, count, IndexToColumn(list.Count()), count); name.NameName = rangeName.ToUpper() + Row.ToString(); addressList = new CellRangeAddressList(Row, Row, Col, Col); //设置生成下拉框的行和列 dvHelper = sheet.GetDataValidationHelper(); dvConstraint = dvHelper.CreateFormulaListConstraint(rangeName.ToUpper() + Row.ToString()); validation = dvHelper.CreateValidation(dvConstraint, addressList); //绑定下拉框和作用区域 sheet.AddValidationData(validation); IWorkbook wb = sheet.Workbook; wb.SetSheetHidden(wb.GetSheetIndex(dataSheet), 1); //隐藏数据sheet }
/// <summary> /// 设置字段为下拉框 /// </summary> /// <param name="ruleName">规则名称</param> /// <param name="fieldName">字段名称</param> private void SetField2Select(string ruleName, string fieldName) { //查找字符索引 var field = _config.Row.Where(t => t.Field == fieldName).FirstOrDefault(); if (field == null) { return; } CellRangeAddressList regions = new CellRangeAddressList(_config.Prop.StartRow - 1, 65535, field.ColumnIndex, field.ColumnIndex); //约束范围:B1到B65535 XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet)_sheet); //获得一个数据验证Helper IDataValidation validation = helper.CreateValidation(helper.CreateFormulaListConstraint(ruleName), regions); //创建一个特定约束范围内的公式列表约束(即第一节里说的"自定义"方式) validation.EmptyCellAllowed = true; validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!"); //不符合约束时的提示 validation.ShowErrorBox = true; //显示上面提示 = True _sheet.AddValidationData(validation); //添加进去 }
/// <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 TestDvProtectionOrder_bug47363b() { IWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Sheet1"); sheet.ProtectSheet("secret"); IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper(); IDataValidationConstraint dvc = dataValidationHelper.CreateintConstraint(OperatorType.BETWEEN, "10", "100"); CellRangeAddressList numericCellAddressList = new CellRangeAddressList(0, 0, 1, 1); IDataValidation dv = dataValidationHelper.CreateValidation(dvc, numericCellAddressList); try { sheet.AddValidationData(dv); } catch (InvalidOperationException e) { String expMsg = "Unexpected (NPOI.HSSF.Record.PasswordRecord) while looking for DV Table insert pos"; if (expMsg.Equals(e.Message)) { throw new AssertionException("Identified bug 47363b"); } throw e; } TestCases.HSSF.UserModel.RecordInspector.RecordCollector rc; rc = new RecordInspector.RecordCollector(); ((HSSFSheet)sheet).Sheet.VisitContainedRecords(rc, 0); int nRecsWithProtection = rc.Records.Length; sheet.ProtectSheet(null); rc = new RecordInspector.RecordCollector(); ((HSSFSheet)sheet).Sheet.VisitContainedRecords(rc, 0); int nRecsWithoutProtection = rc.Records.Length; Assert.AreEqual(4, nRecsWithProtection - nRecsWithoutProtection); }
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; }
private void WriteWorkSheet_DataValidations_formulas(IDataValidation item, XElement dataValidationNode) { if (item is DataValidationInt) { DataValidationInt intItem = item as DataValidationInt; WriteWorkSheet_DataValidations_formulas_int(intItem, dataValidationNode); } else if (item is DataValidationDecimal) { DataValidationDecimal decimalItem = item as DataValidationDecimal; WriteWorkSheet_DataValidations_formulas_decimal(decimalItem, dataValidationNode); } else if (item is DataValidationList) { DataValidationList listItem = item as DataValidationList; WriteWorkSheet_DataValidations_formulas_list(listItem, dataValidationNode); } else if (item is DataValidationDateTime) { DataValidationDateTime dateTime = item as DataValidationDateTime; WriteWorkSheet_DataValidations_formulas_dateTime(dateTime, dataValidationNode); } else if (item is DataValidationTime) { DataValidationTime timeItem = item as DataValidationTime; WriteWorkSheet_DataValidations_formulas_Time(timeItem, dataValidationNode); } else if (item is DataValidationCustom) { DataValidationCustom customItem = item as DataValidationCustom; WriteWorkSheet_DataValidations_formulas_Custom(customItem, dataValidationNode); } }
void ISheet.AddValidationData(IDataValidation dataValidation) { throw new System.NotImplementedException(); }
/// <summary> /// Creates a data validation object /// </summary> /// <param name="dataValidation">The data validation object settings</param> public void AddValidationData(IDataValidation dataValidation) { if (dataValidation == null) { throw new ArgumentException("objValidation must not be null"); } HSSFDataValidation hssfDataValidation = (HSSFDataValidation)dataValidation; DataValidityTable dvt = _sheet.GetOrCreateDataValidityTable(); DVRecord dvRecord = hssfDataValidation.CreateDVRecord(this); dvt.AddDataValidation(dvRecord); }
public void AddValidationData(IDataValidation dataValidation) { _sh.AddValidationData(dataValidation); }
public IActionResult ExportToExcel() { //创建EXCEL工作薄 IWorkbook workBook = new XSSFWorkbook(); //创建sheet文件表 ISheet sheet = workBook.CreateSheet("客户信息"); var expDir = string.Format("{0}Export\\{1}", System.AppDomain.CurrentDomain.BaseDirectory, DateTime.Now.ToString("yyyyMM")); if (!Directory.Exists(expDir)) { Directory.CreateDirectory(expDir); } string filePath = string.Format("{0}\\CD{1}.xlsx", expDir, DateTime.Now.ToString("yyyyMMddHHmmss")); #region 创建Excel表头 //创建表头 IRow header = sheet.CreateRow(0); ICell cell = header.CreateCell(0); cell.SetCellValue("客户编号"); cell = header.CreateCell(1); cell.SetCellValue("客户名称"); cell = header.CreateCell(2); cell.SetCellValue("客户类型"); cell = header.CreateCell(3); cell.SetCellValue("联系电话/Phone"); cell = header.CreateCell(4); cell.SetCellValue("传真/Fax"); cell = header.CreateCell(5); cell.SetCellValue("邮箱/Email"); //IName range = workBook.CreateName();//创建一个命名公式 //range.RefersToFormula = "客户信息!$A$1:$A$4";//公式内容,就是上面的区域 //range.NameName = "sectionName";//公式名称,可以在"公式"-->"名称管理器"中看到 ISheet sheet1 = workBook.GetSheetAt(0); //获得第一个工作表 CellRangeAddressList regions = new CellRangeAddressList(1, 65535, 2, 2); //设定位置 行起,行止,列起,列终 XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet)sheet1); //获得一个数据验证Helper IDataValidation validation = helper.CreateValidation(helper.CreateExplicitListConstraint(new string[] { "Company", "Personal", "Virtual", "Internal" }), regions); //创建一个特定约束范围内的公式列表约束(即第一节里说的"自定义"方式) validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!"); //不符合约束时的提示 validation.ShowErrorBox = true; //显示上面提示 = True sheet1.AddValidationData(validation); //添加进去 sheet1.ForceFormulaRecalculation = true; #endregion //工作流写入,通过流的方式进行创建生成文件 using (MemoryStream stream = new MemoryStream()) { workBook.Write(stream); byte[] buffer = stream.ToArray(); return(File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", string.Format("客户信息表_{0}.xlsx", DateTime.Now.ToString("yyyyMMddHHmmss")))); } }
private async void btnGenerateExcel_Click(object sender, RoutedEventArgs e) { StorageFile storageFile; if (!(Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons"))) { FileSavePicker savePicker = new FileSavePicker(); savePicker.SuggestedStartLocation = PickerLocationId.Desktop; savePicker.SuggestedFileName = "DataValidationSample"; if (rdBtn2003.IsChecked.Value) { savePicker.FileTypeChoices.Add("Excel Files", new List <string>() { ".xls" }); } else { savePicker.FileTypeChoices.Add("Excel Files", new List <string>() { ".xlsx", }); } storageFile = await savePicker.PickSaveFileAsync(); } else { StorageFolder local = Windows.Storage.ApplicationData.Current.LocalFolder; if (rdBtn2003.IsChecked.Value) { storageFile = await local.CreateFileAsync("DataValidationSample.xls", CreationCollisionOption.ReplaceExisting); } else { storageFile = await local.CreateFileAsync("DataValidationSample.xlsx", CreationCollisionOption.ReplaceExisting); } } if (storageFile == null) { return; } //Instantiate excel Engine ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; if (rdBtn2003.IsChecked.Value) { application.DefaultVersion = ExcelVersion.Excel97to2003; } else { application.DefaultVersion = ExcelVersion.Excel2013; } IWorkbook workbook = application.Workbooks.Create(1); IWorksheet sheet = workbook.Worksheets[0]; //Data validation to list the values in the first cell IDataValidation validation = sheet.Range["C7"].DataValidation; sheet.Range["B7"].Text = "Select an item from the validation list"; validation.ListOfValues = new string[] { "PDF", "XlsIO", "DocIO" }; validation.PromptBoxText = "Data Validation list"; validation.IsPromptBoxVisible = true; validation.ShowPromptBox = true; sheet.Range["C7"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium; sheet.Range["C7"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; sheet.Range["C7"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; // Data Validation for Numbers IDataValidation validation1 = sheet.Range["C9"].DataValidation; sheet.Range["B9"].Text = "Enter a Number to validate"; validation1.AllowType = ExcelDataType.Integer; validation1.CompareOperator = ExcelDataValidationComparisonOperator.Between; validation1.FirstFormula = "0"; validation1.SecondFormula = "10"; validation1.ShowErrorBox = true; validation1.ErrorBoxText = "Value must be between 0 and 10"; validation1.ErrorBoxTitle = "ERROR"; validation1.PromptBoxText = "Value must be between 0 and 10"; validation1.ShowPromptBox = true; sheet.Range["C9"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium; sheet.Range["C9"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; sheet.Range["C9"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; // Data Validation for Date IDataValidation validation2 = sheet.Range["C11"].DataValidation; sheet.Range["B11"].Text = "Enter the Date to validate"; validation2.AllowType = ExcelDataType.Date; validation2.CompareOperator = ExcelDataValidationComparisonOperator.Between; validation2.FirstDateTime = new DateTime(2012, 1, 1); validation2.SecondDateTime = new DateTime(2012, 12, 31); validation2.ShowErrorBox = true; validation2.ErrorBoxText = "Value must be from 01/1/2012 to 31/12/2012"; validation2.ErrorBoxTitle = "ERROR"; validation2.PromptBoxText = "Value must be from 01/1/2012 to 31/12/2012"; validation2.ShowPromptBox = true; sheet.Range["C11"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium; sheet.Range["C11"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; sheet.Range["C11"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; // Data Validation for TextLength IDataValidation validation3 = sheet.Range["C13"].DataValidation; sheet.Range["B13"].Text = "Enter the Text to validate"; validation3.AllowType = ExcelDataType.TextLength; validation3.CompareOperator = ExcelDataValidationComparisonOperator.Between; validation3.FirstFormula = "1"; validation3.SecondFormula = "6"; validation3.ShowErrorBox = true; validation3.ErrorBoxText = "Maximum 6 characters are allowed"; validation3.ErrorBoxTitle = "ERROR"; validation3.PromptBoxText = "Maximum 6 characters are allowed"; validation3.ShowPromptBox = true; sheet.Range["C13"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium; sheet.Range["C13"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; sheet.Range["C13"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; // Data Validation for Time IDataValidation validation4 = sheet.Range["C15"].DataValidation; sheet.Range["B15"].Text = "Enter the Time to validate"; validation4.AllowType = ExcelDataType.Time; validation4.CompareOperator = ExcelDataValidationComparisonOperator.Between; validation4.FirstFormula = "10"; validation4.SecondFormula = "12"; validation4.ShowErrorBox = true; validation4.ErrorBoxText = "Time must be between 10 and 12"; validation4.ErrorBoxTitle = "ERROR"; validation4.PromptBoxText = "Time must be between 10 and 12"; validation4.ShowPromptBox = true; sheet.Range["C15"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium; sheet.Range["C15"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; sheet.Range["C15"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; sheet.Range["B2:C2"].Merge(); sheet.Range["B2"].Text = "Simple Data validation"; sheet.Range["B5"].Text = "Validation criteria"; sheet.Range["C5"].Text = "Validation"; sheet.Range["B5"].CellStyle.Font.Bold = true; sheet.Range["C5"].CellStyle.Font.Bold = true; sheet.Range["B2"].CellStyle.Font.Bold = true; sheet.Range["B2"].CellStyle.Font.Size = 16; sheet.Range["B2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter; sheet.AutofitColumn(2); sheet["B7"].ColumnWidth = 40.3; sheet.UsedRange.AutofitRows(); await workbook.SaveAsAsync(storageFile); workbook.Close(); excelEngine.Dispose(); MessageDialog msgDialog = new MessageDialog("Do you want to view the Document?", "File has been created successfully."); UICommand yesCmd = new UICommand("Yes"); msgDialog.Commands.Add(yesCmd); UICommand noCmd = new UICommand("No"); msgDialog.Commands.Add(noCmd); IUICommand cmd = await msgDialog.ShowAsync(); if (cmd == yesCmd) { // Launch the saved file bool success = await Windows.System.Launcher.LaunchFileAsync(storageFile); } }
private void WriteWorkSheet_DataValidations_Attributes(XElement dataValidationNode, IDataValidation item) { if (item.AllowBlank.ConvertBool()) { dataValidationNode.Add(new XAttribute(XName.Get("allowBlank"), 1)); } if (item.ShowInputMessage.ConvertBool()) { dataValidationNode.Add(new XAttribute(XName.Get("showInputMessage"), 1)); } if (item.ShowErrorMessage.ConvertBool()) { dataValidationNode.Add(new XAttribute(XName.Get("showErrorMessage"), 1)); } if (!string.IsNullOrEmpty(item.ErrorTitle)) { dataValidationNode.Add(new XAttribute(XName.Get("errorTitle"), item.ErrorTitle)); } if (!string.IsNullOrEmpty(item.Error)) { dataValidationNode.Add(new XAttribute(XName.Get("error"), item.Error)); } if (!string.IsNullOrEmpty(item.PromptTitle)) { dataValidationNode.Add(new XAttribute(XName.Get("promptTitle"), item.PromptTitle)); } if (!string.IsNullOrEmpty(item.Prompt)) { dataValidationNode.Add(new XAttribute(XName.Get("prompt"), item.Prompt)); } }
void ISheet.AddValidationData(IDataValidation dataValidation) { throw new System.NotImplementedException(); }
// // GET: /Default/ public ActionResult Default(string SaveOption) { if (SaveOption == null) { return(View()); } //New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open]. //The instantiation process consists of two steps. //Step 1 : Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); //Step 2 : Instantiate the excel application object. IApplication application = excelEngine.Excel; //A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel] //The new workbook will have 12 worksheets IWorkbook workbook = application.Workbooks.Open(DefaultResolveApplicationDataPath(@"BudgetPlanner.xls")); IWorksheet sheet = workbook.Worksheets[1]; sheet.FirstVisibleRow = 3; IFont font = workbook.CreateFont(); font.Bold = true; #region TextBox ITextBoxShape textbox = sheet.TextBoxes.AddTextBox(5, 2, 40, 140); textbox.Text = "Quick Budget"; textbox.RichText.SetFont(0, 11, font); textbox.VAlignment = ExcelCommentVAlign.Center; textbox.HAlignment = ExcelCommentHAlign.Center; textbox.Fill.FillType = ExcelFillType.Gradient; textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor; textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2); textbox.Fill.BackColor = Color.FromArgb(204, 204, 255); textbox = sheet.TextBoxes.AddTextBox(7, 2, 25, 140); textbox.Text = "Income"; textbox.RichText.SetFont(0, 5, font); textbox.VAlignment = ExcelCommentVAlign.Center; textbox.HAlignment = ExcelCommentHAlign.Center; textbox.Fill.FillType = ExcelFillType.Gradient; textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor; textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2); textbox.Fill.BackColor = Color.FromArgb(0, 0, 128); textbox = sheet.TextBoxes.AddTextBox(16, 2, 25, 140); textbox.Text = "Spending"; textbox.RichText.SetFont(0, 7, font); textbox.VAlignment = ExcelCommentVAlign.Center; textbox.HAlignment = ExcelCommentHAlign.Center; textbox.Fill.FillType = ExcelFillType.Gradient; textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor; textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2); textbox.Fill.BackColor = Color.FromArgb(0, 0, 128); #endregion #region Write Text and Numbers sheet.Range["O6"].Text = "Weekly"; sheet.Range["E7"].Text = "Frequency"; sheet.Range["F7"].Text = "Amount"; sheet.Range["G7"].Text = "Monthly"; sheet.Range["H7"].Text = "Yearly"; sheet.Range["B8"].Text = "Total Income"; sheet.Range["C9"].Text = "Salary/Wages"; sheet.Range["C10"].Text = "Salary/Wages(Spouse)"; sheet.Range["C11"].Text = "Other"; sheet.Range["C12"].Text = "Other"; sheet.Range["C13"].Text = "Other"; sheet.Range["B17"].Text = "Transportation"; sheet.Range["F25"].Number = 3000; sheet.Range["F9"].Number = 55000; sheet.Range["F10"].Number = 35000; sheet.Range["C18"].Text = "Auto Loan/Lease"; sheet.Range["C19"].Text = "Insurance"; sheet.Range["C20"].Text = "Gas "; sheet.Range["C21"].Text = "Maintenance "; sheet.Range["C22"].Text = "Registration/Inspection"; sheet.Range["C23"].Text = "Bill's train pass"; sheet.Range["C24"].Text = "Jane's bus pass"; sheet.Range["C25"].Text = "Other"; sheet.Range["E16"].Text = "Total"; sheet.Range["N6"].Text = "Chart"; sheet.Range["B27"].Text = "Home"; sheet.Range["F28"].Number = 20000; sheet.Range["F29"].Number = 5000; sheet.Range["F33"].Number = 5000; sheet.Range["C28"].Text = "EMI"; sheet.Range["C29"].Text = "Rent"; sheet.Range["C30"].Text = "Maintanence"; sheet.Range["C31"].Text = "Insurance"; sheet.Range["C32"].Text = "Furniture"; sheet.Range["C33"].Text = "Household Supplies"; sheet.Range["C34"].Text = "Groceries"; sheet.Range["C35"].Text = "Real Estate Tax"; sheet.Range["C36"].Text = "Other"; sheet.Range["B39"].Text = "Utilities"; sheet.Range["F41"].Number = 1000; sheet.Range["F42"].Number = 250; sheet.Range["F43"].Number = 150; sheet.Range["F45"].Number = 175; sheet.Range["C40"].Text = "Phone - Home"; sheet.Range["C41"].Text = "Phone - Cell"; sheet.Range["C42"].Text = "Cable"; sheet.Range["C43"].Text = "Gas"; sheet.Range["C44"].Text = "Water"; sheet.Range["C45"].Text = "Electricity"; sheet.Range["C46"].Text = "Internet"; sheet.Range["C47"].Text = "Other"; sheet.Range["B50"].Text = "Health"; sheet.Range["F53"].Number = 500; sheet.Range["C51"].Text = "Dental"; sheet.Range["C52"].Text = "Medical"; sheet.Range["C53"].Text = "Medication"; sheet.Range["C54"].Text = "Vision/contacts"; sheet.Range["C55"].Text = "Life Insurance"; sheet.Range["C56"].Text = "Electricity"; sheet.Range["C57"].Text = "Other"; #endregion #region Cell styles IStyle tableStyle = workbook.Styles.Add("TableStyle"); tableStyle.BeginUpdate(); tableStyle.Color = Color.White; tableStyle.Borders[ExcelBordersIndex.EdgeBottom].ColorRGB = Color.FromArgb(150, 150, 150); tableStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; tableStyle.Borders[ExcelBordersIndex.EdgeLeft].ColorRGB = Color.FromArgb(150, 150, 150); tableStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; tableStyle.Borders[ExcelBordersIndex.EdgeRight].ColorRGB = Color.FromArgb(150, 150, 150); tableStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; tableStyle.Borders[ExcelBordersIndex.EdgeTop].ColorRGB = Color.FromArgb(150, 150, 150); tableStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; tableStyle.EndUpdate(); sheet.Range["E7:H7"].CellStyle.Font.Bold = true; sheet.Range["B17"].CellStyle.Font.Bold = true; sheet.Range["B27"].CellStyle.Font.Bold = true; sheet.Range["B39"].CellStyle.Font.Bold = true; sheet.Range["B50"].CellStyle.Font.Bold = true; sheet.Range["E7:H7"].CellStyle.Font.Underline = ExcelUnderline.Single; sheet.Range["B7:H14"].CellStyle.Color = Color.FromArgb(223, 223, 223); sheet.Range["C9:C13"].CellStyle = tableStyle; sheet.Range["E9:F13"].CellStyle = tableStyle; sheet.Range["B16:H26"].CellStyle.Color = Color.FromArgb(223, 223, 223); sheet.Range["B17:C17"].CellStyle.Color = Color.White; sheet.Range["C18:C25"].CellStyle = tableStyle; sheet.Range["O6"].CellStyle = tableStyle; sheet.Range["E18:F25"].CellStyle = tableStyle; sheet.Range["B27:H38"].CellStyle.Color = Color.FromArgb(223, 223, 223); sheet.Range["C28:C36"].CellStyle = tableStyle; sheet.Range["B27:C27"].CellStyle.Color = Color.White; sheet.Range["E28:F36"].CellStyle = tableStyle; sheet.Range["B39:H49"].CellStyle.Color = Color.FromArgb(223, 223, 223); sheet.Range["C40:C47"].CellStyle = tableStyle; sheet.Range["B39:C39"].CellStyle.Color = Color.White; sheet.Range["E40:F47"].CellStyle = tableStyle; sheet.Range["B50:H58"].CellStyle.Color = Color.FromArgb(223, 223, 223); sheet.Range["C51:C57"].CellStyle = tableStyle; sheet.Range["B50:C50"].CellStyle.Color = Color.White; sheet.Range["E51:F57"].CellStyle = tableStyle; #endregion #region Data Validation IDataValidation validation = sheet.Range["E9:E13"].DataValidation; sheet.Range["E9:E13"].Text = "Monthly"; validation.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" }; IDataValidation validation1 = sheet.Range["E18:E25"].DataValidation; sheet.Range["E18:E25"].Text = "Monthly"; validation1.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" }; IDataValidation validation2 = sheet.Range["O6"].DataValidation; validation2.ListOfValues = new string[] { "Weekly", "Monthly", "Yearly" }; IDataValidation validation3 = sheet.Range["E28:E37"].DataValidation; sheet.Range["E28:E36"].Text = "Monthly"; validation3.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" }; IDataValidation validation4 = sheet.Range["E40:E47"].DataValidation; sheet.Range["E40:E47"].Text = "Monthly"; validation4.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" }; IDataValidation validation5 = sheet.Range["E51:E57"].DataValidation; sheet.Range["E51:E57"].Text = "Monthly"; validation5.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" }; #endregion #region Formulas sheet.Range["G8"].Formula = "SUM(G9:G13)"; sheet.Range["H8"].Formula = "SUM(H9:H13)"; sheet.Range["G17"].Formula = "SUM(G18:G25)"; sheet.Range["H17"].Formula = "SUM(H18:H25)"; sheet.Range["G16"].Formula = "G17+G27+G39+G50+G59+G71"; sheet.Range["h16"].Formula = "H17+H27+H39+H50+H59+H71"; for (int i = 9; i <= 13; i++) { sheet.Range["G" + i].Formula = "F" + i + "*A" + i; sheet.Range["H" + i].Formula = "G" + i + "*12"; } for (int i = 18; i <= 25; i++) { sheet.Range["G" + i].Formula = "F" + i + "*A" + i; sheet.Range["H" + i].Formula = "G" + i + "*12"; } sheet.Range["G27"].Formula = "SUM(G28:G36)"; sheet.Range["H27"].Formula = "SUM(H28:H37)"; for (int i = 28; i <= 36; i++) { sheet.Range["G" + i].Formula = "F" + i + "*A" + i; sheet.Range["H" + i].Formula = "G" + i + "*12"; } sheet.Range["G39"].Formula = "SUM(G40:G47)"; sheet.Range["H39"].Formula = "SUM(H40:H47)"; for (int i = 40; i <= 47; i++) { sheet.Range["G" + i].Formula = "F" + i + "*A" + i; sheet.Range["H" + i].Formula = "G" + i + "*12"; } sheet.Range["G50"].Formula = "SUM(G51:G57)"; sheet.Range["H50"].Formula = "SUM(H51:H57)"; for (int i = 51; i <= 57; i++) { sheet.Range["G" + i].Formula = "F" + i + "*A" + i; sheet.Range["H" + i].Formula = "G" + i + "*12"; } #endregion #region SummaryChart //Clustered Column Chart IChartShape chart = sheet.Charts.Add(); //Set Chart Type chart.ChartType = ExcelChartType.Bar_Clustered; //Set DataRange. chart.Series.Add("Expense"); chart.Series[0].Values = workbook.Worksheets["Sheet1"].Range["N10"]; chart.Series[0].DataPoints[0].DataLabels.IsValue = true; chart.Series[0].DataPoints[0].DataLabels.Size = 7f; chart.Series.Add("Income"); chart.Series[1].Values = workbook.Worksheets["Sheet1"].Range["N9"]; chart.Series[1].DataPoints[0].DataLabels.IsValue = true; chart.Series[1].DataPoints[0].DataLabels.Size = 7f; chart.Series.Add("Balance"); chart.Series[2].Values = workbook.Worksheets["Sheet1"].Range["N8"]; chart.Series[2].DataPoints[0].DataLabels.IsValue = true; chart.Series[2].DataPoints[0].DataLabels.Size = 7f; chart.PrimaryValueAxis.NumberFormat = "$#,##0"; chart.PrimaryCategoryAxis.Visible = false; //Format Chart Area IChartFrameFormat chartArea = chart.ChartArea; //Style chartArea.Border.LinePattern = ExcelChartLinePattern.Solid; chartArea.Border.LineColor = Color.Gray; chartArea.Border.LineWeight = ExcelChartLineWeight.Medium; //Plot Area IChartFrameFormat chartPlotArea = chart.PlotArea; chartPlotArea.Border.LinePattern = ExcelChartLinePattern.Solid; chart.PlotArea.Border.LineColor = Color.Gray; chart.Legend.Position = ExcelLegendPosition.Bottom; //Embedded chart position. chart.TopRow = 7; chart.BottomRow = 22; chart.LeftColumn = 9; chart.RightColumn = 16; chart.ChartTitle = "Budget Summary"; chart.ChartTitleArea.Bold = true; #endregion #region SpendingChart chart = sheet.Charts.Add(); chart.ChartTitle = "Spending Summary"; chart.ChartTitleArea.Bold = true; //Set Chart Type chart.ChartType = ExcelChartType.Pie_3D; //Set DataRange. chart.DataRange = workbook.Worksheets["Sheet1"].Range["J9:K12"]; chart.IsSeriesInRows = false; chart.Series[0].Values = workbook.Worksheets["Sheet1"].Range["K9:K12"]; chart.Series[0].CategoryLabels = workbook.Worksheets["Sheet1"].Range["J9:J12"]; chart.Series[0].Name = "Spending summary"; chart.Series[0].DataPoints[0].DataLabels.IsValue = true; chart.Series[0].DataPoints[0].DataLabels.Size = 7f; chart.Series[0].DataPoints[1].DataLabels.IsValue = true; chart.Series[0].DataPoints[1].DataLabels.Size = 7f; chart.Series[0].DataPoints[2].DataLabels.IsValue = true; chart.Series[0].DataPoints[2].DataLabels.Size = 7f; chart.Series[0].DataPoints[3].DataLabels.IsValue = true; chart.Series[0].DataPoints[3].DataLabels.Size = 7f; chart.PrimaryValueAxis.NumberFormat = "$#,##0"; //Format Chart Area chartArea = chart.ChartArea; //Style chartArea.Border.LinePattern = ExcelChartLinePattern.Solid; chartArea.Border.LineColor = Color.Gray; chartArea.Border.LineWeight = ExcelChartLineWeight.Medium; //Plot Area chartPlotArea = chart.PlotArea; chartPlotArea.Border.LinePattern = ExcelChartLinePattern.Solid; chart.PlotArea.Border.LineColor = Color.Gray; chartPlotArea.Fill.ForeColor = Color.FromArgb(223, 223, 223); chart.Legend.Position = ExcelLegendPosition.Bottom; //Embedded chart position. chart.TopRow = 25; chart.BottomRow = 42; chart.LeftColumn = 9; chart.RightColumn = 16; #endregion #region Sheet View workbook.Worksheets["Sheet1"].Visibility = WorksheetVisibility.Hidden; workbook.Worksheets[0].Activate(); workbook.TabSheets[0].TabColor = ExcelKnownColors.Blue; workbook.TabSheets[1].TabColor = ExcelKnownColors.Blue; workbook.Worksheets[1].IsRowColumnHeadersVisible = false; sheet.InsertColumn(9); #endregion try { //Saving the workbook to disk. if (SaveOption == "Xls") { //Save as .xls format return(excelEngine.SaveAsActionResult(workbook, "SpreadSheet.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97)); } //Save as .xlsx format else { workbook.Version = ExcelVersion.Excel2016; return(excelEngine.SaveAsActionResult(workbook, "SpreadSheet.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016)); } } catch (Exception) { } //Close the workbook. workbook.Close(); excelEngine.Dispose(); return(View()); }
// // GET: /DataValidation/ public ActionResult DataValidation(string SaveOption) { if (SaveOption == null) { return(View()); } //New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open]. //The instantiation process consists of two steps. //Step 1 : Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); //Step 2 : Instantiate the excel application object. IApplication application = excelEngine.Excel; if (SaveOption == "Xls") { application.DefaultVersion = ExcelVersion.Excel97to2003; } else { application.DefaultVersion = ExcelVersion.Excel2016; } //A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel] //The new workbook will have 3 worksheets IWorkbook workbook = application.Workbooks.Create(3); //The first worksheet object in the worksheets collection is accessed. IWorksheet sheet = workbook.Worksheets[0]; //Data validation to list the values in the first cell IDataValidation validation = sheet.Range["C7"].DataValidation; sheet.Range["B7"].Text = "Select an item from the validation list"; validation.ListOfValues = new string[] { "PDF", "XlsIO", "DocIO" }; validation.PromptBoxText = "Data Validation list"; validation.IsPromptBoxVisible = true; validation.ShowPromptBox = true; sheet.Range["C7"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium; sheet.Range["C7"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; sheet.Range["C7"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; // Data Validation for Numbers IDataValidation validation1 = sheet.Range["C9"].DataValidation; sheet.Range["B9"].Text = "Enter a Number to validate"; validation1.AllowType = ExcelDataType.Integer; validation1.CompareOperator = ExcelDataValidationComparisonOperator.Between; validation1.FirstFormula = "0"; validation1.SecondFormula = "10"; validation1.ShowErrorBox = true; validation1.ErrorBoxText = "Enter Value between 0 to 10"; validation1.ErrorBoxTitle = "ERROR"; validation1.PromptBoxText = "Data Validation using Condition for Numbers"; validation1.ShowPromptBox = true; sheet.Range["C9"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium; sheet.Range["C9"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; sheet.Range["C9"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; // Data Validation for Date IDataValidation validation2 = sheet.Range["C11"].DataValidation; sheet.Range["B11"].Text = "Enter the Date to validate"; validation2.AllowType = ExcelDataType.Date; validation2.CompareOperator = ExcelDataValidationComparisonOperator.Between; validation2.FirstDateTime = new DateTime(2003, 5, 10); validation2.SecondDateTime = new DateTime(2004, 5, 10); validation2.ShowErrorBox = true; validation2.ErrorBoxText = "Enter Value between 5/10/2003 to 5/10/2004"; validation2.ErrorBoxTitle = "ERROR"; validation2.PromptBoxText = "Data Validation using Condition for Date"; validation2.ShowPromptBox = true; sheet.Range["C11"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium; sheet.Range["C11"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; sheet.Range["C11"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; // Data Validation for TextLength IDataValidation validation3 = sheet.Range["C13"].DataValidation; sheet.Range["B13"].Text = "Enter the Text to validate"; validation3.AllowType = ExcelDataType.TextLength; validation3.CompareOperator = ExcelDataValidationComparisonOperator.Between; validation3.FirstFormula = "1"; validation3.SecondFormula = "6"; validation3.ShowErrorBox = true; validation3.ErrorBoxText = "Retype text length to 6 character"; validation3.ErrorBoxTitle = "ERROR"; validation3.PromptBoxText = "Data Validation using Condition for TextLength"; validation3.ShowPromptBox = true; sheet.Range["C13"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium; sheet.Range["C13"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; sheet.Range["C13"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; // Data Validation for Time IDataValidation validation4 = sheet.Range["C15"].DataValidation; sheet.Range["B15"].Text = "Enter the Time to validate"; validation4.AllowType = ExcelDataType.Time; validation4.CompareOperator = ExcelDataValidationComparisonOperator.Between; validation4.FirstFormula = "10"; validation4.SecondFormula = "12"; validation4.ShowErrorBox = true; validation4.ErrorBoxText = "Enter the Correct time between 10 to 12 "; validation4.ErrorBoxTitle = "ERROR"; validation4.PromptBoxText = "Data Validation using Condition for Time"; validation4.ShowPromptBox = true; sheet.Range["C15"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium; sheet.Range["C15"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; sheet.Range["C15"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; sheet.Range["B2:C2"].Merge(); sheet.Range["B2"].Text = "Simple Data validation"; sheet.Range["B5"].Text = "Validation criteria"; sheet.Range["C5"].Text = "Validation"; sheet.Range["B5"].CellStyle.Font.Bold = true; sheet.Range["C5"].CellStyle.Font.Bold = true; sheet.Range["B2"].CellStyle.Font.Bold = true; sheet.Range["B2"].CellStyle.Font.Size = 16; sheet.Range["B2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter; sheet.UsedRange.AutofitColumns(); sheet.UsedRange.AutofitRows(); try { //Saving the workbook to disk. if (SaveOption == "Xls") { return(excelEngine.SaveAsActionResult(workbook, "DataValidation.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97)); } else { return(excelEngine.SaveAsActionResult(workbook, "DataValidation.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016)); } } catch (Exception) { } workbook.Close(); excelEngine.Dispose(); return(View()); }
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); }
public void AddValidationData(IDataValidation dataValidation) { XSSFDataValidation xssfDataValidation = (XSSFDataValidation)dataValidation; CT_DataValidations dataValidations = worksheet.dataValidations; if (dataValidations == null) { dataValidations = worksheet.AddNewDataValidations(); } int currentCount = dataValidations.sizeOfDataValidationArray(); CT_DataValidation newval = dataValidations.AddNewDataValidation(); newval.Set(xssfDataValidation.GetCTDataValidation()); dataValidations.count = (uint)currentCount + 1; }