参考网上资料，自己做的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=@#+@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, 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 @#+@ServerName+@#...@#+@WKS_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：@#+@SQL
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 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), 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, 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), 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 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)+@#@#@#@#+@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
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 @#@#@#+@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 @#@#
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
----------------------------------------------------------------------------------
延伸阅读
文章来源于领测软件测试网 https://www.ltesting.net/
