使用举例:public static HSSFDataValidation CreateListConstaint(this HSSFWorkbook book, Int32 columnIndex, IEnumerable<String> values) { var sheetName = "_constraintSheet_"; HSSFSheet sheet = book.GetSheet(sheetName) ?? book.CreateSheet(sheetName); var firstRow = sheet.GetRow(0); var conColumnIndex = firstRow == null ? 0 : firstRow.PhysicalNumberOfCells; var rowIndex = 0; var lastValue = ""; foreach (var value in values) { var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex); row.CreateCell(conColumnIndex).SetCellValue(value); rowIndex++; lastValue = value; } //如果只有一个可选值的话,则增加一个相同的选项,不然在Excel中会有问题,可能是NPOI的BUG if(values.Count() ==1) { var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex); row.CreateCell(conColumnIndex).SetCellValue(lastValue); rowIndex++; } HSSFName range = book.CreateName(); range.Reference = String.Format("{2}!${0}$1:${0}${1}", (Char)('A' + conColumnIndex), rowIndex.ToString(),sheetName); string rangeName = "dicRange" + columnIndex; range.NameName = rangeName; var cellRegions = new CellRangeAddressList(1, 65535, columnIndex, columnIndex); var constraint = DVConstraint.CreateFormulaListConstraint(rangeName); book.SetSheetHidden(book.GetSheetIndex(sheet), true); return new HSSFDataValidation(cellRegions,constraint); }
IEnumerable<MyObj> channels = DataMaintenanceDataContext.LoadAll<MyObj>(); sheet.AddValidationData( book.CreateListConstaint(1, Array.ConvertAll(o.ToArray(),o=>o.Name) ) );
使用NPOI操作Excel时,给一列所有的单元格加上下拉框的输入限制
最新推荐文章于 2024-07-14 08:00:00 发布