SQL导出为Excel表
来源:互联网 发布:淘宝的官换机可信吗 编辑:程序博客网 时间:2024/06/07 10:04
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) = 'c:/ImportToExcel.xls'
)
AS
DECLARE @SQLServer int,
@QueryResults int,
@CurrentResultSet int,
@object int,
@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
-- Sets the server to the local server
IF @server IS NULL SELECT @server = @@servername
-- Sets the username to the current user name
IF @uname IS NULL SELECT @uname = SYSTEM_USER
SET NOCOUNT ON
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @SQLServer OUT
IF @hr <> 0
BEGIN
PRINT 'error create SQLDMO.SQLServer'
RETURN
END
-- Connect to the 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
SELECT @result_str = 'ExecuteWithResults("' + @QueryText + '")'
EXEC @hr = sp_OAMethod @SQLServer, @result_str, @QueryResults OUT
IF @hr <> 0
BEGIN
PRINT 'error with method ExecuteWithResults'
RETURN
END
EXEC @hr = sp_OAMethod @QueryResults, 'CurrentResultSet', @CurrentResultSet OUT
IF @hr <> 0
BEGIN
PRINT 'error get CurrentResultSet'
RETURN
END
EXEC @hr = sp_OAMethod @QueryResults, 'Columns', @Columns OUT
IF @hr <> 0
BEGIN
PRINT 'error get Columns'
RETURN
END
EXEC @hr = sp_OAMethod @QueryResults, 'Rows', @Rows OUT
IF @hr <> 0
BEGIN
PRINT 'error get Rows'
RETURN
END
EXEC @hr = sp_OACreate 'Excel.Application', @object OUT
IF @hr <> 0
BEGIN
PRINT 'error create Excel.Application'
RETURN
END
EXEC @hr = sp_OAGetProperty @object, 'WorkBooks', @WorkBooks OUT
IF @hr <> 0
BEGIN
PRINT 'error create WorkBooks'
RETURN
END
EXEC @hr = sp_OAGetProperty @WorkBooks, 'Add', @WorkBook OUT
IF @hr <> 0
BEGIN
PRINT 'error with method Add'
RETURN
END
EXEC @hr = sp_OAGetProperty @object, 'Range("A1")', @Range OUT
IF @hr <> 0
BEGIN
PRINT 'error create Range'
RETURN
END
SELECT @indRow = 1
SELECT @off_Row = 0
SELECT @off_Column = 1
WHILE (@indRow <= @Rows)
BEGIN
SELECT @indColumn = 1
WHILE (@indColumn <= @Columns)
BEGIN
EXEC @hr = sp_OAMethod @QueryResults, 'GetColumnString', @result_str OUT, @indRow, @indColumn
IF @hr <> 0
BEGIN
PRINT 'error get GetColumnString'
RETURN
END
EXEC @hr = sp_OASetProperty @Range, 'value', @result_str
IF @hr <> 0
BEGIN
PRINT 'error set value'
RETURN
END
EXEC @hr = sp_OAGetProperty @Range, 'Offset', @Range OUT, @off_Row, @off_Column
IF @hr <> 0
BEGIN
PRINT 'error get Offset'
RETURN
END
SELECT @indColumn = @indColumn + 1
END
SELECT @indRow = @indRow + 1
SELECT @code_str = 'Range("A' + LTRIM(str(@indRow)) + '")'
EXEC @hr = sp_OAGetProperty @object, @code_str, @Range OUT
IF @hr <> 0
BEGIN
PRINT 'error create Range'
RETURN
END
END
SELECT @result_str = 'exec master..xp_cmdshell ''del ' + @filename + ''', no_output'
EXEC(@result_str)
SELECT @result_str = 'SaveAs("' + @filename + '")'
EXEC @hr = sp_OAMethod @WorkBook, @result_str
IF @hr <> 0
BEGIN
PRINT 'error with method SaveAs'
RETURN
END
EXEC @hr = sp_OAMethod @WorkBook, 'Close'
IF @hr <> 0
BEGIN
PRINT 'error with method Close'
RETURN
END
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
PRINT 'error destroy Excel.Application'
RETURN
END
EXEC @hr = sp_OADestroy @SQLServer
IF @hr <> 0
BEGIN
PRINT 'error destroy SQLDMO.SQLServer'
RETURN
END
GO
调用方法:
EXEC ExportToExcel @server = '.',
@uname = 'sa',
@QueryText = 'SELECT au_fname FROM pubs..authors',
@filename = 'c:/ImportToExcel.xls'
转自:http://www.oschina.net/code/snippet_59519_1800
- SQL导出为Excel表
- 导出为excel表
- SQL Server表结构导出为Excel代码
- Sql Server 数据库表查询结果导出为excel文件
- Sql结果导出为excel文件
- excel数据批量导出为sql脚本
- SQL Server 导出 excel表
- SQL Server表中数据按条件批量导出为多个Excel
- 使用PL/SQL导出数据库表中的数据为excel格式
- SQL Server表中数据导出为多个Excel文件和TXT文件
- 使用sql语句导入/导出 数据为Excel
- 将SQL Server 2005中的数据导出为Excel
- 将gridview表导出为excel表格
- 导出数据库任意表数据为EXCEL
- 把库中的表,导出为excel
- JAVA导出数据为EXCEL表
- 将jsp内容导出为Excel表
- DataGrid导出为Excel
- TextView介绍
- SQL与NoSQL——MySQL与NoSQL的融合
- 一个制作多分区SD卡的脚本
- Postfix上的反垃圾邮件的四个方法
- 有一种美叫做思考——《编程之美》书评
- SQL导出为Excel表
- 浅谈如何将NoSQL引入现有架构系统
- Hadoop跑map-reduce任务时停滞不前的问题
- 三角形面积算法
- GDB多线程调试
- 动态创建表并添加数据最后用reporting显示
- UNICODE项目整理贴
- Facebook如何管理150亿张照片
- Postfix过滤垃圾邮件