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 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")))); } }
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 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); }
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> /// 设置字段为下拉框 /// </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 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")))); } }
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(); #if !HIDE_UNREACHABLE_CODE 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())); throw new NotImplementedException("The method CreateDocumentInputStream of POIFSFileSystem is not implemented."); //erf.ProcessRecords(fs.CreateDocumentInputStream("Workbook")); } catch (RecordFormatException e) { throw new RuntimeException(e); } catch (IOException e) { throw new RuntimeException(e); } } // else verify record ordering by navigating the raw bytes #endif 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); }
/// <summary> /// 设置列类型 /// </summary> /// <param name="excelGlobalDTO"></param> public void SetSheetColumnType(ExcelGlobalDTO <TEntity> excelGlobalDTO) { //遍历Sheet实体集合 foreach (var item in excelGlobalDTO.Sheets) { ISheet sheet = excelGlobalDTO.Workbook.GetSheetAt(item.SheetIndex); //判断是否为空 if (item.SheetEntityList == null) { continue; } //遍历行 foreach (var entity in item.SheetEntityList) { IRow row = sheet.GetRow(entity.RowNumber); foreach (var head in item.SheetHeadList) { //获取列 ICell cell = row.GetCell(head.ColumnIndex); //设置生成下拉框的行和列 //var cellRegions = new NPOI.SS.Util.CellRangeAddressList((item.StartRowIndex.Value) + 1, sheet.LastRowNum, head.ColumnIndex, head.ColumnIndex); var cellRegions = new NPOI.SS.Util.CellRangeAddressList(entity.RowNumber, entity.RowNumber, head.ColumnIndex, head.ColumnIndex); IDataValidationHelper dvHelper = sheet.GetDataValidationHelper(); IDataValidationConstraint dvConstraint = null; IDataValidation dataValidate = null; switch (head.ColumnType) { //列类型为文本 case Attribute.Enum.ColumnTypeEnum.Text: break; //列类型为日期 case Attribute.Enum.ColumnTypeEnum.Date: head.Format = string.IsNullOrEmpty(head.Format) ? "yyyy-MM-dd" : head.Format; dvConstraint = dvHelper.CreateDateConstraint(OperatorType.BETWEEN, DateTime.MinValue.ToString(head.Format), DateTime.MaxValue.ToString(head.Format), head.Format); dataValidate = dvHelper.CreateValidation(dvConstraint, cellRegions); dataValidate.CreateErrorBox("输入不合法", "必须为日期"); dataValidate.ShowPromptBox = true; break; //列类型为浮点 case Attribute.Enum.ColumnTypeEnum.Decimal: dvConstraint = dvHelper.CreateDecimalConstraint(OperatorType.BETWEEN, "0", "9999999999"); dataValidate = dvHelper.CreateValidation(dvConstraint, cellRegions); dataValidate.CreateErrorBox("输入不合法", "必须在0~9999999999之间。"); dataValidate.ShowPromptBox = true; break; //列类型为选项 case Attribute.Enum.ColumnTypeEnum.Option: List <string> options = null; #region 全局列设置 if (item.ColumnOptions != null && item.ColumnOptions.Count > 0) { string key = null; //如果头部名称存在则取头部名称(以头部名称设置选项) if (item.ColumnOptions.Keys.Contains(head.HeadName) == true) { key = head.HeadName; } //如果属性存在则取头部名称(以属性名称设置选项) if (item.ColumnOptions.Keys.Contains(head.PropertyName) == true) { key = head.PropertyName; } //不为空说明存在,则设置选项 if (key != null) { options = item.ColumnOptions[key]; } } #endregion #region 单个列设置 //行的优先级高于Sheet的优先级 if (entity.ColumnOptions != null && entity.ColumnOptions.Count > 0) { string key = null; //如果头部名称存在则取头部名称(以头部名称设置选项) if (entity.ColumnOptions.Keys.Contains(head.HeadName) == true) { key = head.HeadName; } //如果属性存在则取头部名称(以属性名称设置选项) if (entity.ColumnOptions.Keys.Contains(head.PropertyName) == true) { key = head.PropertyName; } //不为空说明存在,则设置选项 if (key != null) { options = entity.ColumnOptions[key]; } } #endregion //不符合条件则跳出 if (options == null) { continue; } if (options.Count() > 0) { dvConstraint = dvHelper.CreateExplicitListConstraint(options.ToArray()); dataValidate = dvHelper.CreateValidation(dvConstraint, cellRegions); dataValidate.CreateErrorBox("输入不合法", "请选择下拉列表中的值。"); dataValidate.ShowPromptBox = true; } break; } //类型在指定的范围内是才设置校验 if (dataValidate != null) { sheet.AddValidationData(dataValidate); } } } } }
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; }