Example #1
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);
        }
Example #2
0
        public static 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

            #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
            });
            #endregion


            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);  //添加进去
                        }
                    }
                }
            });

            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); //添加进去
                        }
                    }
                }
            });

            sheet.ForceFormulaRecalculation = true;

            var memory = new NpoiMemoryStream {
                AllowClose = false
            };

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