예제 #1
0
파일: TestXSSFName.cs 프로젝트: zzy092/npoi
        public void TestSetNameName()
        {
            // Test that renaming named ranges doesn't break our new named range map
            XSSFWorkbook wb = new XSSFWorkbook();

            wb.CreateSheet("First Sheet");
            // Two named ranges called "name1", one scoped to sheet1 and one globally
            XSSFName nameSheet1 = wb.CreateName() as XSSFName;

            nameSheet1.NameName        = "name1";
            nameSheet1.RefersToFormula = "'First Sheet'!$A$1";
            nameSheet1.SheetIndex      = 0;
            XSSFName nameGlobal = wb.CreateName() as XSSFName;

            nameGlobal.NameName        = "name1";
            nameGlobal.RefersToFormula = "'First Sheet'!$B$1";
            // Rename sheet-scoped name to "name2", check everything is updated properly
            // and that the other name is unaffected
            nameSheet1.NameName = "name2";
            Assert.AreEqual(1, wb.GetNames("name1").Count);
            Assert.AreEqual(1, wb.GetNames("name2").Count);
            Assert.AreEqual(nameGlobal, wb.GetName("name1"));
            Assert.AreEqual(nameSheet1, wb.GetName("name2"));
            // Rename the other name to "name" and check everything again
            nameGlobal.NameName = "name2";
            Assert.AreEqual(0, wb.GetNames("name1").Count);
            Assert.AreEqual(2, wb.GetNames("name2").Count);
            Assert.IsTrue(wb.GetNames("name2").Contains(nameGlobal));
            Assert.IsTrue(wb.GetNames("name2").Contains(nameSheet1));
            wb.Close();
        }
예제 #2
0
        public void TestRemoveSheet()
        {
            // Test removing a sheet maintains the named ranges correctly
            XSSFWorkbook wb = new XSSFWorkbook();

            wb.CreateSheet("Sheet1");
            wb.CreateSheet("Sheet2");
            XSSFName sheet1Name = wb.CreateName() as XSSFName;

            sheet1Name.NameName        = "name1";
            sheet1Name.SheetIndex      = 0;
            sheet1Name.RefersToFormula = "Sheet1!$A$1";
            XSSFName sheet2Name = wb.CreateName() as XSSFName;

            sheet2Name.NameName        = "name1";
            sheet2Name.SheetIndex      = 1;
            sheet2Name.RefersToFormula = "Sheet2!$A$1";
            Assert.IsTrue(wb.GetAllNames().Contains(sheet1Name));
            Assert.IsTrue(wb.GetAllNames().Contains(sheet2Name));
            Assert.AreEqual(2, wb.GetNames("name1").Count);
            Assert.AreEqual(sheet1Name, wb.GetNames("name1")[0]);
            Assert.AreEqual(sheet2Name, wb.GetNames("name1")[1]);
            // Remove sheet1, we should only have sheet2Name now
            wb.RemoveSheetAt(0);
            Assert.IsFalse(wb.GetAllNames().Contains(sheet1Name));
            Assert.IsTrue(wb.GetAllNames().Contains(sheet2Name));
            Assert.AreEqual(1, wb.GetNames("name1").Count);
            Assert.AreEqual(sheet2Name, wb.GetNames("name1")[0]);
            // Check by index as well for sanity
            Assert.AreEqual(1, wb.NumberOfNames);
            Assert.AreEqual(0, wb.GetNameIndex("name1"));
            Assert.AreEqual(sheet2Name, wb.GetNameAt(0));
            wb.Close();
        }
예제 #3
0
        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();
                }
            }
        }
