C# NPOI生成Excel模板,字段下拉项校验，下拉项取某个范围中的值

 

 　　　　　　 //创建Excel文件的对象(调用NPOI文件)
            XSSFWorkbook workbook = new XSSFWorkbook();
            //设置标题样式
            ICellStyle style = workbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;
            style.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");//单元格格式设置为文本
            //设置普通内容样式
            ICellStyle style1 = workbook.CreateCellStyle();
            style1.Alignment = HorizontalAlignment.Left;
            style1.VerticalAlignment = VerticalAlignment.Center;
            style1.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");//单元格格式设置为文本

            //创建Excel工作表
            var sheet1 = (XSSFSheet)workbook.CreateSheet("Template");
            //字段的Excelz字段取值范围
            var fieldRanges = AddFieldItemsSheel(workbook, style1, titleFileds, fieldItms);
            IRow row1 = sheet1.CreateRow(0);//创建标题行
            int col = 0;//列
            foreach (var field in titleFileds)//循环字段
            {
                sheet1.SetDefaultColumnStyle(col, style1);//设置列样式
                sheet1.SetColumnWidth(col, 20 * 256);//设置宽度
                row1.CreateCell(col).CellStyle = style;//设置标题行的第col列样式
                row1.GetCell(col).SetCellValue(field.Value);//设置标题行的第col列内容

                if (fieldItms != null & isValidation)//isValidation是否开启字段下拉项校验,fieldItms字段校验项
                {
                    //判断字段是否是选项
                    if (fieldItms.ContainsKey(field.Key))//判断当前字段是否有下拉项
                    {
                        var cellRegions = new CellRangeAddressList(1, 65535, col, col);//设置区域,第2~65535行,col~col列
                        XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet)sheet1);
                        //创建约束
                        var dropDownConstraint = helper.CreateFormulaListConstraint(fieldRanges[field.Key]);
                        //创建验证规则
                        IDataValidation dropDownValidation = helper.CreateValidation(dropDownConstraint, cellRegions);
                        //设置约束提示信息
                        dropDownValidation.CreateErrorBox("输入不合法", "请输入下拉列表中的值。");
                        dropDownValidation.ShowErrorBox = true;
                        sheet1.AddValidationData(dropDownValidation);
                    }
                }
                col++;
            }

AddFieldItemsSheel()创建第二页用于存放模板页字段的下拉项值

　　　　　　　Dictionary<string, string> dic = new Dictionary<string, string>();
            if (fieldItms != null)
            {
                ISheet sheet = workbook.CreateSheet("FieldItems");//创建Excel第二页
                var col = 0;
                foreach (var item in fieldItms)//字段下拉项
                {
                    for (var i = 0; i <= item.Value.Length; i++)//因为要留出标题行，所以这里是<=
                    {
                        var row = sheet.GetRow(i);
                        if (row == null)
                        {
                            row = sheet.CreateRow(i);
                        }
                        var cell = row.CreateCell(col);
                        if (i == 0) //第一行为标题
                        {
                            cell.CellStyle = style;//标题样式
                            cell.SetCellValue(titleFileds[item.Key]);//标题
                        }
                        else
                        {
                            cell.SetCellValue(item.Value[i - 1]);//下拉项值
                        }
                    }
                    col++;
                    //取Excle列  1=A  2=b  27=AA
                    var dividend = col;
                    string cName = string.Empty;
                    while (dividend > 0)
                    {
                        var modulo = (dividend-1) % 26;
                        cName = Convert.ToChar(65 + modulo) + cName;
                        dividend = (dividend - modulo) / 26;
                    }
                    //生成下拉项取值范围：$FieldItems!$A$2:$A$10
                    //范围应该是$FieldItems!A2:A10，因为Excel会根据规律改值，第二行就会变成A3:A11,所以要加上$符号，定死
                    dic.Add(item.Key, $"FieldItems!${cName}$2:${cName }${item.Value.Length + 1}");
                }
            }
            return dic;

生成的Excel是.xlsx文件

第二页是选项