NPOI excel表格将模板的多个sheet页合成一个sheet页面导出

有一个需求是需要根据四个选项导出表格，本来是可以导成一个excel的多个sheet页面完成，但由于客户需求。所以要求导出最后为一个excel的一个sheet页。
解决思路就是写一个模板，四个sheet页，根据选中项去拼接表头。

 /// <summary>
        ///导出，从模板读sheet页的表头拼接
        /// </summary>
        /// <param name="ds">导出的数据</param>
        /// <param name="arr">类型0基础信息1单位信息2项目任务3项目成果</param>
        /// <returns></returns>
        public  HSSFWorkbook DataSetToExcel(DataSet ds,string arr)
        {
          
            FileStream fs = null;
            string  path = AppDomain.CurrentDomain.BaseDirectory + "\\XXXX导出模板.xls"; //获取模板路径
            fs = new FileStream(path, FileMode.Open, FileAccess.Read);
            HSSFWorkbook workbook = new HSSFWorkbook();
            workbook = new HSSFWorkbook(fs);
            HSSFWorkbook fullbook = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)fullbook.CreateSheet("sheet");
            HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
            HSSFRow header2Row = (HSSFRow)sheet.CreateRow(1);
            int length = 0;
            //填写表头       
            for (int i = 0; i < 4; i++)
            {
                bool exists = arr.Contains(i.ToString());
                if(!exists)
                {
                    continue;
                }
                HSSFSheet sheet1 = (HSSFSheet)workbook.GetSheetAt(i);
                               
                for (int s = 0; s < 2; s++)
                {
                    HSSFRow row = (HSSFRow)sheet1.GetRow(s);   //row读入模板的第1行表头                                         
                    if (row != null)
                    {
                        for (int j = 0; j < row.LastCellNum; j++)  //对工作表每一列  
                        {
                            ICell cell = row.GetCell(j);
                            ICellStyle style = fullbook.CreateCellStyle();//创建新样式
                            style.CloneStyleFrom(cell.CellStyle);//复制原本的样式
                           
                            string cellValue = row.GetCell(j).ToString(); //获取i行j列数据  
                          
                            if (s == 0)
                            {
                                headerRow.CreateCell(length+j).SetCellValue(cellValue);
                                headerRow.GetCell(length+j).CellStyle = style;//给新的工作表的表头赋样式
                            }
                            else
                            {
                                header2Row.CreateCell(length+j).SetCellValue(cellValue);
                                header2Row.GetCell(length+j).CellStyle = style;//给新的工作表的表头赋样式
                            }
                        }
                    }                    
                }
                length = headerRow.LastCellNum;//判断模板从哪列写值 
            }

            //合并单元格
            /**第一个参数：从第几行开始合并;第二个参数：到第几行结束合并
              第三个参数：从第几列开始合并;第四个参数：到第几列结束合并**/
            for (int len =0; len < sheet.GetRow(0).LastCellNum;len++)
            {
                CellRangeAddress region = new CellRangeAddress(0, 1, len, len);
                if (len == 19)
                {
                    region = new CellRangeAddress(0, 0, 19, 22);
                }
                else if(len ==23){
                    region = new CellRangeAddress(0, 0, 23, 26);
                }
                else if (len ==20 || len ==21 ||len== 22 ||len == 24 || len == 25 || len == 26)
                {
                    continue;
                }
                sheet.AddMergedRegion(region);
            }
            //设置第一行行高
            sheet.GetRow(0).HeightInPoints = 55;

            //填写数据
            for (int i = 0; i < ds.Tables.Count; i++)
            {
                HSSFSheet sheet1 = (HSSFSheet)fullbook.GetSheetAt(0);//获取到第一个sheet即合并的模板

                HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();//日期样式
                HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
                dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
                int rowIndex = 2;

                foreach (DataRow row in ds.Tables[i].Rows)
                {
                    HSSFRow dataRow = (HSSFRow)sheet1.CreateRow(rowIndex);
                    #region 填充内容
                    foreach (DataColumn column in ds.Tables[i].Columns)
                    {
                        HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
                        string type = row[column].GetType().FullName.ToString();
                        newCell.SetCellValue(GetValue(row[column].ToString(), type));
                    }
                    #endregion
                    rowIndex++;
                }
            }

            return fullbook;
        }

        private static string GetValue(string cellValue, string type)
        {
            object value = string.Empty;
            switch (type)
            {
                case "System.String"://字符串类型
                    value = cellValue;
                    break;
                case "System.DateTime"://日期类型
                    System.DateTime dateV;
                    System.DateTime.TryParse(cellValue, out dateV);
                    value = dateV;
                    break;
                case "System.Boolean"://布尔型
                    bool boolV = false;
                    bool.TryParse(cellValue, out boolV);
                    value = boolV;
                    break;
                case "System.Int16"://整型
                case "System.Int32":
                case "System.Int64":
                case "System.Byte":
                    int intV = 0;
                    int.TryParse(cellValue, out intV);
                    value = intV;
                    break;
                case "System.Decimal"://浮点型
                case "System.Double":
                    double doubV = 0;
                    double.TryParse(cellValue, out doubV);
                    value = doubV;
                    break;
                case "System.DBNull"://空值处理
                    value = string.Empty;
                    break;
                default:
                    value = string.Empty;
                    break;
            }
            return value.ToString();
        }   

