SQLSERVER使用CLR Stored Procedure导出数据到Excel

      在实际应用中，我们经常需要导出数据到Excel文件，你可以使用DTS或SSIS来做。但有时，我们并不需要这么重量级的工具，直接用CLR SP实现就可以了。
假设你已经写好了导出Excel的C# code:

  /// <summary>
    /// This is the method registered with SQL Server as a
    /// CLR stored procedure.  The attribute, Microsoft.SqlServer.Server.SqlProcedure, is
    /// required for the method to be a CLR stored procedure.
    /// </summary>
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void ExportToExcel(SqlString procName, SqlString filePath, SqlString fileName, SqlXml xmlParams)
    {
        DataSet exportData = new DataSet();

        //check for empty parameters

        if (procName.Value == string.Empty)
            throw new Exception("Procedure name value is missing.");

        if (filePath.Value == string.Empty)
            throw new Exception("Missing file path location.");

        if (fileName.Value == string.Empty)
            throw new Exception("Missing name of file.");

        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand getOutput = new SqlCommand();

            getOutput.CommandText = procName.ToString(); ;
            getOutput.CommandType = CommandType.StoredProcedure;
            getOutput.CommandTimeout = 120;

            //To allow for multiple parameters, xml is used
            //and must then be parsed to set up the paramaters
            //for the command object.
            using (XmlReader parms = xmlParams.CreateReader())
            {
                while(parms.Read())
                {
                    if (parms.Name == "param")
                    {
                        string paramName;
                        paramName = parms.GetAttribute("name");

                        string paramValue;
                        paramValue = parms.GetAttribute("value");

                        getOutput.Parameters.AddWithValue(paramName, paramValue);
                    }
                }
            }

            getOutput.Connection = conn;

            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter(getOutput);
            da.Fill(exportData);
            conn.Close();
        }

        ExcelExportUtility exportUtil = new ExcelExportUtility(fileName.ToString(),filePath.ToString());
        //This allows for flexible naming of the tabs in the workbook
        exportUtil.SheetNameColumnOrdinal = 0;
        exportUtil.Export(exportData);
    }

C#导出Excel细节代码在这儿就不贴了。编译成Assembly,这里我们叫ExcelExport.dll。

首先，启用SQL CLR

sp_configure'clr',1
reconfigure

设置Database可依赖性:

ALTER DATABASE [AdventureWorks] SET TRUSTWORTHY ON

接下来，我们在AdventureWorks 数据库中注册这个Assembly.

CREATE ASSEMBLY ExportToExcel
FROM 'D:\ExcelExport.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS

创建一个存储过程关联它：

CREATE PROCEDURE [dbo].[prc_ExportToExcel]
       @proc [nvarchar](100),
       @path [nvarchar](200),
       @filename [nvarchar](100),
       @params xml
AS
EXTERNAL NAME [ExportToExcel].[StoredProcedures].[ExportToExcel]

好的。让我们创建一个查询数据的SP，加一个'MyContactList'列，导出时将取这个名称做为Sheet的名称，另返回多个数据集将生成多个Sheet页。：

CREATE PROC GetContactByFirstName
@FirstName  nvarchar(50)
AS
BEGIN
  SELECT 'MyContactList'
      ,[ContactID]
      ,[FirstName]
      ,[LastName]
      ,[EmailAddress]
      ,[Phone]
  FROM [AdventureWorks].[Person].[Contact] Where FirstName=@FirstName
END

现在，我们可以使用了,

Declare @params xml
Set @params = '<params><param name="FirstName" value="Peter" /></params>'
--Set @params = '<params />'
exec [AdventureWorks].[dbo].[prc_ExportToExcel]
'[AdventureWorks].[dbo].[GetContactByFirstName]', 'D:\', 'MyContact', @params

注意，传递参数是xml，如果无参数刚传’<params />’，FirstName是之前那个SP的参数。最后，运行，将生成文件 D:\MyContact.xls

 

完了，由此我们可以看到SQL CLR的强大。

Author:Petter Liu   http://wintersun.cnblogs.com

希望对您有帮助！