数据库

本类阅读TOP10

·SQL语句导入导出大全
·SQL Server日期计算
·SQL语句导入导出大全
·SQL to Excel 的应用
·Oracle中password file的作用及说明
·MS SQLServer OLEDB分布式事务无法启动的一般解决方案
·sqlserver2000数据库置疑的解决方法
·一个比较实用的大数据量分页存储过程
·如何在正运行 SQL Server 7.0 的服务器之间传输登录和密码
·SQL中两台服务器间使用连接服务器

分类导航
VC语言Delphi
VB语言ASP
PerlJava
Script数据库
其他语言游戏开发
文件格式网站制作
软件工程.NET开发
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 [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
----------------------------------------------------------------------------------





相关文章

相关软件




月光软件源码下载编程文档电脑教程网站优化网址导航网络文学游戏天地生活休闲写作范文安妮宝贝站内搜索
电脑技术编程开发网络专区谈天说地情感世界游戏元素分类游戏热门游戏体育运动手机专区业余爱好影视沙龙
音乐天地数码广场教育园地科学大观古今纵横谈股论金人文艺术医学保健动漫图酷二手专区地方风情各行各业

月光软件站·版权所有