本文来源于网页设计爱好者web开发社区http://www.html.org.cn收集整理，欢迎访问。
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))+' '
print ''
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='[email protected]+';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 '[email protected]_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 '[email protected]+'...'[email protected]_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), [email protected], [email protected]
return
end
if @log = 1 print char(9)+'assigned connectionstring property'
-- set a the conn object's 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), [email protected], [email protected]
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, can't 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), [email protected], [email protected]
return
end
-- %%% this section could be repeated for multiple worksheets (tables)
if @log = 1 print char(9)+'execute ddl to create '''[email protected]_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 excel's 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)+''''[email protected]_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), [email protected], [email protected]
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), [email protected], [email protected]
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 '''[email protected]+''' 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 '''[email protected]_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 '''[email protected]+''' and login'
exec sp_dropserver @servername, 'droplogins'
end
go
set nocount off
print ''
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 '[email protected]+'...'[email protected]_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'
