SQL to Excel 的应用.doc

SQL to Excel 的应用 作者：未知 来源：月光软件站 加入时间：2005-2-28　月光软件站 参考网上资料，自己做的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