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:

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.