本文目录：
（一）背景
（二）数据库数据导入到Excel的方法比较
（三）SSIS的简介 （四）数据库中存储过程示例（SSIS应用需要）（五）Excel模板的制作（这步这么简单，稍微介绍一下）
（六）SSIS操作过程（生成Package，用来调用）（下一篇随笔将详细讲解制作Package包的过程，图片太多，篇幅过长，因此本文将直接采用生成的Package包进行应用）（七）C#中如何调用SSIS创建的Package和Excel模板（可以自己编写逻辑代码进行重复利用），用来生成Excel数据
（八）总结
（一）背景
如何将数据库中的数据导入到EXCEL文件中，我们经常会碰到。本文将比较常用的几种方法，并且将详细讲解基于SSIS的用法。笔者认为，基于SSIS的方法，对于海量数据来说，应该是效率最好的一种方法。个人认为，这是一种值得推荐的方法，因此，本人决定将本人所知道的、以及自己总结的完整的写出来，一是提高一下自己的写作以及表达能力，二是让更多的读者能够在具体的应用中如何解决将海量数据导入到Excel中的效率问题。
（二）方法的比较
方案一：SSIS（SQL Server数据集成服务），追求效率，Package制作过程复杂一点（容易出错）。
方案二：采用COM.Excel组件。一般，对于操作能够基本满足，但对于数据量大时可能会慢点。下面的代码，本人稍微修改了下，如下所示：该方法主要是对单元格一个一个的循环写入，基本方法为 excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat)。当数据量大时，肯定效率还是有影响的。
public string DataExcels(System.Data.DataTable[] dts, string strTitle, string FilePath, Hashtable nameList, string [] titles) { COM.Excel.cExcelFile excel = new COM.Excel.cExcelFile(); // 当文件大于10的时候 清空所有文件！！！ ClearFile(FilePath); // 文件名 string filename = strTitle + DateTime.Now.ToString( " yyyyMMddHHmmssff " ) + " .xls " ; // 生成相应的文件 excel.CreateFile(FilePath + filename)； // 设置margin COM.Excel.cExcelFile.MarginTypes mt1 = COM.Excel.cExcelFile.MarginTypes.xlsTopMargin; COM.Excel.cExcelFile.MarginTypes mt2 = COM.Excel.cExcelFile.MarginTypes.xlsLeftMargin; COM.Excel.cExcelFile.MarginTypes mt3 = COM.Excel.cExcelFile.MarginTypes.xlsRightMargin; COM.Excel.cExcelFile.MarginTypes mt4 = COM.Excel.cExcelFile.MarginTypes.xlsBottomMargin; double height = 2.2 ; excel.SetMargin( ref mt1, ref height); excel.SetMargin( ref mt2, ref height); excel.SetMargin( ref mt3, ref height); excel.SetMargin( ref mt4, ref height); // 设置字体！！ COM.Excel.cExcelFile.FontFormatting ff = COM.Excel.cExcelFile.FontFormatting.xlsNoFormat; string font = " 宋体 " ; short fontsize = 14 ; excel.SetFont( ref font, ref fontsize, ref ff); byte b1 = 1 , b2 = 12 ; short s3 = 12 ; excel.SetColumnWidth( ref b1, ref b2, ref s3); string header = " 页眉 " ; string footer = " 页脚 " ; excel.SetHeader( ref header); excel.SetFooter( ref footer); COM.Excel.cExcelFile.ValueTypes vt = COM.Excel.cExcelFile.ValueTypes.xlsText; COM.Excel.cExcelFile.CellFont cf = COM.Excel.cExcelFile.CellFont.xlsFont0; COM.Excel.cExcelFile.CellAlignment ca = COM.Excel.cExcelFile.CellAlignment.xlsCentreAlign; COM.Excel.cExcelFile.CellHiddenLocked chl = COM.Excel.cExcelFile.CellHiddenLocked.xlsNormal; // 报表标题 int cellformat = 1 ； int rowIndex = 1 ; // 起始行 int colIndex = 0 ; foreach (System.Data.DataTable dt in dts) { colIndex = 0 ; // 取得列标题 foreach (DataColumn colhead in dt.Columns) { colIndex ++ ; string name = colhead.ColumnName.Trim(); object namestr = ( object )name; excel.WriteValue( ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref namestr, ref cellformat); } // 取得表格中的数据 foreach (DataRow row in dt.Rows) { rowIndex ++ ; colIndex = 0 ; foreach (DataColumn col in dt.Columns) { colIndex ++ ; if (col.DataType == System.Type.GetType( " System.DateTime " )) { object str = ( object )(Convert.ToDateTime(row[col.ColumnName].ToString())).ToString( " yyyy-MM-dd " ); ; excel.WriteValue( ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat); } else { object str = ( object )row[col.ColumnName].ToString(); excel.WriteValue( ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat); } } } rowIndex += 3 ; } int ret = excel.CloseFile(); return FilePath + filename; }
方案三：采用Excel组件。一般，对于操作能够基本满足，但对于数据量大时可能会慢点。下面的代码，本人在原有基础上稍微修改了下，如下所示：
1 public string OutputExceles( string strTitle, string FilePath, string typeName, System.Data.DataTable[] dtList, string [] smallTitleList) 2 { 3 beforeTime = DateTime.Now; 4 Excel.Application excel; 5 Excel._Workbook xBk; 6 Excel._Worksheet xSt; 7 int rowIndex = 1 ; 8 int colIndex = 1 ; 9 excel = new Excel.ApplicationClass(); 10 xBk = excel.Workbooks.Add( true ); 11 xSt = (Excel._Worksheet)xBk.ActiveSheet; 12 int add = 0 ; 13 foreach (System.Data.DataTable dt in dtList) 14 { 15 colIndex = 1 ; 16 // 取得整个报表的标题 17 excel.Cells[rowIndex , 1 ] = smallTitle[add]; 18 add ++ ; 19 /// /设置整个报表的标题格式 20 xSt.get_Range(excel.Cells[rowIndex, 1 ], excel.Cells[rowIndex , dt.Columns.Count]).Font.Bold = true ; 21 xSt.get_Range(excel.Cells[rowIndex, 1 ], excel.Cells[rowIndex , dt.Columns.Count]).Font.Size = 22 ; 22 /// /设置整个报表的标题为跨列居中 23 xSt.get_Range(excel.Cells[rowIndex , 1 ], excel.Cells[rowIndex , dt.Columns.Count]).Select(); 24 xSt.get_Range(excel.Cells[rowIndex , 1 ], excel.Cells[rowIndex, dt.Columns.Count]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection; 25 rowIndex ++ ; 26 foreach (DataColumn col in dt.Columns) 27 { 28 excel.Cells[rowIndex, colIndex] = col.ColumnName; 29 // 设置标题格式为居中对齐 30 xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).Font.Bold = true ; 31 xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; 32 xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).Select(); 33 xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).Interior.ColorIndex = titleColorindex; 34 colIndex ++ ; 35 } 36 // 取得表格中的数据 37 foreach (DataRow row in dt.Rows) 38 { 39 rowIndex ++ ; 40 colIndex = 1 ; 41 foreach (DataColumn col in dt.Columns) 42 { 43 if (col.DataType == System.Type.GetType( " System.DateTime " )) 44 { 45 if ( ! string .IsNullOrEmpty(row[col.ColumnName].ToString())) 46 { 47 excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString( " yyyy-MM-dd " ); 48 xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; } 49 } 50 else if (col.DataType == System.Type.GetType( " System.String " )) 51 { 52 excel.Cells[rowIndex, colIndex] = " ' " + row[col.ColumnName].ToString(); 53 xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;r; } 54 else 55 { 56 excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString(); 57 xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; } 58 colIndex ++ ; 59 } 60 } 61 rowIndex ++ ; 62 } 63 afterTime = DateTime.Now; 64 xSt.Name = strTitle； 65 string filename = typeName + DateTime.Now.ToString( " yyyyMMdd " ) + " .xls " ; 66 // excel.Save(FilePath+filename); 67 excel.ActiveWorkbook.SaveCopyAs(FilePath + filename); 68 #region 结束Excel进程 69 xBk.Close( null , null , null ); 70 excel.Workbooks.Close(); 71 excel.Quit(); 72 #endregion 73 return filename; 74 }
方法四：采用DataGrid,GridView自带的属性。如下：
private void ExportExcelFromDataGrid( string filename, System.Web.UI.WebControls.GridView ToExcelGrid) { Response.ClearHeaders(); Response.Clear(); Response.Expires = 0 ; Response.Buffer = true ; Response.HeaderEncoding = System.Text.Encoding.UTF8; // Response.Charset = "utf-8"; Response.AppendHeader( " Content-Disposition " , " attachment;filename= " + Server.UrlEncode(filename)); Response.ContentEncoding = System.Text.Encoding.Default; // 设置输出流为简体中文 // Response.ContentType = "application/ms-excel"; // 设置输出文件类型为excel文件。 Response.ContentType = " Application/octet-stream " ; this .EnableViewState = false ; System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo( " zh-CHS " , true ); System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad); System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); ToExcelGrid.RenderControl(oHtmlTextWriter); Response.Write(oStringWriter.ToString()); Response.End(); }
SQL Server 2005 Integration Services (SSIS) 提供一系列支持业务应用程序开发的内置任务、容器、转换和数据适配器。可以创建 SSIS 解决方案来使用 ETL 和商业智能解决复杂的业务问题，管理 SQL Server 数据库以及在 SQL Server 实例之间复制 SQL Server 对象。
（四）数据库中存储过程示例（SSIS应用过程中需要的，最好拿个本子把需要的内容记下） 在SQL SERVER 2005中，以SSISDataBase数据库作为应用，仅包括2张表City，Province.（主要是为了简单，便于讲解）
其中存储过程如下：
ALTER PROCEDURE [dbo].[ProvinceSelectedCityInfo] ( @provinceId int = 0 ) as beginselect P.EName as 省份拼音 ,P.CName as 省份名 ,C.CName as 城市名 from City C left join Province P on C.ProvinceId = P.ProvinceId where C.ProvinceId = @provinceId and @provinceId is not null or @provinceId is null or @provinceId = 0 end
其中，在这一步中我们必须要记住相关的内容，如上标识（红色）；为什么这么做？主要是在制作SSIS包的时候很容易混淆，建议拿个本子把需要的内容写好。
（五）Excel模板的制作（这步这么简单，稍微介绍一下）
因为SSIS中列映射对应的是Excel的标题，与数据是一对一的关系。先不管这么多，看下我们的模板，如下图所示。我们应该能够发现，省份拼音、省份名、城市名，还有ProvinceCityInfoExcel.xls,Sheet1都被笔者标识了，当然 这一步与数据库中的存储过程取出的数据也是一对一的。（ 名称一致，可以减少很多不必要的麻烦，不然的话，嘿嘿....自己去想，那不是哥的事）
等下，需要将创建的EXCEL模板放置到我们的项目文件目录中。 （详见第七步）
（六）SSIS操作过程（生成Package，用来调用） 这一步是最主要的过程，当然，也是很容易出错的一步。 笔者会另外详细介绍制作Package包的过程，本文将直接将生成的包放到VS项目中进行运用。 利用SQL Server 2005数据库自带的SQL Server Business Intelligence Development Studio（SQL Server商业智能开发平台），最终生成的项目如下图所示： 
然后， 将在SSIS项目中生成的Package.dtsx包复制到自己的项目文件目录中。这就是我们马上进入的步骤了---->（步骤七）
（七）C#中调用SSIS创建的Package和Excel模板（可以自己编写逻辑代码进行重复利用），用来生成Excel数据
先看下我们的VS2008项目，如下图所示：
大家会发现，笔者 将（五）（六）步骤生成的模板和Package包放置在项目中的“Excel导出”目录下,当然这些文件随便你放在哪里，这是不用再废话的，哈哈。
另外，笔者简单的设计了如下很粗糙的界面，目的是根据省份来显示城市的相关信息（其实大家都是很熟悉这些的，很多项目都是有省-市-县数据库表的），添加一个导出按钮，点击的时候，我们可以 参考页面显示的内容和我们生成的客户端Excel中的内容是否一致。
现在我们的重头戏开始了，如下代码（点击将触发的代码内容）：
1 protected void btnSSISSearch_Click( object sender, EventArgs e) 2 { 3 // 构造sql语句 作为参数传递给数据包 4 string sqlParams = Jasen.SSIS.Core.SsisToExcel.BuildSql( " dbo.ProvinceSelectedCityInfo " , " @provinceId " , int .Parse(ddlProvice.SelectedValue)); 5 Jasen.SSIS.Core.SsisToExcel ssis = new Jasen.SSIS.Core.SsisToExcel(); 6 string rootPath = Request.PhysicalApplicationPath; 7 string copyFilePath; 8 // 执行SSIS包的操作 生成EXCEL文件 9 bool result = ssis.ExportDataBySsis(rootPath, sqlParams, out copyFilePath, " Package.dtsx " , " ProviceCityInfoExcel.xls " , " ProviceCityInfo " ); 10 if (result == false ){ 11 if (System.IO.File.Exists(copyFilePath)) System.IO.File.Delete(copyFilePath); 12 } 13 else 14 { 15 ssis.DownloadFile( this , " ProviceCityInfoClientFile.xls " , copyFilePath, true ); 16 } 17 }
你肯定会说：“哥，你这个也太简单了吧？”。就是这么简单，不就是多写一个类给你调用就可以了吗。调用接口，这个你总会吧。不过你得了解各个参数才行。
首先，我们必须引用2个DLL，Microsoft.SQLServer.ManagedDTS.dll和Microsoft.SqlServer.DTSPipelineWrap.dll（系统自带的）。先看下我们生成Excel文件数据的步骤，如下：
/// <summary> /// 导出数据到EXCEL文件中 /// </summary> /// <param name="rootPath"></param> /// <param name="sqlParams"> 执行包的传入参数 </param> /// <param name="copyFile"> 生成的Excel的文件 </param> /// <param name="packageName"> SSIS包名称 </param> /// <param name="execlFileName"> SSIS EXCEL模板名称 </param> /// <param name="createdExeclPreName"> 生成的Excel的文件前缀 </param> /// <returns></returns> public bool ExportDataBySsis( string rootPath, string sqlParams, out string tempExcelName, string packageName, string execlFileName, string createdExeclPreName) { // 数据包和EXCEL模板的存储路径 string path = rootPath + @" Excel导出\ " ; // 强制生成目录 if ( ! System.IO.Directory.Exists(path)) System.IO.Directory.CreateDirectory(path); // 返回生成的文件名 string copyFile = this .SaveAndCopyExcel(path, execlFileName, createdExeclPreName); tempExcelName = copyFile; // SSIS包路径 string ssisFileName = path + packageName; // 执行---把数据导入到Excel文件 return ExecuteSsisDataToFile(ssisFileName, tempExcelName, sqlParams); }
代码注释如此清楚，想必也不需要再多做解释了吧，下面就是最最最重要的一步，需要看清楚了----->
1 private bool ExecuteSsisDataToFile( string ssisFileName, string tempExcelName, string sqlParams) 2 { 3 Application app = new Application(); 4 Package package = new Package(); 5 // 加载SSIS包 6 package = app.LoadPackage(ssisFileName, null ); 7 // 获取 数据库连接字符串 8 package.Connections[ " AdoConnection " ].ConnectionString = Jasen.SSIS.Common.SystemConst.ConnectionString; 9 // 目标Excel属性 10 string excelDest = string .Format( " Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\ " EXCEL 8.0 ;HDR = YES\ " ; " , tempExcelName); 11 package.Connections[ " ExcelConnection " ].ConnectionString = excelDest; 12 // 给参数传值 13 Variables vars = package.Variables; 14 string str = vars[ " 用户::SqlStr " ].Value .ToString(); 15 vars[ " 用户::SqlStr " ].Value = sqlParams; 16 // 执行 17 DTSExecResult result = package.Execute(); 18 if (result == DTSExecResult.Success){ 19 return true ; 20 } 21 else { 22 if (package.Errors.Count > 0 ){ 23 // 在log中写出错误列表 24 StringBuilder sb = new StringBuilder(); 25 for ( int i = 0 ; i < package.Errors.Count; i ++ ){ 26 sb.Append( " Package error: " + package.Errors[i].Description + " ; " ); 27 } 28 throw new Exception(sb.ToString()); 29 } 30 else { 31 throw new Exception( " SSIS Unknow error " ); 32 } 33 return false ; 34 } 35 }
上面标注为红色的就是最重要的几个步骤了，相对来说，就是（1）加载包，（2）设置包的数据库连接串，（3）设置Excel的连接串，（4）设置参数变量，（5）执行操作
其次是如何巧妙的将Excel模板复制，使模板可以重复利用（当然也要注意将生成的文件下载到客户端后，将服务器上生成的Excel临时文件删除，你也可以写自己的算法进行清理不必要的Excel临时文件），如下代码所示，方法将复制模板，然后返回生成的临时文件的路径，如果需要删除该文件，System.IO.File.Delete（filePath）就可以删除文件了：
1 private string SaveAndCopyExcel( string sourcePath, string execlFileName, string createdExeclPreName) 2 { 3 string copyFile = sourcePath + createdExeclPreName + DateTime.Now.ToString( " yyyyMMddHHMMss " ) + " .xls " ; 4 if (File.Exists(copyFile)) File.Delete(copyFile); 5 File.Copy(sourcePath + execlFileName, copyFile, true ); 6 return copyFile; 7 }
讲了这么多，来看下我们点击后生成的效果，
开始有点效果了，Excel终于可以下载到客户端了，我们保存该文件。我们是不是想核实一下，我们采用的SSIS方法来实现Excel数据导入是不是正确的，会不会生成错误的数据？
那我们看下下面的一张图，将它与上面的一张图比较一下，看下数据是不是一样的：
发现生成的数据是一模一样的。我们是将数据导入到服务器上的临时EXCEL文件中，将文件发送到客户端肯定是不会出错的，除了你RP太差以外。RP差，任何事情都可能发生，嘿嘿。
（八）总结
在上面的示例中，由于数据量不是太多，你还感觉不到该方法的优势（效率高）。但是当数据量很大的时候，你用其他方法还在那里慢慢地等待excel文件生成的时候，该方法早就已经将数据导入到Excel中，并且发送到客户端了。有时候时间相差几十秒也是有可能的。数据量越大，效果越明显.....
接下来笔者将在另外一篇随笔中详细讲解SSIS package包的制作过程。这篇主要是SSIS应用篇。
希望各位能够在本随笔中有所收获。一口气写下来，还真不容易，写文章确实挺锻炼人的。当然，本文中肯定还有很多不足之处，希望各位多多指教。
本文源代码附上VS2008项目：
相关SSIS资料：