예제 #4
0
        CallingConstructorWithExistingWorkbookShouldInstanstiateNewXssfWorkbookDefaultRowAccessWindowSizeCompressTempFilesAsFalseAndUseSharedStringsTableFalse
            ()
        {
            var wb   = new XSSFWorkbook();
            var name = wb.CreateName();

            name.NameName = "test";
            var sheet = wb.CreateSheet("test1");

            _objectToTest = new SXSSFWorkbook(wb);
            Assert.AreEqual(100, _objectToTest.RandomAccessWindowSize);
            Assert.AreEqual("test", _objectToTest.XssfWorkbook.GetName("test").NameName);
            Assert.AreEqual(1, _objectToTest.NumberOfSheets);
        }
예제 #5
0
 public IName CreateName()
 {
     return(XssfWorkbook.CreateName());
 }
예제 #6
0
        //public NpoiMemoryStream GetFileTemplate(string fileName,string[] heads,params CellValidation[] cellValidations)
        //{
        //    var memory = new NpoiMemoryStream();
        //    var workbook = new XSSFWorkbook();
        //    var sheet = workbook.CreateSheet(fileName);
        //    var row = sheet.CreateRow(0);
        //    for (var i = 0; i < heads.Length; i++)
        //    {
        //        row.CreateCell(i).SetCellValue(heads[i]);
        //    }

        //    var sheet1 = workbook.GetSheetAt(0);

        //    cellValidations.ForEach(item =>
        //    {
        //        var regions = new CellRangeAddressList(item.FirstRow,item.LastRow,item.FirstCol, item.LastCol);//约束范围:c2到c65535
        //        var helper = new XSSFDataValidationHelper((XSSFSheet)sheet1);//获得一个数据验证Helper
        //        var validation =
        //            helper.CreateValidation(helper.CreateExplicitListConstraint(item.ListOfValues),
        //                regions);//创建约束
        //        validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!");
        //        validation.ShowErrorBox = true;//显示上面提示=Ture
        //        sheet1.AddValidationData(validation);//添加进去
        //    });

        //    sheet1.ForceFormulaRecalculation = true;

        //    memory.AllowClose = false;
        //    workbook.Write(memory);
        //    memory.Flush();
        //    memory.Position = 0;    // 指定内存流起始值

        //    return memory;
        //}

        public NpoiMemoryStream GetFileTemplate(string fileName, string[] heads, params CellValidation[] cellValidations)
        {
            var workbook = new XSSFWorkbook();

            var sheet = workbook.CreateSheet(fileName);
            var style = workbook.CreateCellStyle();

            style.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");

            #region set heads

            var newRow = sheet.CreateRow(0);
            for (var i = 0; i < heads.Length; i++)
            {
                var cell = newRow.CreateCell(i);
                cell.CellStyle = style;
                cell.SetCellValue(heads[i]);
                for (int j = 1; j <= 65535; j++)
                {
                    var row = sheet.GetRow(j) ?? sheet.CreateRow(j);
                    cell           = row.CreateCell(i);
                    cell.CellStyle = style;
                }
            }

            #endregion set heads

            #region set datasource

            var dataNum = 0;
            cellValidations?.ToList().ForEach(item =>
            {
                if (item.FirstList == null)
                {
                    return;
                }

                var dataSourceSheetName = $"dataSource{dataNum}";
                dataNum++;
                var dataSourceSheet = workbook.CreateSheet(dataSourceSheetName);//创建sheet

                for (var i = 0; i < item.FirstList.Count; i++)
                {
                    var row = dataSourceSheet.GetRow(i) ?? dataSourceSheet.CreateRow(i); //添加行

                    row.CreateCell(0).SetCellValue(item.FirstList[i].Value);             //单元格写值

                    if (i > 0)
                    {
                        if (item.SecondList != null && item.SecondList.Any())
                        {
                            dataSourceSheet.GetRow(0).CreateCell(i).SetCellValue(item.FirstList[i].Value);//一级列头

                            var datas = item.SecondList.Where(c => c.Key == item.FirstList[i].Key).ToList();

                            for (var j = 0; j < datas.Count; j++)
                            {
                                var secondRow = dataSourceSheet.GetRow(j + 1) ?? dataSourceSheet.CreateRow(j + 1);
                                secondRow.CreateCell(i).SetCellValue(datas[j].Value);//单元格写值
                            }
                        }
                    }
                }

                #region Range

                for (var i = 0; i < item.FirstList.Count; i++)
                {
                    if (i == 0)
                    {
                        var range             = workbook.CreateName();
                        range.NameName        = item.FirstList[i].Value;
                        range.RefersToFormula = string.Format("{0}!${3}${2}:${3}${1}",
                                                              dataSourceSheetName,
                                                              item.FirstList.Count,
                                                              2,
                                                              Index2ColName(i));
                    }
                    else
                    {
                        if (item.SecondList != null && item.SecondList.Any())
                        {
                            var cellDatas = item.SecondList.Where(c => c.Key == item.FirstList[i].Key).ToList();
                            if (cellDatas.Any())
                            {
                                var range             = workbook.CreateName();
                                range.NameName        = item.FirstList[i].Value;
                                range.RefersToFormula = string.Format("{0}!${3}${2}:${3}${1}",
                                                                      dataSourceSheetName,
                                                                      cellDatas.Count + 1,
                                                                      2,
                                                                      Index2ColName(i));
                            }
                        }
                    }
                }

                #endregion Range
            });

            #endregion set datasource

            cellValidations?.ToList().ForEach(item =>
            {
                if (item.FirstList != null && item.FirstList.Any())
                {
                    var regions = new CellRangeAddressList(item.FirstRow, item.LastRow, item.FirstCol, item.LastCol); //约束范围:c2到c65535

                    var helper     = new XSSFDataValidationHelper((XSSFSheet)sheet);                                  //获得一个数据验证Helper
                    var validation =
                        helper.CreateValidation(
                            helper.CreateFormulaListConstraint(item.FirstList[0].Value),
                            regions);                    //创建约束
                    validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!");
                    validation.ShowErrorBox = true;      //显示上面提示
                    sheet.AddValidationData(validation); //添加进去
                }
                else
                {
                    if (item.ListOfValues != null && item.ListOfValues.Any())
                    {
                        var regions    = new CellRangeAddressList(item.FirstRow, item.LastRow, item.FirstCol, item.LastCol); //约束范围:c2到c65535
                        var helper     = new XSSFDataValidationHelper((XSSFSheet)sheet);                                     //获得一个数据验证Helper
                        var validation =
                            helper.CreateValidation(helper.CreateExplicitListConstraint(item.ListOfValues),
                                                    regions); //创建约束
                        validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!");
                        validation.ShowErrorBox = true;       //显示上面提示
                        sheet.AddValidationData(validation);  //添加进去
                    }
                }
            });

            cellValidations?.ToList().ForEach(item =>
            {
                if (item.SecondList != null && item.SecondList.Any())
                {
                    var regions = new CellRangeAddressList(item.FirstRow, item.LastRow, item.FirstCol + 1, item.LastCol + 1); //约束范围:c2到c65535

                    var helper     = new XSSFDataValidationHelper((XSSFSheet)sheet);                                          //获得一个数据验证Helper
                    var validation =
                        helper.CreateValidation(
                            helper.CreateFormulaListConstraint($"INDIRECT(${Index2ColName(item.FirstCol)}2)"),
                            regions);                    //创建约束
                    validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!");
                    validation.ShowErrorBox = true;      //显示上面提示=Ture
                    sheet.AddValidationData(validation); //添加进去
                }
            });

            sheet.ForceFormulaRecalculation = true;

            var memory = new NpoiMemoryStream();
            memory.AllowClose = false;

            workbook.Write(memory);
            memory.Flush();
            memory.Position = 0;    // 指定内存流起始值
            return(memory);
        }
