Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Exporting to Excel Using a CLR Stored Procedure

By Anders Pedersen,

Introduction

Like most of my jobs, my current one requires a lot of simple reports to Excel. Nothing fancy, no serious formatting requirements beyond looking good which in most cases means good column headings and correctly aligned data. DTS and SSIS has most of the time served me well in doing this, but can be rather cumbersome for just a simply exporting the results of a stored procedure.

The solution discussed here has been tested on both 32-bit SQL 2005 and 64-bit SQL 2008, however any scripts are for SQL 2008 so if there are any differences I apologize.

Solution

During a discussion about Excel one of the C# developers I work with, Nick Hanson, he mentioned that he had found an easy way to write results from C# to Excel. We then looked into what was required to make this code into a CLR stored procedure for SQL. Turned out this was really easy and he quickly had a test solution for me. The code to this is available for download. The solution is a CLR stored procedure that takes a stored procedure as a parameter, and puts the output of the stored procedure passed in as a parameter to an Excel file, the only modification needed to use this compared to a regular stored procedure is that the results in the first column becomes the name of the spreadsheet.

The attached DLL will do the work, but the source code is also included to compile it yourself. Visual Studio 2008 was used for this project.

Copy the DLL to whatever directory you want your CLR DLL's to be stored in, in my case that is C:\CLR

The following steps will set this up in a database. First enable CLR on the server:

sp_configure'clr',1
reconfigure

Next step is to set the database to TRUSTWORTHY. Be sure you understand the ramifications of doing this, it should not be take lightly changing this setting.

ALTER DATABASE ProdReports2008 SET TRUSTWORTHY ON

Then we will create the assembly:

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

Associate a stored procedure name with that assembly:

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

At this point you have a stored procedure that can be called, that will export to Excel. The prototype call for this procedure is:

Declare @params xml
Set @params = '<params><param name="lastname" value="Smith" /><param name="country" value="US" /></params>'
exec prc_ExportToExcel 'procname',‘Drive:\Directory\', 'Filename', @params

Note that filename is without XLS at the end, XLS will be added on automatically.

The parameters here are what you would normally use in the stored procedure passed into prc_ExportToExcel, each parameter has to be defined in the XML variable @params. The parameters to pass into the stored procedure is in XML, at a minimum that line needs to have empty <params> in it:

Set @params ='<params></params>' -- this will work for a proc with no parameters

For example:

exec prc_AndersDemo @FirstName = 'Anders'

When called to export to Excel would look like this:

Declare @paramsxml
Set @params='<params><param name="FirstName" value="Anders" /></params>'
exec prc_ExportToExcel 'prc_AndersDemo', ‘Drive:\Directory\', 'Filename', @params

To be able to change the Tab name for the worksheets in Excel, the first column in the stored procedure must hold the name you want on the Tab. Currently we do not have a way to NOT do this, so if you do not put on one it will take whatever is in the first record and make the column header.

I wrote a quick stored procedure to demo this:

create procedureAndersExcelDemo
as
begin
       select 'sysobjects',*fromsys.objects
       select 'syscolumns',*fromsys.columns
end

The call to create an Excel file for my directory structure, note that the directory this is saved to is local seen from the SQL Server it is executed as, but it will work to network shares as long as the account SQL is running under has the appropriate permissions. The below code will create a file on the SQL Server it is executed on in the C:\Anders directory called AndersDemo.XLS.

declare @params XML
set @params='<params></params>'
exec prodreports2008.dbo.prc_ExportToExcel 'AndersTest.dbo.AndersExcelDemo', 'C:\Anders\', 'AndersDemo', @params

The resulting Excel file looks like this:

Data exported to Excel

Note how it creates 2 spreadsheets, one corresponding to each result set in the stored procedures. I have not tested what the limits is for how many can be created, but one I have in production is running fine with 15.

I hope this can be of use to some of you. Comments and suggestions for improvements are welcome.

Resources:

ExcelExport.zip
Total article views: 18237 | Views in the last 30 days: 26
 
Related Articles
FORUM

store procedure result to excel

store procedure result to excel

FORUM

Help with frustrating BCP call and stored procedure params

BCP + Stored Procedure queryout with SP params - "quotes" are killing me !!!!

FORUM

Stored Procedure and Excel

Stored procedure that can read excel

FORUM

stored procedure results to excel file.

Excel files from stored procedure

FORUM

To Create PDF from SQL stored procedure

To Create PDF from SQL stored procedure

Tags
excel    
sqlclr    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones