袖珍拾遗（转贴来源于流水无声）
关键字 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
