create excel xls from t-sql
---------------------------------------------------------------------------
-- create xls script dal - 04/24/2003
--
-- designed for agent scheduling, turn on "append output for step history"
--
-- search for %%% to find adjustable constants and other options
--
-- uses ole for ado and ole db to create the xls file if it does not exist
-- linked server requires the xls to exist before creation
-- uses ole ado to create the xls worksheet for use as a table by t-sql
-- uses linked server to allow t-sql access to xls table
-- uses t-sql to populate te xls worksheet, very fast
--
print begin createxls script at +rtrim(convert(varchar(24),getdate(),121))+
go
set nocount on
declare @conn int -- ado connection object to create xls
, @hr int -- ole return value
, @src varchar(255) -- ole error source
, @desc varchar(255) -- ole error description
, @path varchar(255) -- drive or unc path for xls
, @connect varchar(255) -- ole db connection string for jet 4 excel isam
, @wks_created bit -- whether the xls worksheet exists
, @wks_name varchar(128) -- name of the xls worksheet (table)
, @servername nvarchar(128) -- linked server name for xls
, @ddl varchar(8000) -- jet4 ddl for the xls wks table creation
, @sql varchar(8000) -- insert into xls t-sql
, @recs int -- number of records added to xls
, @log bit -- whether to log process detail
-- init variables
select @recs = 0
-- %%% 1 = verbose output detail, helps find problems, 0 = minimal output detail
, @log = 1
-- %%% assign the unc or path and name for the xls file, requires read/write access
-- must be accessable from server via sql server service account
-- & sql server agent service account, if scheduled
set @path = c:\temp\test_+convert(varchar(10),getdate(),112)+.xls
-- assign the ado connection string for the xls creation
set @connect = provider=microsoft.jet.oledb.4.0;data source=+@path+;extended properties=excel 8.0
-- %%% assign the linked server name for the xls population
set @servername = excel_test
-- %%% rename table as required, this will also be the xls worksheet name
set @wks_name = people
-- %%% table creation ddl, uses jet4 syntax,
-- text data type = varchar(255) when accessed from t-sql
set @ddl = create table +@wks_name+ (ssn text, name text, phone text)
-- %%% t-sql for table population, note the 4 part naming required by jet4 ole db
-- insert into select, insert into values, and exec sp types are supported
-- linked server does not support select into types
set @sql = insert into +@servername+...+@wks_name+ (ssn, name, phone)
set @sql = @sql+select au_id as ssn
set @sql = @sql+, ltrim(rtrim(isnull(au_fname,)+ +isnull(au_lname,))) as name
set @sql = @sql+, phone as phone
set @sql = @sql+from pubs.dbo.authors
if @log = 1 print created ole adodb.connection object
-- create the conn object
exec @hr = sp_oacreate adodb.connection, @conn out
if @hr <> 0 -- have to use <> as ole / ado can return negative error numbers
begin
-- return ole error
exec sp_oageterrorinfo @conn, @src out, @desc out
select error=convert(varbinary(4),@hr), source=@src, description=@desc
return
end
if @log = 1 print char(9)+assigned connectionstring property
-- set a the conn objects connectionstring property
-- work-around for error using a variable parameter on the open method
exec @hr = sp_oasetproperty @conn, connectionstring, @connect
if @hr <> 0
begin
-- return ole error
exec sp_oageterrorinfo @conn, @src out, @desc out
select error=convert(varbinary(4),@hr), source=@src, description=@desc
return
end
if @log = 1 print char(9)+open connection to xls, for file create or append
-- call the open method to create the xls if it does not exist, cant use parameters
exec @hr = sp_oamethod @conn, open
if @hr <> 0
begin
-- return ole error
exec sp_oageterrorinfo @conn, @src out, @desc out
select error=convert(varbinary(4),@hr), source=@src, description=@desc
return
end
-- %%% this section could be repeated for multiple worksheets (tables)
if @log = 1 print char(9)+execute ddl to create +@wks_name+ worksheet
-- call the execute method to create the work sheet with the @wks_name caption,
-- which is also used as a table reference in t-sql
-- neat way to define column data types in excel worksheet
-- sometimes converting to text is the only work-around for excels general
-- cell formatting, even though the cell contains text, excel tries to format
-- it in a "smart" way, i have even had to use the single quote appended as the
-- 1st character in t-sql to force excel to leave it alone
exec @hr = sp_oamethod @conn, execute, null, @ddl, null, 129 -- adcmdtext + adexecutenorecords
-- 0x80040e14 for table exists in ado
if @hr = 0x80040e14
-- kludge, skip 0x80042732 for ado optional parameters (null) in sql7
or @hr = 0x80042732
begin
-- trap these ole errors
if @hr = 0x80040e14
begin
print char(9)++@wks_name+ worksheet exists for append
set @wks_created = 0
end
set @hr = 0 -- ignore these errors
end
if @hr <> 0
begin
-- return ole error
exec sp_oageterrorinfo @conn, @src out, @desc out
select error=convert(varbinary(4),@hr), source=@src, description=@desc
return
end
if @log = 1 print destroyed ole adodb.connection object
-- destroy the conn object, +++ important to not leak memory +++
exec @hr = sp_oadestroy @conn
if @hr <> 0
begin
-- return ole error
exec sp_oageterrorinfo @conn, @src out, @desc out
select error=convert(varbinary(4),@hr), source=@src, description=@desc
return
end
-- linked server allows t-sql to access the xls worksheet (table)
-- this must be performed after the ado stuff as the xls must exist
-- and contain the schema for the table, or worksheet
if not exists(select srvname from master.dbo.sysservers where srvname = @servername)
begin
if @log = 1 print created linked server +@servername+ and login
exec sp_addlinkedserver @server = @servername
, @srvproduct = microsoft excel workbook
, @provider = microsoft.jet.oledb.4.0
, @datasrc = @path
, @provstr = excel 8.0
-- no login name or password are required to connect to the jet4 isam linked server
exec sp_addlinkedsrvlogin @servername, false
end
-- have to exec the sql, otherwise the sql is evaluated
-- for the linked server before it exists
exec (@sql)
print char(9)+populated +@wks_name+ table with +convert(varchar,@@rowcount)+ rows
-- %%% optional you may leave the linked server for other xls operations
-- remember that the linked server will not create the xls, so remove it
-- when you are done with it, especially if you delete or move the file
if exists(select srvname from master.dbo.sysservers where srvname = @servername)
begin
if @log = 1 print deleted linked server +@servername+ and login
exec sp_dropserver @servername, droplogins
end
go
set nocount off
print finished createxls script at +rtrim(convert(varchar(24),getdate(),121))+
go
目的
set @path = c:\temp\test_+convert(varchar(10),getdate(),112)+.xls
源
set @sql = insert into +@servername+...+@wks_name+ (ssn, name, phone)
set @sql = @sql+select au_id as ssn
set @sql = @sql+, ltrim(rtrim(isnull(au_fname,)+ +isnull(au_lname,))) as name
set @sql = @sql+, phone as phone
set @sql = @sql+from pubs.dbo.authors
文章整理：站长天空 网址：http://www.z6688.com/
以上信息与文章正文是不可分割的一部分,如果您要转载本文章,请保留以上信息，谢谢!
