发表于 2009-1-5 19:48
| 
SQL Server 涓 Excel
鍙戝竷: 2008-6-30 23:27 | 浣滆: admin | 鏌ョ湅: 24娆
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
--杩炴帴鍒癝QL 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
