參考網上資料，自己做的sql to excel 事例，
1、需要先安裝ms的事例數據庫：pubs
2、預先已有的c:/temp/test.xls(macro代碼已寫好，包含'sheet1'和'people'兩張sheet)
3、執行此sql，可把數據導入test.xls
4、打開test.xls，按按鈕，可產生數據的圖表
sql：
---------------------------------------------------------------------------------------------------------------------------
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.xls'
--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, zip numeric)'
-- %%% 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, zip) '
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+', zip as zip '
set @sql = @sql+'from pubs.dbo.authors '
set @sql = @sql+'order by zip '
print '1：'[email protected]
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
print '2：'
-- 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
---------------------------------------------------------------------------------------------------------------------------------------------------
excel .sheet1.commandbutton宏代碼：
-----------------------------------------------------------------------
private sub commandbutton1_click()
dim b_p as boolean
b_p = false
for i = 1 to sheets.count
if sheets(i).name = "people" then
b_p = true
exit for
end if
next i
if b_p = false then exit sub
charts.add
activechart.charttype = xlcolumnclustered
activechart.setsourcedata source:=sheets("people").range("b1:d24"), plotby _
:=xlcolumns
activechart.seriescollection(1).xvalues = "=people!r2c2:r24c2"
activechart.location where:=xllocationasobject, name:="sheet1"
with activechart
.hastitle = true
.charttitle.characters.text = "zip"
.axes(xlcategory, xlprimary).hastitle = false
.axes(xlvalue, xlprimary).hastitle = false
end with
end sub
----------------------------------------------------------------------------------
