有一个需求是需要根据四个选项导出表格，本来是可以导成一个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));}#endregionrowIndex++;}}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;//0string sqlunit = "";//1string sqltask = " " + condition + order;//2string sqlresult = " ";//3DataSet 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数据集合中是否包含有这个IDif (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;}