关于将数据导出成Excel文件的问题

[ustbwuyi]

需要在执行某存储过程之前先将表的数据导出成Excel保存,我试了两种方案,
1. 用邹建老大的存储过程p_exporttb,但是生成的Excel文件都有问题,打不开,双击打开的时候提示"test1.xls cannot be accessed, The file may be
corrupted, located on a server that is not responding,or read-only",而且无法删除,删除的时候提示被另一个用户占用,貌似仍然被SQL SERVER在占用,
无法解决.

2.用DTS,这个方式不是很熟悉,但是看了一下,在存储过程里调用DTS包貌似参数设置比较麻烦,客户估计会有怨言

谁能给个好点的方案,谢谢

本来想发200分,发不了,谁解决了到时候再发帖加分.

[ustbwuyi]

执行语句

EXEC   master..xp_cmdshell   'bcp   "select   *   from   aa"   queryout   c:temp.xls   -c   -q   -S "localhost "   -U "sa "   -P " " '

[wzy_love_sly]

把语句帖上

[wzy_love_sly]

把 库名.dbo.表写上

[wzy_love_sly]

EXEC   master..xp_cmdshell   'bcp   "select   *   from   库名.dbo.aa"   queryout   c:temp.xls   -c   -q   -S "localhost "   -U "sa "   -P " " '

[ustbwuyi]

加了还是不行,还是报这个错

[wzy_love_sly]

是sa密码空？你好象都没连上sqlserver

[wzy_love_sly]

登陆模式是混合吗？

[ustbwuyi]

登录模式没错啊,密码也没错,实际上密码是password1$,我先连的数据库引擎用sa帐号输了一遍密码进来的.

我看它报错好像也是说没连上,不知道怎么回事.但是参数又都没错

[wzy_love_sly]

语句是没问题的哦 这个是肯定的

[wzy_love_sly]

把local改成ip

[ustbwuyi]

刚试过了,改成IP也连不上,改成过(local),也改成过IP,还是这样,崩溃了

[kye_jufei]

試試這個,我這邊在windowsxp-sp3+sql2008環境下測試通過,以下是sql StoredProcedure!

USE [MES]
GO
/****** Object:  StoredProcedure [dbo].[H_SqlToExcel]    Script Date: 12/31/2008 17:07:52 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROC [dbo].[H_SqlToExcel]
(
@Path varchar(100),--文件存放路径
@Fname varchar(100),--文件名字
@SheetName varchar(80),---工作表名字
@SqlStr varchar(8000)--查询语句,如果查询语句中使用了order by ,请加上top 100 percent,注意,如果导出表/视图,用上面的存储过程 
)
AS
SET NOCOUNT ON

declare @sql varchar(8000)
declare @obj int--OLE对象
declare @constr varchar(8000)
declare @err int
declare @out int
declare @fdlist varchar(8000)
declare @tbname sysname--临时表
declare @Src nvarchar(200)
declare @Desc nvarchar(200)

set @tbname='##tmp_'+convert(varchar(38),newid())

exec('select * into ['+@tbname +'] from '+'('+@sqlStr+') A')

select @fdlist = ''

set @sql= @path+@fname
set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE'
+';CREATE_DB="'+@sql+'";DBQ='+@sql

--生成Excel的列
set @sql = ''
select @sql = @sql+','+'['+a.name+'] '+ case when b.name like '%char' then case when a.length >255 then 'memo' else 'text('+cast(a.length as varchar)+')' end
when b.name like '%int' or b.name='bit' then 'int'
when b.name like '%datetime' then 'datetime'
when b.name like '%money' then 'money'
when b.name like '%text' then 'memo'
else b.name
end, 
@fdlist = @fdlist+','+'['+a.name+']' 
from tempdb..syscolumns a join tempdb..systypes b on a.xtype = b.xusertype 
where b.name not in('image','uniqueidentifier','sql_variant','varbinary','binary','timestamp') 
and id in(select id from tempdb..sysobjects where name = @tbname) order by colorder
if @@rowcount=0 return

set @fdlist = substring(@fdlist,2,8000)

--连接数据库
exec @err=sp_oacreate 'adodb.connection',@obj out
if @err <> 0 goto lberror
exec @err=sp_oamethod @obj,'open',null,@constr
if @err <> 0 goto lberror
--创建工作薄
select @sql='create table ['+@sheetname
+']('+substring(@sql,2,8000)+')'

exec @err=sp_oamethod @obj,'execute',@out out,@sql--@sql为excute方法提供参数

if @err <> 0 goto lberror

exec @err=sp_oadestroy @obj

--导入数据
set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES
;DATABASE='+@path+@fname+''',['+@sheetname+'$])'
--print @sql
exec ('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from ['+@tbname+']')

exec('drop table ['+@tbname+']')
return


lberror:
exec sp_oageterrorinfo 0,@src out,@desc out

lbexit:
select cast(@err as varbinary(4)) as 错误号
,@src as 错误源,@desc as 错误描述
select @sql,@constr,@fdlist

[ustbwuyi]

楼上,你的生成的Excel文件和邹建老大那个有同样的问题,

打不开,双击打开的时候提示"test1.xls cannot be accessed, The file may be
corrupted, located on a server that is not responding,or read-only",而且无法删除,删除的时候提示被另一个用户占用,貌似仍然被SQL SERVER在占用

不知道怎么回事,你们那都能打开?

[claro]

帮顶

[nettman]

自己写程序将数据表中的记录逐条写入Excel表格中吧！

[ustbwuyi]

现在用BCP可以了，但是有个问题，我写了个存储过程将参数传进去，但是传进去执行不了，

EXEC master..xp_cmdshell 'bcp @querySql queryout @filePath -c -q -S @databaseIP -U @username -P @password'

这句改怎么写呢？用+号连接也不行

ALTER proc [dbo].[WriteExcel]
(
@querySql varchar(100),
@filePath varchar(100),
@databaseIP varchar(100),
@username varchar(100),
@password varchar(100)
)
as
begin

--sp_configure 'xp_cmdshell',1
--reconfigure
--go


EXEC master..xp_cmdshell 'bcp @querySql queryout @filePath -c -q -S @databaseIP -U @username -P @password'

end

[ustbwuyi]

SQL版也这么没人气了?

[fcuandy]

declare @sql varchar(1000)
set @sql = ' bcp "' + @querySql + '" queryout "' + @filepath + '" -c -q -s' + @databaseIP + ' -u' + @username + ' -p' + @password
exec master..xp_cmdshell @sql

或者用双重exec嵌套。

xp_cmdshell 后面参数为变量或常量，不支持变量拼接。

[oraclelogan]

[Quote=引用 21 楼 ustbwuyi 的回复:]
现在用BCP可以了，但是有个问题，我写了个存储过程将参数传进去，但是传进去执行不了，

EXEC master..xp_cmdshell 'bcp @querySql queryout @filePath -c -q -S @databaseIP -U @username -P @password'

这句改怎么写呢？用+号连接也不行

ALTER proc [dbo].[WriteExcel]
(
@querySql varchar(100),
@filePath varchar(100),
@databaseIP varchar(100),
@username varchar(100),
@password var…
[/Quote]

用变量传吧别用欧冠''来传。

[ustbwuyi]

谢谢各位,结贴了