SQL Server 与 Excel
热 荐
【字体：小 大】
SQL Server 与 Excel
作者：- 文章来源：- 点击数：1808 更新时间：2006-4-20 
/*
存储过程名称：导出数据到Excel
功能描述：导出数据到Excel
EXEC ExportToExcel @server = '.',
@uname = 'sa',
@pwd = '',
@QueryText = 'SELECT * FROM dldata..bbbbbb',
@filename = 'd:\ImportToExcel.xls'
*/
IF OBJECT_ID('ExportToExcel') IS NOT NULL DROP PROC ExportToExcel
GO
CREATE PROCEDURE ExportToExcel (
@server sysname = null,
@uname sysname = null,
@pwd sysname = null,
@QueryText varchar(200) = null,
@filename varchar(200) = 'd:\ImportToExcel.xls'
)
AS
DECLARE @SQLServer int, --SQLDMO.SQLServer对象
@QueryResults int, --QueryResults对象
@CurrentResultSet int,
@object int, --Excel.Application对象
@WorkBooks int,
@WorkBook int,
@Range int,
@hr int,
@Columns int,
@Rows int,
@indColumn int,
@indRow int,
@off_Column int,
@off_Row int,
@code_str varchar(100),
@result_str varchar(255)
IF @QueryText IS NULL
BEGIN
PRINT 'Set the query string'
RETURN
END
--设置服务器名为本地服务器(@@servername返回运行SQL Server的本地服务器名称)
IF @server IS NULL SELECT @server = @@servername
--设置用户名为当前系统用户名(使用SYSTEM_USER返回当前系统用户名)
IF @uname IS NULL SELECT @uname = SYSTEM_USER
SET NOCOUNT ON
--创建SQLDMO.SQLServer对象
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @SQLServer OUT
IF @hr <> 0
BEGIN
PRINT 'error create SQLDMO.SQLServer'
RETURN
END
--连接到SQL Server系统
IF @pwd IS NULL
BEGIN
EXEC @hr = sp_OAMethod @SQLServer, 'Connect', null, @server, @uname
IF @hr <> 0
BEGIN
PRINT 'error Connect'
RETURN
END
END
ELSE
BEGIN
EXEC @hr = sp_OAMethod @SQLServer, 'Connect', null, @server, @uname, @pwd
IF @hr <> 0
BEGIN
PRINT 'error Connect'
RETURN
END
END