关键字 SQL Server 与 Excel
/*
存储过程名称：导出数据到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
--The ExecuteWithResults method executes a Transact-SQL command batch
--returning batch result sets in a QueryResults object
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
--The CurrentResultSet property controls access to the result sets of a
QueryResults object
EXEC @hr = sp_OAMethod @QueryResults, 'CurrentResultSet', @CurrentResultSet OUT
IF @hr <> 0
BEGIN
PRINT 'error get CurrentResultSet'
RETURN
END
--The Columns property exposes the number of columns contained
--in the current result set of a QueryResults object
EXEC @hr = sp_OAMethod @QueryResults, 'Columns', @Columns OUT
IF @hr <> 0
BEGIN
PRINT 'error get Columns'
RETURN
END
--The Rows property returns the number of rows in a referenced
--query result set or the number of rows existing in a table
EXEC @hr = sp_OAMethod @QueryResults, 'Rows', @Rows OUT
IF @hr <> 0
BEGIN
PRINT 'error get Rows'
RETURN
END
--创建Excel.Application对象
EXEC @hr = sp_OACreate 'Excel.Application', @object OUT
IF @hr <> 0
BEGIN
PRINT 'error create Excel.Application'
RETURN
END
--获得Excel工作簿对象
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
--Range对象(A1单元格)
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
--The GetColumnString method returns a QueryResults object result set
member converted to a String value
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) --如果存在@filename文件，则先删除
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
作者Blog：http://blog.csdn.net/azsoft/
相关文章
SQL Server 与 Excel
------------------------------------
一问一答：存储过程经典问题 选择自 wangnewton 的 Blog
关键字 一问一答：存储过程经典问题
出处
只涉及到一个表：xkb_treeNode
表结构是这样：
node_id int //节点id
parentNode_id int //父节点id
node_text varchar //节点内容
isModule bit //是否叶子节点
现在保存的数据有：
node_id parentNode_id node_text isModule
1 -1 语言与文学 0
2 -1 数学 0
3 -1 技术 0
4 1 语文 0
5 1 外语 0
6 5 英语 0
7 6 初中英语 0
8 7 特斯塔 1
9 4 测定是2 1
10 2 测试3 1
现在问题是：
能否通过做一个存储过程，
根据表中的isModule字段的取值（取值为1的表示最终叶子结点），
比如“特斯塔”为叶子节点，层层向上递进找到”特斯塔“的祖先节点：
特斯塔-〉初中英语-〉英语-〉外语-〉语言与文学
即通过”特斯塔“找到”语言与文学“来
最终返回的形态为：
叶子节点id 父节点id 节点名称 祖先节点名称 祖先节点id
8 7 特斯塔 语言与文学 1
9 4 测定是2 语言与文学 1
10 2 测试3 数学 2
/////////////////////////////////////////////////////////////////////////
正确答案：
--生成测试数据
create table xkb_treeNode(
node_id int,
parentNode_id int,
node_textvarchar(10),
isModulebit)
insert into xkb_treeNode select 1 ,-1,'语言与文学',0
insert into xkb_treeNode select 2 ,-1,'数学',0
insert into xkb_treeNode select 3 ,-1,'技术',0
insert into xkb_treeNode select 4 , 1,'语文',0
insert into xkb_treeNode select 5 , 1,'外语',0
insert into xkb_treeNode select 6 , 5,'英语',0
insert into xkb_treeNode select 7 , 6,'初中英语',0
insert into xkb_treeNode select 8 , 7,'特斯塔' ,1
insert into xkb_treeNode select 9 , 4,'测定是2',1
insert into xkb_treeNode select 10 , 2,'测试3',1
--创建存储过程
create procedure sp_test
as
begin
select
a.node_id,
a.parentNode_id,
a.node_text,
b.node_id as ancestor_id ,
b.node_text as ancestor_text
into
#t
from
xkb_treeNode a,xkb_treeNode b
where
a.parentNode_id = b.node_id and a.isModule = 1
while(exists(select 1 from xkb_treeNode a,#t b where a.node_id=ancestor_id and
a.parentNode_id != -1))
begin
update #t
set
ancestor_id = b.p_id,
ancestor_text = b.p_text
from
#t a,
(select
c.node_id,
d.node_id as p_id,
d.node_text as p_text
from
xkb_treeNode c,xkb_treeNode d
where
c.parentNode_id = d.node_id) b
where
a.ancestor_id = b.node_id
end
select * from #t order by node_id
end
做人要厚道，请注明转自酷网动力(www.ASPCOOL.COM)。