Export به Excel با استفاده از T-SQL و Linked Server

روشی برای ذخیره نتایج یک Query به صورت Excel در Sql Server

برای اینکار ابتدا یک فایل خالی Excel ایجاد نمایید و آنرا مثلا با نام Empty.xls در c:\temp ذخیره نمایید. این فایل در واقع قالب فایلهای Excel شما خواهد بود.
در داخل فایل شیت اول را به نام ExcelTable تغییر داده و در ردیف اول این شیت حروف A, B , C و ... را به ترتیب وارد کنید. این حروف در واقع نام ستونهای شما خواهد بود.

قالب فایل Excel آماده است. حال به سراغ Stored Proc می رویم.

Create proc sp_write2Excel (@fileName varchar(100),

                                   @NumOfColumns tinyint,

                                   @query    varchar(200))

as

begin

        declare @dosStmt  varchar(200)

        declare @tsqlStmt varchar(500)

        declare @colList  varchar(200)

        declare @charInd  tinyint

        

        set nocount on

 

        -- construct the  columnList A,B,C ... 

        -- until Num Of columns is reached.



        set @charInd=0

        set @colList = 'A'

        while @charInd < @NumOfColumns - 1

        begin 

          set @charInd = @charInd + 1

          set @colList = @colList + ',' + char(65 + @charInd)

        end 



        -- Create an Empty Excel file as the target file name by copying the template Empty excel File

        set @dosStmt = ' copy c:\temp\empty.xls ' + @fileName

        exec master..xp_cmdshell @dosStmt

 

        -- Create a "temporary" linked server to that file in order to "Export" Data

        EXEC sp_addlinkedserver 'ExcelSource',

        'Jet 4.0',

        'Microsoft.Jet.OLEDB.4.0',

        @fileName,

        NULL,

        'Excel 5.0'



        -- construct a T-SQL statement that will actually export the query results

        -- to the Table in the target linked server 

        set @tsqlStmt = 'Insert ExcelSource...[ExcelTable$] ' +  ' ( ' + @colList + ' ) '+ @query

        

        print @tsqlStmt



        -- execute dynamically the TSQL statement

        exec (@tsqlStmt)



        -- drop the linked server 

        EXEC sp_dropserver 'ExcelSource'

        set nocount off

end 

GO

کد بالا یک Stored Proc با نام sp_write2excel می سازد. پارامتر های ورودی آن به ترتیب نام فایل مقصد، تعداد ستونها و عبارت Query است.

کد بالا برای انجام این عمل،‌ ابتدا یک Linked Server موقتی می سازد و سپس و نتایج Query مورد نظر شما را به آن ارسال می کند.

برای تست می توانید از عبارت زیر استفاده نمایید :

exec sp_write2Excel 

           -- Target Excel file

           'c:\temp\NorthProducts.xls' ,             



           -- Number of columns in result          

           3,                                                  

  

           -- The query to be exported     

           'select convert(varchar(10),ProductId),  

            ProductName,

            Convert (varchar(20),UnitPrice) from Northwind..Products'

بهترین راه برای ارسال اطلاعات و گزارشات به EXcel فقط Fast Report .....

فکر کنم هر چه به third party وابسته تر نباشی بهتره. ثانیا هیج نوع مقایسه ای را نمی توان بین یک ابزار گزارش سازی و یک stored proc در sql server که هیچ ربطی هم به دلفی نداره انجام داد.