从SQL Server中导入和导出Excel的基本方法

共计 5468 个字符，预计需要花费 14 分钟才能阅读完成。

这篇文章主要讲解了“从 SQL Server 中导入和导出 Excel 的基本方法”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“从 SQL Server 中导入和导出 Excel 的基本方法”吧！

从 SQL Server 中导入 / 导出 Excel 的基本方法

/*=================== 导入 / 导出 Excel 的基本方法 ===================*/

从 Excel 文件中, 导入数据到 SQL 中, 很简单, 直接用下面的语句:

/*===================================================================*/
– 如果接受数据导入的表已经存在
insert into 表 select * from
OPENROWSET(MICROSOFT.JET.OLEDB.4.0
,Excel 5.0;HDR=YES;DATABASE=c:test.xls,sheet1$)

– 如果导入数据并生成表
select * into 表 from
OPENROWSET(MICROSOFT.JET.OLEDB.4.0
,Excel 5.0;HDR=YES;DATABASE=c:test.xls,sheet1$)

/*===================================================================*/
– 如果从 SQL 数据库中, 导出数据到 Excel, 如果 Excel 文件已经存在, 而且已经按照要接收的数据创建好表头, 就可以简单的用:
insert into OPENROWSET(MICROSOFT.JET.OLEDB.4.0
,Excel 5.0;HDR=YES;DATABASE=c:test.xls,sheet1$)
select * from 表

– 如果 Excel 文件不存在, 也可以用 BCP 来导成类 Excel 的文件, 注意大小写:
– 导出表的情况
EXEC master..xp_cmdshell bcp 数据库名.dbo. 表名 out c:test.xls /c -/S 服务器名 /U 用户名 -P 密码

– 导出查询的情况
EXEC master..xp_cmdshell bcp SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname queryout c:test.xls /c -/S 服务器名 /U 用户名 -P 密码

/*– 说明:
c:test.xls 为导入 / 导出的 Excel 文件名.
sheet1$  为 Excel 文件的工作表名, 一般要加上 $ 才能正常使用.
–*/
– 上面已经说过, 用 BCP 导出的是类 Excel 文件, 其实质为文本文件,

– 要导出真正的 Excel 文件. 就用下面的方法

/*– 数据导出 EXCEL

导出表中的数据到 Excel, 包含字段名, 文件为真正的 Excel 文件
, 如果文件不存在, 将自动创建文件
, 如果表不存在, 将自动创建表
基于通用性考虑, 仅支持导出标准数据类型
– 邹建 2003.10–*/

/*– 调用示例

p_exporttb @tbname= 地区资料,@path=c:,@fname=aa.xls
–*/
if exists (select * from dbo.sysobjects where id = object_id(N[dbo].[p_exporttb]) and OBJECTPROPERTY(id, NIsProcedure) = 1)
drop procedure [dbo].[p_exporttb]
GO

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=   +;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 in(char,nchar,varchar,nvarchar) then
  text(+cast(case when a.length 255 then 255 else a.length end as varchar)+)
  when b.name in(tynyint,int,bigint,tinyint) then int
  when b.name in(smalldatetime,datetime) then datetime
  when b.name in(money,smallmoney) then money
  else b.name end
FROM syscolumns a left join systypes b on a.xtype=b.xusertype
where b.name not in(image,text,uniqueidentifier,sql_variant,ntext,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 5.0;HDR=YES
  ;DATABASE=+@path+@fname+,[+@tbname+$])

exec(insert into) select  from )

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
– 上面是导表的, 下面是导查询语句的.

/*– 数据导出 EXCEL

导出查询中的数据到 Excel, 包含字段名, 文件为真正的 Excel 文件
, 如果文件不存在, 将自动创建文件
, 如果表不存在, 将自动创建表
基于通用性考虑, 仅支持导出标准数据类型
– 邹建 2003.10–*/

/*– 调用示例

p_exporttb @sqlstr=select * from 地区资料
,@path=c:,@fname=aa.xls,@sheetname= 地区资料
–*/
if exists (select * from dbo.sysobjects where id = object_id(N[dbo].[p_exporttb]) and OBJECTPROPERTY(id, NIsProcedure) = 1)
drop procedure [dbo].[p_exporttb]
GO

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=   +;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() a
exec(@sql)

select @sql=,@fdlist=
select @fdlist=@fdlist+,[+a.name+]
,@sql=@sql+,[+a.name+]
+case when b.name in(char,nchar,varchar,nvarchar) then
  text(+cast(case when a.length 255 then 255 else a.length end as varchar)+)
  when b.name in(tynyint,int,bigint,tinyint) then int
  when b.name in(smalldatetime,datetime) then datetime
  when b.name in(money,smallmoney) then money
  else b.name end
FROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertype
where b.name not in(image,text,uniqueidentifier,sql_variant,ntext,varbinary,binary,timestamp)
and a.id=(select id from tempdb..sysobjects where)
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.

感谢各位的阅读，以上就是“从 SQL Server 中导入和导出 Excel 的基本方法”的内容了，经过本文的学习后，相信大家对从 SQL Server 中导入和导出 Excel 的基本方法这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是丸趣 TV，丸趣 TV 小编将为大家推送更多相关知识点的文章，欢迎关注！