예제 #7
0
        /// <summary>
        /// 应用规则
        /// </summary>
        private void ApplyRule()
        {
            //单独存放一个工作簿存放所有的数据
            var sheetName = $"ref_rule";
            var sheetRef  = _workbook.CreateSheet(sheetName);//名为ref的工作表

            //循环 i 作为数据存储的列
            // 第一组数据 存储在第一列
            // 第二组数据 存储在第二列
            // 以此类推
            for (int i = 0; i < _rules.Count; i++)
            {
                var rule = _rules[i];

                var options = rule.Source;

                if (options.Count == 0)
                {
                    continue;
                }

                //在当前列 保存所有数据, 所有数据是存储在行上
                for (var j = 0; j < options.Count; j++)
                {
                    var row = sheetRef.GetRow(j) ?? sheetRef.CreateRow(j);
                    row.CreateCell(i).SetCellValue(options[j]);
                }

                //在Excel工作簿中增加此数据范围
                IName range = _workbook.CreateName();//创建一个命名公式

                //获取当前列的字符串列信息,如 1=>A , 2=>B
                var colChar = PowerExcelConfigCell.ToColumnChar(i + 1);

                //公式范围格式应为:A1-A50  B1:B25
                range.RefersToFormula = $"{sheetName}!${colChar}$1:${colChar}${options.Count}"; //公式内容,就是上面的区域
                range.NameName        = $"rule_{i}";                                            //公式名称,可以在"公式"-->"名称管理器"中看到

                //规则也保存该名称,留着后面使用
                rule.RuleName = range.NameName;

                //if (rule.RowField != null && _dtList.Rows.Count > 0)
                //应用到行级字段
                if (rule.RowField != null)
                {
                    foreach (var field in rule.RowField)
                    {
                        SetField2Select(range.NameName, field);
                    }
                }
                //应用到填充字段
                if (rule.FillField != null)
                {
                    foreach (var field in rule.FillField)
                    {
                        SetField2Select(range.NameName, field);
                    }
                }
            }

            var index = _workbook.GetSheetIndex(sheetRef);

            _workbook.SetSheetHidden(index, _config.Prop.Debug ? false : true);
        }
