16,722
社区成员
发帖
与我相关
我的任务
分享sqlserver表导出到excel 列名那行导不出来
我用了两种方法：1、种是读数据到datatable再一行行写到excel
2、ado模式导出
cn.Execute(" master..xp_cmdshell 'bcp threedata.dbo.table_clcl out " + Me.OpenFileDialog1.FileName + " -c -q -S" + "RD2YIQI2" + " -U" + "sa" + " -P""' ")
1方法可以导出列名，但导出速度很慢，2方法速度很快，但是没有导出列名
http://hi.baidu.com/%CE%DE%C1%C4%CB%A3%CB%A3%BF%E1/blog/item/b6a8883bedb954e315cecb2e.html在这个帖子中有办法导出来，我在sqlserver中试了，的确可以，但是列名导出在excel的最后一行。
郁闷中。。。。。。。
2、ado模式导出
cn.Execute(" master..xp_cmdshell 'bcp threedata.dbo.table_clcl out " + Me.OpenFileDialog1.FileName + " -c -q -S" + "RD2YIQI2" + " -U" + "sa" + " -P""' ")
1方法可以导出列名，但导出速度很慢，2方法速度很快，但是没有导出列名
http://hi.baidu.com/%CE%DE%C1%C4%CB%A3%CB%A3%BF%E1/blog/item/b6a8883bedb954e315cecb2e.html在这个帖子中有办法导出来，我在sqlserver中试了，的确可以，但是列名导出在excel的最后一行。
郁闷中。。。。。。。
...全文
请发表友善的回复…
发表回复
use_zj 2010-07-07
- 打赏
- 举报
--导入数据
create proc p_3
@tbname nvarchar(50), --要导出的表名
@path nvarchar(1000), --文件存放目录
@sheetname nvarchar(10)
as
declare @sql varchar(8000),@fdlist varchar(8000)
--set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES;IMEX=1
-- ;DATABASE='+@path+@fname+''',['+@tbname+'$])'
set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES
;DATABASE='+@path+''',['+@sheetname+'$])'
exec('insert into '+@sql+'(*) select * from '+@tbname)
return
go
exec p_3 ('Table_clcl','c:\temp1.xls','temp1')这是我改的 我把@fdlist改成了*，调用的时候出错
use_zj 2010-07-07
- 打赏
- 举报
/*--调用示例
我excel放在c：\temp1.xls 表是threedata.dbo.Table_clcl 这个@fdlist我是全部excel内容
导入excel 谁帮我改改？
--*/
create proc p_exporttb
@sqlstr varchar(8000), --查询语句,如果查询语句中使用了order by ,请加上top 100 percent
@path nvarchar(1000), --文件存放目录
@sheetname varchar(250)='' --要创建的工作表名,默认为文件名
as
declare @fdlist varchar(8000)
set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES
;DATABASE='+@path+@fname+''',['+@sheetname+'$])'
exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from ['+@tbname+']')
return
gouse_zj 2010-07-07
- 打赏
- 举报
/*--调用示例
我excel放在c：\temp1.xls 表是threedata.dbo.Table_clcl 这个@fdlist我是全部excel内容
导入excel 谁帮我改改？
--*/
create proc p_exporttb
@sqlstr varchar(8000), --查询语句,如果查询语句中使用了order by ,请加上top 100 percent
@path nvarchar(1000), --文件存放目录
@sheetname varchar(250)='' --要创建的工作表名,默认为文件名
as
declare @fdlist varchar(8000)
set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES
;DATABASE='+@path+@fname+''',['+@sheetname+'$])'
exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from ['+@tbname+']')
return
go
永生天地 2010-07-07
- 打赏
- 举报
你要是这个
--下面是导出真正Excel文件的方法:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_exporttb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_exporttb]
GO
/*--数据导出EXCEL
导出表中的数据到Excel,包含字段名,文件为真正的Excel文件
,如果文件不存在,将自动创建文件
,如果表不存在,将自动创建表
基于通用性考虑,仅支持导出标准数据类型
--邹建 2003.10(引用请保留此信息)--*/
/*--调用示例
p_exporttb @tbname='地区资料',@path='c:\',@fname='aa.xls'
--*/
create proc p_exporttb
@tbname sysname, --要导出的表名
@path nvarchar(1000), --文件存放目录
@fname nvarchar(250)='' --文件名,默认为表名
as
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)
--参数检测
if isnull(@fname,'')='' set @fname=@tbname+'.xls'
--检查文件是否已经存在
if right(@path,1)<>'\' set @path=@path+'\'
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql
--数据库创建语句
set @sql=@path+@fname
if exists(select 1 from #tb where a=1)
set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE'
+';CREATE_DB="'+@sql+'";DBQ='+@sql
else
set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES'
+';DATABASE='+@sql+'"'
--连接数据库
exec @err=sp_oacreate 'adodb.connection',@obj out
if @err<>0 goto lberr
exec @err=sp_oamethod @obj,'open',null,@constr
if @err<>0 goto lberr
/*--如果覆盖已经存在的表,就加上下面的语句
--创建之前先删除表/如果存在的话
select @sql='drop table ['+@tbname+']'
exec @err=sp_oamethod @obj,'execute',@out out,@sql
--*/
--创建表的SQL
select @sql='',@fdlist=''
select @fdlist=@fdlist+',['+a.name+']'
,@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
FROM syscolumns a left join systypes b on a.xtype=b.xusertype
where b.name not in('image','uniqueidentifier','sql_variant','varbinary','binary','timestamp')
and object_id(@tbname)=id
select @sql='create table ['+@tbname
+']('+substring(@sql,2,8000)+')'
,@fdlist=substring(@fdlist,2,8000)
exec @err=sp_oamethod @obj,'execute',@out out,@sql
if @err<>0 goto lberr
exec @err=sp_oadestroy @obj
--导入数据
set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES;IMEX=1
;DATABASE='+@path+@fname+''',['+@tbname+'$])'
exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from '+@tbname)
return
lberr:
exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
select cast(@err as varbinary(4)) as 错误号
,@src as 错误源,@desc as 错误描述
select @sql,@constr,@fdlist
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_exporttb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_exporttb]
GO
/*--数据导出EXCEL
导出查询中的数据到Excel,包含字段名,文件为真正的Excel文件
如果文件不存在,将自动创建文件
如果表不存在,将自动创建表
基于通用性考虑,仅支持导出标准数据类型
--邹建 2003.10(引用请保留此信息)--*/
/*--调用示例
p_exporttb @sqlstr='select * from 地区资料'
,@path='c:\',@fname='aa.xls',@sheetname='地区资料'
--*/
create proc p_exporttb
@sqlstr varchar(8000), --查询语句,如果查询语句中使用了order by ,请加上top 100 percent
@path nvarchar(1000), --文件存放目录
@fname nvarchar(250), --文件名
@sheetname varchar(250)='' --要创建的工作表名,默认为文件名
as
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)
--参数检测
if isnull(@fname,'')='' set @fname='temp.xls'
if isnull(@sheetname,'')='' set @sheetname=replace(@fname,'.','#')
--检查文件是否已经存在
if right(@path,1)<>'\' set @path=@path+'\'
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql
--数据库创建语句
set @sql=@path+@fname
if exists(select 1 from #tb where a=1)
set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE'
+';CREATE_DB="'+@sql+'";DBQ='+@sql
else
set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES'
+';DATABASE='+@sql+'"'
--连接数据库
exec @err=sp_oacreate 'adodb.connection',@obj out
if @err<>0 goto lberr
exec @err=sp_oamethod @obj,'open',null,@constr
if @err<>0 goto lberr
--创建表的SQL
declare @tbname sysname
set @tbname='##tmp_'+convert(varchar(38),newid())
set @sql='select * into ['+@tbname+'] from('+@sqlstr+') a'
exec(@sql)
select @sql='',@fdlist=''
select @fdlist=@fdlist+',['+a.name+']'
,@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
FROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertype
where b.name not in('image','uniqueidentifier','sql_variant','varbinary','binary','timestamp')
and a.id=(select id from tempdb..sysobjects where name=@tbname)
if @@rowcount=0 return
select @sql='create table ['+@sheetname
+']('+substring(@sql,2,8000)+')'
,@fdlist=substring(@fdlist,2,8000)
exec @err=sp_oamethod @obj,'execute',@out out,@sql
if @err<>0 goto lberr
exec @err=sp_oadestroy @obj
--导入数据
set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES
;DATABASE='+@path+@fname+''',['+@sheetname+'$])'
exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from ['+@tbname+']')
set @sql='drop table ['+@tbname+']'
exec(@sql)
return
lberr:
exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
select cast(@err as varbinary(4)) as 错误号
,@src as 错误源,@desc as 错误描述
select @sql,@constr,@fdlist
go
use_zj 2010-07-07
- 打赏
- 举报
computerfox
是啊 我就是一行一行的写入的 有30万条数据，所以速度很慢，这种数据的写入不知道30万数据要多长时间？？？bcp的方式瞬间就搞定
是啊 我就是一行一行的写入的 有30万条数据，所以速度很慢，这种数据的写入不知道30万数据要多长时间？？？bcp的方式瞬间就搞定
lester19872007 2010-07-07
- 打赏
- 举报
列名可以自己定义到导出！
捷哥1999 2010-07-06
- 打赏
- 举报
1方法可以导出列名，但导出速度很慢，2方法速度很快，但是没有导出列名
我估计你是一行一行写到excle的，所以慢是自然的，你要读取所有表数据到二维数组中，再一次写入！
参考：使用 Visual Studio 2005 Tools for Office （VSTO）开发C# Excel 程序
需要注意写入Excel时的效率
如果数据量比较大，最好不要逐个单元格写入，而应该使用数组写入方式：
object[,] objData = new Object[maxRowCountData, maxColumnCountData];
for (int i = 1; i < data.Length - 1; i++)
//给objData 赋值
Excel.Range CurRange = GetRange(x1, y1, x2, y2);//选择要写入的区域
CurRange.Value2 = objData;
我估计你是一行一行写到excle的，所以慢是自然的，你要读取所有表数据到二维数组中，再一次写入！
参考：使用 Visual Studio 2005 Tools for Office （VSTO）开发C# Excel 程序
需要注意写入Excel时的效率
如果数据量比较大，最好不要逐个单元格写入，而应该使用数组写入方式：
object[,] objData = new Object[maxRowCountData, maxColumnCountData];
for (int i = 1; i < data.Length - 1; i++)
//给objData 赋值
Excel.Range CurRange = GetRange(x1, y1, x2, y2);//选择要写入的区域
CurRange.Value2 = objData;
myhope88 2010-07-06
- 打赏
- 举报
没试过用sql的方法，帮顶下
use_zj 2010-07-06
- 打赏
- 举报
CREATE PROCEDURE [dbo].[sq1]
AS
BEGIN
select * into tempdb..Temptable from (
select 'h' as h,'X' as X,'Y' as Y,'Z' as Z,'gx' as gx,'gy' as gy,'gz' as gz,'△z' as [△z],'△H' as [△H],'fw' as fw,'dj' as dj,'ho' as ho
Union
all
SELECT convert(varchar,h), convert(varchar,X), convert(varchar,Y), convert(varchar,Z), convert(varchar,gx), convert(varchar,gy), convert(varchar,gz), convert(varchar,[△z]), convert(varchar,[△H]), convert(varchar,fw), convert(varchar,dj), convert(varchar,ho) FROM table_clcl
) as temptable
EXEC master..xp_cmdshell 'bcp tempdb..temptable out "c:\temp1.xls" -c -q -S"RD2YIQI2" -U"sa" -P""'
drop table tempdb..TempTable
END
调用这个存储过程 我成功的在导出的excel表头加了列行，但是再从excel导出到数据库时，原来用的
Dim conn As New ADODB.Connection
conn.Open("driver={SQL Server};server=RD2YIQI2;uid=sa;pwd=;database=threedata;")
Dim Sql As String = "insert into " & ListBox1.SelectedItem.ToString & " select * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source=""" + Me.OpenFileDialog1.FileName + """;User ID=Admin;Password=;Extended properties=Excel 5.0')...[" + "temp2" + "$] "
这样的语句行不通了，不知道怎么回事
怀疑是导出的excel编码格式不通了
大神 救救我！！！
AS
BEGIN
select * into tempdb..Temptable from (
select 'h' as h,'X' as X,'Y' as Y,'Z' as Z,'gx' as gx,'gy' as gy,'gz' as gz,'△z' as [△z],'△H' as [△H],'fw' as fw,'dj' as dj,'ho' as ho
Union
all
SELECT convert(varchar,h), convert(varchar,X), convert(varchar,Y), convert(varchar,Z), convert(varchar,gx), convert(varchar,gy), convert(varchar,gz), convert(varchar,[△z]), convert(varchar,[△H]), convert(varchar,fw), convert(varchar,dj), convert(varchar,ho) FROM table_clcl
) as temptable
EXEC master..xp_cmdshell 'bcp tempdb..temptable out "c:\temp1.xls" -c -q -S"RD2YIQI2" -U"sa" -P""'
drop table tempdb..TempTable
END
调用这个存储过程 我成功的在导出的excel表头加了列行，但是再从excel导出到数据库时，原来用的
Dim conn As New ADODB.Connection
conn.Open("driver={SQL Server};server=RD2YIQI2;uid=sa;pwd=;database=threedata;")
Dim Sql As String = "insert into " & ListBox1.SelectedItem.ToString & " select * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source=""" + Me.OpenFileDialog1.FileName + """;User ID=Admin;Password=;Extended properties=Excel 5.0')...[" + "temp2" + "$] "
这样的语句行不通了，不知道怎么回事
怀疑是导出的excel编码格式不通了
大神 救救我！！！
游子 2010-07-05
- 打赏
- 举报
我用的方法是把数据放到DATAGRID里面导出。。可以导出表头的。。
use_zj 2010-07-05
- 打赏
- 举报
[Quote=引用 1 楼 zw_548498 的回复:]
SQL 自有导出Excel功能
[/Quote]
这个我知道
SQL 自有导出Excel功能
[/Quote]
这个我知道
zw_548498 2010-07-05
- 打赏
- 举报
SQL 自有导出Excel功能
teerhu 2010-07-05
- 打赏
- 举报
[Quote=引用 4 楼 use_zj 的回复:]
引用 3 楼 nxslwh 的回复:
我用的方法是把数据放到DATAGRID里面导出。。可以导出表头的。。
速度跟不上吧！
[/Quote]
速度很快呀,只是耗費的資源比較多
引用 3 楼 nxslwh 的回复:
我用的方法是把数据放到DATAGRID里面导出。。可以导出表头的。。
速度跟不上吧！
[/Quote]
速度很快呀,只是耗費的資源比較多
use_zj 2010-07-05
- 打赏
- 举报
[Quote=引用 3 楼 nxslwh 的回复:]
我用的方法是把数据放到DATAGRID里面导出。。可以导出表头的。。
[/Quote]
速度跟不上吧！
我用的方法是把数据放到DATAGRID里面导出。。可以导出表头的。。
[/Quote]
速度跟不上吧！
