none
create Excel file in sql database project

    Question

  • Hi, I need to create an Excel file and fill it with formated data from sql database. This is easy in almost any type of project, but I couldn't find a solution hot to do this in c# MS VS sql database project - the whole project is extracted as assembly to sql server. The problem is, I can't reference any Microsoft.Excel class.

    Is there any solution how to create an excel file from datatable or dataset in MS VS c# sql database project?

    Monday, September 24, 2012 9:21 AM

Answers

  • Hi Tomas,

    I found a useful article Exporting to Excel Using a CLR Stored Procedure written by Anders Pedersen:

    http://www.sqlservercentral.com/articles/SQLCLR/68842/

    If you do not want to register, here are the components(C# sql project dll http://www.sqlservercentral.com/Files/ExcelExport.zip/4943.zip)

    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
    

    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.


    TechNet Subscriber Support

    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.




    Tuesday, September 25, 2012 7:54 AM
    Moderator
  • Hi, thx for reply.

    I got it working this way: 

    I created assembly for Microsoft.Office.Interop.Excel  in database, then I was able to working with excel as in regular win application project.

    But I have one problem. Let have:

     private Excel.Application document =null;
            private Excel.Sheets sheets =null;
            private Excel.Worksheet newSheet = null;
            private Excel.Workbook workbook
            {
                get
                {
                    sheets = workbook.Sheets;
                    newSheet = (Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
                    newSheet.Name = "My New Sheet";
                    newSheet.Cells[1, 1] = "BOO!";
    
                    workbook.Save();
                    workbook.Close(null, null, null);
                    return document.Workbooks.Add(System.Reflection.Missing.Value);
                }
            }

    The execution of clr stored procedure (also when debugging it) got stuck on the row: workbook.Close. No error rises. When I comment that row, everything works fine. Does anybody knows why it got stuck? Or should I just let the row commented?

    Tuesday, October 16, 2012 11:08 AM

All replies

  • Hi Tomas,

    I found a useful article Exporting to Excel Using a CLR Stored Procedure written by Anders Pedersen:

    http://www.sqlservercentral.com/articles/SQLCLR/68842/

    If you do not want to register, here are the components(C# sql project dll http://www.sqlservercentral.com/Files/ExcelExport.zip/4943.zip)

    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
    

    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.


    TechNet Subscriber Support

    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.




    Tuesday, September 25, 2012 7:54 AM
    Moderator
  • Hi, thx for reply.

    I got it working this way: 

    I created assembly for Microsoft.Office.Interop.Excel  in database, then I was able to working with excel as in regular win application project.

    But I have one problem. Let have:

     private Excel.Application document =null;
            private Excel.Sheets sheets =null;
            private Excel.Worksheet newSheet = null;
            private Excel.Workbook workbook
            {
                get
                {
                    sheets = workbook.Sheets;
                    newSheet = (Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
                    newSheet.Name = "My New Sheet";
                    newSheet.Cells[1, 1] = "BOO!";
    
                    workbook.Save();
                    workbook.Close(null, null, null);
                    return document.Workbooks.Add(System.Reflection.Missing.Value);
                }
            }

    The execution of clr stored procedure (also when debugging it) got stuck on the row: workbook.Close. No error rises. When I comment that row, everything works fine. Does anybody knows why it got stuck? Or should I just let the row commented?

    Tuesday, October 16, 2012 11:08 AM