controller内的方法

```csharp

```csharp
 /// <summary>
        /// 已立项项目的导出
        /// </summary>
        public void DataSetToExcel()
        {
            string connectionStr = ConfigHelper.GetConnSettingsValue("SqlServer");
            string filename = "xxxx" + DateTime.Now.ToString("yyyy-MM-ddHHmmss"); //导出文件名
            string arr = Request.Params["type"];//获取导出的类型
            string condition = Request.Params["exportCon"];//查询条件
            string order = Request.Params["order"];//排序

            if (!String.IsNullOrEmpty(condition))
            {
                condition = " AND " +condition;
            }
            if (!String.IsNullOrEmpty(order))
            {
                order = "  order by  " + order;
            }          
            string sqlbase = "" + condition + order;//0
            string sqlunit = "";//1
            string sqltask = " " + condition + order;//2
            string sqlresult = " ";//3

            DataSet ds = SqlHelper.ExecuteDataset(connectionStr, CommandType.Text, sqlbase); 
                      
            if (arr.Contains("1"))
            {
                DataSet ds2 = SqlHelper.ExecuteDataset(connectionStr, CommandType.Text, sqlunit);

                //当没有选中基础信息时，ds就为空，就需要先根据检索条件查询出当前的ProjectID，
                //然后根据ProjectID去找到ds2中所需对应的列填充，没有的话赋空，为拼接ds3数据                               
                ds.Tables[0].Columns.Add("Name1", typeof(string));
                ds.Tables[0].Columns.Add("Name2", typeof(string));
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)   //遍历ds集合
                {
                    string projectID = ds.Tables[0].Rows[i]["ProjectID"].ToString();   //获得该行的项目号
                    DataRow[] dr = ds2.Tables[0].Select("ProjectID='" + projectID + "'");         //ds2数据集合中是否包含有这个ID
                    if (dr.Length.Equals(1))   //包含
                    {
                        int k = ds2.Tables[0].Rows.IndexOf(dr[0]);        //记录下该行数据的行号！                        
                        ds.Tables[0].Rows[i]["Name1"] = ds2.Tables[0].Rows[k]["Name1"].ToString();   //添加列数据
                        ds.Tables[0].Rows[i]["Name2"] = ds2.Tables[0].Rows[k]["Name2"].ToString();
                    }
                    else
                    {
                        ds.Tables[0].Rows[i]["Name1"] = "";
                        ds.Tables[0].Rows[i]["Name2"] = "";
                    }
                }
                
            }

            if (arr.Contains("2"))
            {
                DataSet ds3 = SqlHelper.ExecuteDataset(connectionStr, CommandType.Text, sqltask);
                ds.Tables[0].Columns.Add("Name3", typeof(string));
                ds.Tables[0].Columns.Add("Name4", typeof(string));
                ds.Tables[0].Columns.Add("Name5", typeof(string));
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    string projectID = ds.Tables[0].Rows[i]["ProjectID"].ToString();
                    DataRow[] dr = ds3.Tables[0].Select("ProjectID='" + projectID + "'");
                    if (dr.Length.Equals(1))
                    {
                        int k = ds3.Tables[0].Rows.IndexOf(dr[0]);
                        ds.Tables[0].Rows[i]["Name3"] = ds3.Tables[0].Rows[k]["Name3"].ToString();   //添加列数据
                        ds.Tables[0].Rows[i]["Name4"] = ds3.Tables[0].Rows[k]["Name4"].ToString();
                        ds.Tables[0].Rows[i]["Name5"] = ds3.Tables[0].Rows[k]["Name5"].ToString();
                    }
                }
            }
                       
            HSSFWorkbook book = commonService.DataSetToExcel(ds,arr);

            MemoryStream ms = new MemoryStream();
            book.Write(ms);
            System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8)));
            System.Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray());
            System.Web.HttpContext.Current.Response.End();
            book = null;
            ms.Close();
            ms.Dispose();
        }

        /// <summary>  
        /// 判断DS是否为空  
        /// </summary>  
        /// <param name="ds">需要判断的ds</param>  
        /// <returns>如果ds为空，返回true</returns>  
        private bool JudgeDs(DataSet ds)
        {
            bool Flag = false;
            if ((ds == null) || (ds.Tables.Count == 0) || (ds.Tables.Count == 1 && ds.Tables[0].Rows.Count == 0))
            {
                Flag = true;
            }
            return Flag;
        }