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

[Zahraii]

روشی برای ذخیره نتایج یک 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 .....

[Zahraii]

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