How to export sql query to an Excel file

Posted on 2011-10-30
Last Modified: 2012-05-12
I would like when user clicks on ExportToExcel button to run code behind
"select * from tblData where RequestNumber = 77777777"
 to export the query data to a new excel workbook called Request.xls
I am using and visual basic.
Your help will be greately appreciated.
Question by:smc1234
    LVL 83

    Expert Comment

    LVL 11

    Expert Comment

    use master
    if object_id('spExportData') is not null
        drop proc spExportData
    create proc spExportData
        @dbName varchar(100) = 'master',
        @sql varchar(5000) = '',    
        @fullFileName varchar(100) = ''
    if @sql = '' or @fullFileName = ''
        select 0 as ReturnValue -- failure
    -- if DB isn't passed in set it to master
    select    @dbName = 'use ' + @dbName + ';'
    if object_id('##TempExportData') is not null
        drop table ##TempExportData
    if object_id('##TempExportData2') is not null
        drop table ##TempExportData2
    -- insert data into a global temp table
    declare @columnNames varchar(8000), @columnConvert varchar(8000), @tempSQL varchar(8000)
    select    @tempSQL = left(@sql, charindex('from', @sql)-1) + ' into ##TempExportData ' +

         substring(@sql, charindex('from', @sql)-1, len(@sql))
    exec(@dbName + @tempSQL)
    if @@error > 0
        select 0 as ReturnValue -- failure
    -- build 2 lists
    -- 1. column names
    -- 2. columns converted to nvarchar
    SELECT    @columnNames = COALESCE( @columnNames  + ',', '') + column_name,
            @columnConvert = COALESCE( @columnConvert  + ',', '') + 'convert(nvarchar(4000),'
            + column_name + case when data_type in ('datetime', 'smalldatetime') then ',121'
                                 when data_type in ('numeric', 'decimal') then ',128'
                                 when data_type in ('float', 'real', 'money', 'smallmoney') then ',2'
                                 when data_type in ('datetime', 'smalldatetime') then ',120'
                                 else ''
                            end + ') as ' + column_name
    FROM    tempdb.INFORMATION_SCHEMA.Columns
    WHERE    table_name = '##TempExportData'
    -- execute select query to insert data and column names into new temp table
    SELECT    @sql = 'select ' + @columnNames + ' into ##TempExportData2 from (select ' + @columnConvert + ', ''2'' as [temp##SortID]
           from ##TempExportData union all select ''' + replace(@columnNames, ',', ''', ''') + ''', ''1'') t order by [temp##SortID]'
    exec (@sql)
    -- build full BCP query
    select    @sql = 'bcp "' + @dbName + ' select * from ##TempExportData2" queryout "' + @fullFileName + '" -c -CRAW'
    -- execute BCP
    Exec master..xp_cmdshell @sql
    if @@error > 0
        select 0 as ReturnValue -- failure
    drop table ##TempExportData
    drop table ##TempExportData2
    select 1 as ReturnValue -- success
    declare @sql varchar(6800),    @dbName varchar(100), @fullFileName varchar(100)
    select    @dbName = 'northwind', @sql = 'select * from orders order by orderdate', @fullFileName = 'e:\test.xls'
    exec    master..spExportData @dbName, @sql, @fullFileName
    LVL 35

    Expert Comment

    by:Miguel Oz
    You can save your sql query to a datatable and then  this datatable tos csv file (Excel can read it), check:
    LVL 7

    Accepted Solution

    whenever I see this question, whether it's exporting from a datatable or gridview, I can do no better than point the OP to Matt Berseth's pages, he explains it all nicely and the code is solid.

    I know he's done it in C#, but it shouldn't be difficult to translate :) good luck!

    Author Closing Comment

    Thank you

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
    For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK ( for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now