使用NPOI操作Excel时，给一列所有的单元格加上下拉框的输入限制

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) ) );