效率最高的Excel数据导入---(c#调用SSIS Package将数据库数据导入到Excel文件中【附源代码下载】) 转

时间:2022-02-16 15:07:05

效率最高的Excel数据导入---(c#调用SSIS Package将数据库数据导入到Excel文件中【附源代码下载】)

 本文目录:

(一)背景

(二)数据库数据导入到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)。当数据量大时,肯定效率还是有影响的。

方案三:采用Excel组件。一般,对于操作能够基本满足,但对于数据量大时可能会慢点。下面的代码,本人在原有基础上稍微修改了下,如下所示:

效率最高的Excel数据导入---(c#调用SSIS Package将数据库数据导入到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         }      
效率最高的Excel数据导入---(c#调用SSIS Package将数据库数据导入到Excel文件中【附源代码下载】) 转

方法四:采用DataGrid,GridView自带的属性。如下:

(三)SSIS的简介
    SQL Server 2005 提供的一个集成化的商业智能开发平台,主要包括:   
  *SQL Server Analysis Services(SQL Server数据分析服务,简称SSAS)   
  *SQL Server Reporting Services(SQL Server报表服务,简称SSRS)   
  *SQL Server Integration Services(SQL Server数据集成服务,简称SSIS)

SQL Server 2005 Integration Services (SSIS) 提供一系列支持业务应用程序开发的内置任务、容器、转换和数据适配器。可以创建 SSIS 解决方案来使用 ETL 和商业智能解决复杂的业务问题,管理 SQL Server 数据库以及在 SQL Server 实例之间复制 SQL Server 对象。
 
(四)数据库中存储过程示例(SSIS应用过程中需要的,最好拿个本子把需要的内容记下) 
    在SQL SERVER 2005中,以SSISDataBase数据库作为应用,仅包括2张表City,Province.(主要是为了简单,便于讲解)
效率最高的Excel数据导入---(c#调用SSIS Package将数据库数据导入到Excel文件中【附源代码下载】) 转

其中存储过程如下:

效率最高的Excel数据导入---(c#调用SSIS Package将数据库数据导入到Excel文件中【附源代码下载】) 转
ALTER PROCEDURE [dbo].[ProvinceSelectedCityInfo]
(
@provinceId int=0

as
begin
select 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
效率最高的Excel数据导入---(c#调用SSIS Package将数据库数据导入到Excel文件中【附源代码下载】) 转

其中,在这一步中我们必须要记住相关的内容,如上标识(红色);为什么这么做?主要是在制作SSIS包的时候很容易混淆,建议拿个本子把需要的内容写好。

(五)Excel模板的制作(这步这么简单,稍微介绍一下)
   因为SSIS中列映射对应的是Excel的标题,与数据是一对一的关系。先不管这么多,看下我们的模板,如下图所示。我们应该能够发现,省份拼音、省份名、城市名,还有ProvinceCityInfoExcel.xls,Sheet1都被笔者标识了,当然这一步与数据库中的存储过程取出的数据也是一对一的。(名称一致,可以减少很多不必要的麻烦,不然的话,嘿嘿....自己去想,那不是哥的事)
效率最高的Excel数据导入---(c#调用SSIS Package将数据库数据导入到Excel文件中【附源代码下载】) 转
    等下,需要将创建的EXCEL模板放置到我们的项目文件目录中。(详见第七步)
(六)SSIS操作过程(生成Package,用来调用)
    这一步是最主要的过程,当然,也是很容易出错的一步。笔者会另外详细介绍制作Package包的过程,本文将直接将生成的包放到VS项目中进行运用。
    利用SQL Server 2005数据库自带的SQL Server Business Intelligence Development Studio(SQL Server商业智能开发平台),最终生成的项目如下图所示:
 效率最高的Excel数据导入---(c#调用SSIS Package将数据库数据导入到Excel文件中【附源代码下载】) 转
    然后,将在SSIS项目中生成的Package.dtsx包复制到自己的项目文件目录中。这就是我们马上进入的步骤了---->(步骤七)
(七)C#中调用SSIS创建的Package和Excel模板(可以自己编写逻辑代码进行重复利用),用来生成Excel数据
    先看下我们的VS2008项目,如下图所示:
效率最高的Excel数据导入---(c#调用SSIS Package将数据库数据导入到Excel文件中【附源代码下载】) 转
    大家会发现,笔者将(五)(六)步骤生成的模板和Package包放置在项目中的“Excel导出”目录下,当然这些文件随便你放在哪里,这是不用再废话的,哈哈。
    另外,笔者简单的设计了如下很粗糙的界面,目的是根据省份来显示城市的相关信息(其实大家都是很熟悉这些的,很多项目都是有省-市-县数据库表的),添加一个导出按钮,点击的时候,我们可以参考页面显示的内容和我们生成的客户端Excel中的内容是否一致。
效率最高的Excel数据导入---(c#调用SSIS Package将数据库数据导入到Excel文件中【附源代码下载】) 转
    现在我们的重头戏开始了,如下代码(点击将触发的代码内容):
效率最高的Excel数据导入---(c#调用SSIS Package将数据库数据导入到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         }   
效率最高的Excel数据导入---(c#调用SSIS Package将数据库数据导入到Excel文件中【附源代码下载】) 转

你肯定会说:“哥,你这个也太简单了吧?”。就是这么简单,不就是多写一个类给你调用就可以了吗。调用接口,这个你总会吧。不过你得了解各个参数才行。

首先,我们必须引用2个DLL,Microsoft.SQLServer.ManagedDTS.dll和Microsoft.SqlServer.DTSPipelineWrap.dll(系统自带的)。先看下我们生成Excel文件数据的步骤,如下:

效率最高的Excel数据导入---(c#调用SSIS Package将数据库数据导入到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);
        }
效率最高的Excel数据导入---(c#调用SSIS Package将数据库数据导入到Excel文件中【附源代码下载】) 转

代码注释如此清楚,想必也不需要再多做解释了吧,下面就是最最最重要的一步,需要看清楚了----->

效率最高的Excel数据导入---(c#调用SSIS Package将数据库数据导入到Excel文件中【附源代码下载】) 转
 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         }
效率最高的Excel数据导入---(c#调用SSIS Package将数据库数据导入到Excel文件中【附源代码下载】) 转

上面标注为红色的就是最重要的几个步骤了,相对来说,就是(1)加载包,(2)设置包的数据库连接串,(3)设置Excel的连接串,(4)设置参数变量,(5)执行操作

其次是如何巧妙的将Excel模板复制,使模板可以重复利用(当然也要注意将生成的文件下载到客户端后,将服务器上生成的Excel临时文件删除,你也可以写自己的算法进行清理不必要的Excel临时文件),如下代码所示,方法将复制模板,然后返回生成的临时文件的路径,如果需要删除该文件,System.IO.File.Delete(filePath)就可以删除文件了:

     讲了这么多,来看下我们点击后生成的效果,
效率最高的Excel数据导入---(c#调用SSIS Package将数据库数据导入到Excel文件中【附源代码下载】) 转
    开始有点效果了,Excel终于可以下载到客户端了,我们保存该文件。我们是不是想核实一下,我们采用的SSIS方法来实现Excel数据导入是不是正确的,会不会生成错误的数据?
    那我们看下下面的一张图,将它与上面的一张图比较一下,看下数据是不是一样的:
效率最高的Excel数据导入---(c#调用SSIS Package将数据库数据导入到Excel文件中【附源代码下载】) 转
    发现生成的数据是一模一样的。我们是将数据导入到服务器上的临时EXCEL文件中,将文件发送到客户端肯定是不会出错的,除了你RP太差以外。RP差,任何事情都可能发生,嘿嘿。
(八)总结
    在上面的示例中,由于数据量不是太多,你还感觉不到该方法的优势(效率高)。但是当数据量很大的时候,你用其他方法还在那里慢慢地等待excel文件生成的时候,该方法早就已经将数据导入到Excel中,并且发送到客户端了。有时候时间相差几十秒也是有可能的。数据量越大,效果越明显.....
    接下来笔者将在另外一篇随笔中详细讲解SSIS package包的制作过程。这篇主要是SSIS应用篇。
    希望各位能够在本随笔中有所收获。一口气写下来,还真不容易,写文章确实挺锻炼人的。当然,本文中肯定还有很多不足之处,希望各位多多指教。