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 که هیچ ربطی هم به دلفی نداره انجام داد.