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

墨蓝 2022-09-28 05:43 168阅读 0赞

[SQLSERVER使用CLR Stored Procedure导出数据到Excel][SQLSERVER_CLR Stored Procedure_Excel]

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

1:    /// <summary>

2:      /// This is the method registered with SQL Server as a

3:      /// CLR stored procedure.  The attribute, Microsoft.SqlServer.Server.SqlProcedure, is

4:      /// required for the method to be a CLR stored procedure.

5:      /// </summary>

6:      [Microsoft.SqlServer.Server.SqlProcedure]

7:      public static void ExportToExcel(SqlString procName, SqlString filePath, SqlString fileName, SqlXml xmlParams)

8:      {

9:          DataSet exportData = new DataSet();

10:

11:          //check for empty parameters

12:

13:          if (procName.Value == string.Empty)

14:              throw new Exception("Procedure name value is missing.");

15:

16:          if (filePath.Value == string.Empty)

17:              throw new Exception("Missing file path location.");

18:

19:          if (fileName.Value == string.Empty)

20:              throw new Exception("Missing name of file.");

21:

22:          using (SqlConnection conn = new SqlConnection("context connection=true"))

23:          {

24:              SqlCommand getOutput = new SqlCommand();

25:

26:              getOutput.CommandText = procName.ToString(); ;

27:              getOutput.CommandType = CommandType.StoredProcedure;

28:              getOutput.CommandTimeout = 120;

29:

30:              //To allow for multiple parameters, xml is used

31:              //and must then be parsed to set up the paramaters

32:              //for the command object.

33:              using (XmlReader parms = xmlParams.CreateReader())

34:              {

35:                  while(parms.Read())

36:                  {

37:                      if (parms.Name == "param")

38:                      {

39:                          string paramName;

40:                          paramName = parms.GetAttribute("name");

41:

42:                          string paramValue;

43:                          paramValue = parms.GetAttribute("value");

44:

45:                          getOutput.Parameters.AddWithValue(paramName, paramValue);

46:                      }

47:                  }

48:              }

49:

50:              getOutput.Connection = conn;

51:

52:              conn.Open();

53:              SqlDataAdapter da = new SqlDataAdapter(getOutput);

54:              da.Fill(exportData);

55:              conn.Close();

56:          }

57:

58:          ExcelExportUtility exportUtil = new ExcelExportUtility(fileName.ToString(),filePath.ToString());

59:          //This allows for flexible naming of the tabs in the workbook

60:          exportUtil.SheetNameColumnOrdinal = 0;

61:          exportUtil.Export(exportData);

62:      }

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

首先，启用SQL CLR

1:  sp_configure'clr',1

2:  reconfigure

设置Database可依赖性:

ALTER DATABASE [AdventureWorks] SET TRUSTWORTHY ON

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

1:  CREATE ASSEMBLY ExportToExcel

2:  FROM 'D:/ExcelExport.dll'

3:  WITH PERMISSION_SET = EXTERNAL_ACCESS

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

1:  CREATE PROCEDURE [dbo].[prc_ExportToExcel]

2:         @proc [nvarchar](100),

3:         @path [nvarchar](200),

4:         @filename [nvarchar](100),

5:         @params xml

6:  AS

7:  EXTERNAL NAME [ExportToExcel].[StoredProcedures].[ExportToExcel]

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

1:  CREATE PROC GetContactByFirstName

2:  @FirstName  nvarchar(50)

3:  AS

4:  BEGIN

5:    SELECT 'MyContactList'

6:        ,[ContactID]

7:        ,[FirstName]

8:        ,[LastName]

9:        ,[EmailAddress]

10:        ,[Phone]

11:    FROM [AdventureWorks].[Person].[Contact] Where FirstName=@FirstName

12:  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

[![exceldata][]][exceldata 1]

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

[SQLSERVER_CLR Stored Procedure_Excel]: http://www.cnblogs.com/wintersun/archive/2009/12/03/1616410.html
[exceldata]: /images/20220708/906d8d56f68b455ba704bd347a150da7.png
[exceldata 1]: http://images.cnblogs.com/cnblogs_com/wintersun/WindowsLiveWriter/SQLSERVERCLRStoredProcedureExcel_FD8D/exceldata_2.png