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


    效率最高的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组件。一般,对于操作能够基本满足,但对于数据量大时可能会慢点。下面的代码,本人在原有基础上稍微修改了下,如下所示:

    复制代码
     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自带的属性。如下:


    (三)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.(主要是为了简单,便于讲解)

       

        其中存储过程如下:

    复制代码
    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
    复制代码

        其中,在这一步中我们必须要记住相关的内容,如上标识(红色);为什么这么做?主要是在制作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)就可以删除文件了:

       
         讲了这么多,来看下我们点击后生成的效果,
     
        开始有点效果了,Excel终于可以下载到客户端了,我们保存该文件。我们是不是想核实一下,我们采用的SSIS方法来实现Excel数据导入是不是正确的,会不会生成错误的数据?
        那我们看下下面的一张图,将它与上面的一张图比较一下,看下数据是不是一样的:
     
        发现生成的数据是一模一样的。我们是将数据导入到服务器上的临时EXCEL文件中,将文件发送到客户端肯定是不会出错的,除了你RP太差以外。RP差,任何事情都可能发生,嘿嘿。
     
    (八)总结
        在上面的示例中,由于数据量不是太多,你还感觉不到该方法的优势(效率高)。但是当数据量很大的时候,你用其他方法还在那里慢慢地等待excel文件生成的时候,该方法早就已经将数据导入到Excel中,并且发送到客户端了。有时候时间相差几十秒也是有可能的。数据量越大,效果越明显.....
        接下来笔者将在另外一篇随笔中详细讲解SSIS package包的制作过程。这篇主要是SSIS应用篇。
        希望各位能够在本随笔中有所收获。一口气写下来,还真不容易,写文章确实挺锻炼人的。当然,本文中肯定还有很多不足之处,希望各位多多指教。
     
  • 相关阅读:
    Nginx与Apache的对比
    gc buffer busy waits(ZT)
    Brocade SAN Switch Change Domain ID (ZT)
    Oracle异机恢复时报错ora19870 ora19507
    row cache lock (ZT)
    can a select block a truncate (ZT)
    NBU常用命令
    the RRD does not contain an RRA matching the chosen C
    Solaris10 x64安装64bit perl
    Solaris and Oracle 32bit Linking Error "fatal: symbol 'ntcontab'
  • 原文地址:https://www.cnblogs.com/mmbbflyer/p/6126743.html
Copyright © 2020-2023  润新知