예제 #8
0
        /// <summary>
        /// 下载excel模板
        /// </summary>
        /// <param name="fileName">文件名称</param>
        /// <param name="heads">表头</param>
        /// <param name="contentInfo">导出excel数据源</param>
        /// <param name="cellContents"></param>
        /// <param name="templteRowCount">模板或者导出excel样式设置行数</param>
        /// <param name="cellValidations">验证数据源</param>
        /// <returns></returns>
        public static NpoiMemoryStream GetFileTemplate(string fileName
                                                       , List <HeadInfo> heads
                                                       , List <List <ContentInfo> > contentInfo = null
                                                       , List <CellContent> cellContents        = null
                                                       , int templteRowCount = 65535
                                                       , params CellValidation[] cellValidations)
        {
            #region 内容特殊字符过滤  待处理
            for (var i = 0; i < cellValidations?.Length; i++)
            {
                if (cellValidations[i].FirstList == null || !cellValidations[i].FirstList.Any())
                {
                    continue;
                }

                cellValidations[i].FirstList[0].Key   = ReplaceSpecialCharacter(cellValidations[i].FirstList[0].Key);
                cellValidations[i].FirstList[0].Value = ReplaceSpecialCharacter(cellValidations[i].FirstList[0].Value);
                //foreach (var item in cellValidations[i].FirstList)
                //{
                //    item.Key =ReplaceSpecialCharacter(item.Key);
                //    item.Value =ReplaceSpecialCharacter(item.Value);
                //}
            }
            #endregion

            var workbook = new XSSFWorkbook();

            var sheet = workbook.CreateSheet(fileName);

            #region set heads

            var rowCount = contentInfo?.Count ?? templteRowCount;
            var newRow   = sheet.CreateRow(0);
            for (var i = 0; i < heads.Count; i++)
            {
                sheet.SetColumnWidth(i, heads[i].ColumnWidth);
                var style = GetCellStyle(workbook, heads[i].CellStyle);
                var cell  = newRow.CreateCell(i);
                cell.CellStyle = style;
                cell.SetCellValue(heads[i].Name);
                for (var j = 1; j <= rowCount; j++)
                {
                    var row = sheet.GetRow(j) ?? sheet.CreateRow(j);
                    cell           = row.CreateCell(i);
                    cell.CellStyle = style;
                }
            }
            #endregion

            #region 初始化导出excel数据源
            for (var i = 0; i < contentInfo?.Count; i++)
            {
                var contentRow = sheet.CreateRow(i + 1);
                for (var j = 0; j < contentInfo[i]?.Count; j++)
                {
                    var contentCell = contentRow.CreateCell(j);
                    contentCell.SetCellValue(contentInfo[i][j].Name);
                }
            }
            #endregion

            #region set datasource
            cellValidations?.ToList().ForEach(item =>
            {
                if (item.FirstList == null)
                {
                    return;
                }

                var dataSourceSheetName = $"{item.FirstList?[0]?.Value.Replace(" ", "")}";
                var dataSourceSheet     = workbook.CreateSheet(dataSourceSheetName);//创建sheet

                /*
                 * FirstList
                 * 1 2 3
                 * 2
                 * 3
                 */


                #region init datasource
                for (var i = 0; i < item.FirstList.Count; i++)
                {
                    var row = dataSourceSheet.GetRow(i) ?? dataSourceSheet.CreateRow(i);//添加行

                    row.CreateCell(0).SetCellValue(i == 0
                        ? item.FirstList[i].Value.Replace(" ", "")
                        : item.FirstList[i].Value);//单元格写值

                    if (i <= 0)
                    {
                        continue;
                    }


                    if (item.SecondList != null && item.SecondList.Any())
                    {
                        dataSourceSheet.GetRow(0).CreateCell(i).SetCellValue(item.FirstList[i].Value);//一级列头

                        var cellDatas = item.SecondList.Where(c => c.Key == item.FirstList[i].Key).ToList();

                        for (var j = 0; j < cellDatas.Count; j++)
                        {
                            var secondRow = dataSourceSheet.GetRow(j + 1) ?? dataSourceSheet.CreateRow(j + 1);
                            secondRow.CreateCell(i).SetCellValue(cellDatas[j].Value);//单元格写值
                        }
                    }
                }
                #endregion

                #region Range
                for (var i = 0; i < item.FirstList.Count; i++)
                {
                    if (i == 0)
                    {
                        var range             = workbook.CreateName();
                        range.NameName        = item.FirstList[i].Value.Replace(" ", "");
                        range.RefersToFormula = string.Format("{0}!${3}${2}:${3}${1}",
                                                              dataSourceSheetName,
                                                              item.FirstList.Count,
                                                              2,
                                                              Index2ColName(i));
                    }
                    else
                    {
                        if (item.SecondList != null && item.SecondList.Any())
                        {
                            var cellDatas = item.SecondList.Where(c => c.Key == item.FirstList[i].Key).ToList();
                            if (cellDatas.Any())
                            {
                                var range             = workbook.CreateName();
                                range.NameName        = item.FirstList[i].Value.Replace(" ", "");
                                range.RefersToFormula = string.Format("{0}!${3}${2}:${3}${1}",
                                                                      dataSourceSheetName,
                                                                      cellDatas.Count + 1,
                                                                      2,
                                                                      Index2ColName(i));
                            }
                        }
                    }
                }
                #endregion
            });
            #endregion

            #region set cellContents
            cellContents?.ForEach(item =>
            {
                var writeSheet = workbook.GetSheetAt(item.SheetIndex) ??
                                 workbook.CreateSheet(item.SheetIndex.ToString());
                var i = 0;
                item.ListOfValues?.ForEach(info =>
                {
                    var writeRow  = writeSheet.GetRow(item.FirstRow + i) ?? writeSheet.CreateRow(item.FirstRow + i);
                    var writeCell = writeRow.GetCell(item.FirstCol) ?? writeRow.CreateCell(item.FirstCol);
                    writeCell.SetCellValue(info);
                    i++;
                });
            });
            #endregion

            #region 设置验证
            cellValidations?.ToList().ForEach(item =>
            {
                if (item.FirstList != null && item.FirstList.Any())
                {
                    if (item.FirstCellLocation != null)
                    {
                        var regions = new CellRangeAddressList(item.FirstCellLocation.FirstRow, item.FirstCellLocation.LastRow, item.FirstCellLocation.FirstCol, item.FirstCellLocation.LastCol); //约束范围:c2到c65535

                        var helper     = new XSSFDataValidationHelper((XSSFSheet)sheet);                                                                                                          //获得一个数据验证Helper
                        var validation =
                            helper.CreateValidation(
                                helper.CreateFormulaListConstraint(item.FirstList[0].Value),
                                regions);                    //创建约束
                        validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!");
                        validation.ShowErrorBox = true;      //显示上面提示
                        sheet.AddValidationData(validation); //添加进去
                    }
                }
                else
                {
                    if (item.ListOfValues != null && item.ListOfValues.Any())
                    {
                        if (item.FirstCellLocation != null)
                        {
                            var regions    = new CellRangeAddressList(item.FirstCellLocation.FirstRow, item.FirstCellLocation.LastRow, item.FirstCellLocation.FirstCol, item.FirstCellLocation.LastCol); //约束范围:c2到c65535
                            var helper     = new XSSFDataValidationHelper((XSSFSheet)sheet);                                                                                                             //获得一个数据验证Helper
                            var validation =
                                helper.CreateValidation(helper.CreateExplicitListConstraint(item.ListOfValues),
                                                        regions); //创建约束
                            validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!");
                            validation.ShowErrorBox = true;       //显示上面提示
                            sheet.AddValidationData(validation);  //添加进去
                        }
                    }
                }
            });
            #endregion

            #region 设置验证
            cellValidations?.ToList().ForEach(item =>
            {
                if (item.SecondList != null && item.SecondList.Any())
                {
                    if (item.SecondCellLocation != null)
                    {
                        var regions = new CellRangeAddressList(item.SecondCellLocation.FirstRow, item.SecondCellLocation.LastRow, item.SecondCellLocation.FirstCol, item.SecondCellLocation.LastCol); //约束范围:c2到c65535

                        var helper     = new XSSFDataValidationHelper((XSSFSheet)sheet);                                                                                                              //获得一个数据验证Helper
                        var validation =
                            helper.CreateValidation(
                                helper.CreateFormulaListConstraint($"INDIRECT(${Index2ColName(item.FirstCellLocation.FirstCol)}2)"),
                                regions);                    //创建约束
                        validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!");
                        validation.ShowErrorBox = true;      //显示上面提示=Ture
                        sheet.AddValidationData(validation); //添加进去
                    }
                    else if (item.FirstCellLocation != null)
                    {
                        var regions = new CellRangeAddressList(item.FirstCellLocation.FirstRow, item.FirstCellLocation.LastRow, item.FirstCellLocation.FirstCol + 1, item.FirstCellLocation.LastCol + 1); //约束范围:c2到c65535

                        var helper     = new XSSFDataValidationHelper((XSSFSheet)sheet);                                                                                                                  //获得一个数据验证Helper
                        var validation =
                            helper.CreateValidation(
                                helper.CreateFormulaListConstraint($"INDIRECT(${Index2ColName(item.FirstCellLocation.FirstCol)}2)"),
                                regions);                    //创建约束
                        validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!");
                        validation.ShowErrorBox = true;      //显示上面提示=Ture
                        sheet.AddValidationData(validation); //添加进去
                    }
                }
            });
            #endregion

            sheet.ForceFormulaRecalculation = true;

            var memory = new NpoiMemoryStream {
                AllowClose = false
            };

            workbook.Write(memory);
            memory.Flush();
            memory.Position = 0;    // 指定内存流起始值
            return(memory);
        }