NPOI给模板绑定下拉项

1、.xlsx后缀模板

使用NPOI版本【v 2.7.0】

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;

/// <summary>
        /// 模板初始化
        /// </summary>
        public void TemplateInit()
        {
            string filePath = System.Web.HttpContext.Current.Request.MapPath("~/Template/设备编码.xlsx");
            string outputPath = System.Web.HttpContext.Current.Request.MapPath("~/Template/设备编码1.xlsx");

            FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read);

            XSSFWorkbook workbook = new XSSFWorkbook(fileStream);
            ISheet sheet = workbook.GetSheetAt(0);

            String[] pmsTypeArr = DictDataHelper.GetDictArrayByType(1);
            String[] collectionTypeArr = DictDataHelper.GetDictArrayByType(2);

            // 创建新的Excel工作簿
            //IWorkbook workbook = new XSSFWorkbook();
            //ISheet sheet = workbook.CreateSheet("Sheet1");

            //String[] listOfValues = new String[] { "Item1", "Item2", "Item3" };


            //设置数据源的值
            IDataValidationConstraint dvConstraint1 = sheet.GetDataValidationHelper().CreateExplicitListConstraint(pmsTypeArr);
            //设置编辑的区域
            IDataValidation dataValidation1 = sheet.GetDataValidationHelper().CreateValidation(dvConstraint1, new CellRangeAddressList(2, 6000, 2, 2));

            //设置数据源的值
            IDataValidationConstraint dvConstraint2 = sheet.GetDataValidationHelper().CreateExplicitListConstraint(collectionTypeArr);
            //设置编辑的区域
            IDataValidation dataValidation2 = sheet.GetDataValidationHelper().CreateValidation(dvConstraint2, new CellRangeAddressList(2, 6000, 8, 8));


            sheet.AddValidationData(dataValidation1);
            sheet.AddValidationData(dataValidation2);

            // 写入文件
            using (FileStream stream = new FileStream(outputPath, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(stream);
            }
        }

 

2、.xls后缀模板

 public static void SetCellDropdownList(HSSFWorkbook workbook, ISheet sheet, string name, int firstcol, int lastcol, string[] vals, int sheetindex = 1)
        {
            //先创建一个Sheet专门用于存储下拉项的值
            ISheet sheet2 = workbook.CreateSheet(name);
            //隐藏
            workbook.SetSheetHidden(sheetindex, true);
            int index = 0;
            foreach (var item in vals)
            {
                sheet2.CreateRow(index).CreateCell(0).SetCellValue(item);
                index++;
            }
            //创建的下拉项的区域：
            var rangeName = name + "Range";
            IName range = workbook.CreateName();
            range.RefersToFormula = name + "!$A$1:$A$" + index;
            range.NameName = rangeName;
            CellRangeAddressList regions = new CellRangeAddressList(0, 65535, firstcol, lastcol);

            DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(rangeName);
            HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
            dataValidate.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。");
            dataValidate.ShowPromptBox = true;
            sheet.AddValidationData(dataValidate);
        }

调用

HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("sheet1");
var roomTypeList = GetRoomTypeNameList();
ExcelHelper.SetCellDropdownList(workbook, sheet, "RoomTypeDictionary", 1, 1, roomTypeList.ToArray());