从SQLServer中导入导出Excel的基本方法

从sql server导入/导出excel的基本方法

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

从excel文档中，将数据导入sql数据库。很简单。只需使用以下语句:

/*=============================================*/

-如果接受数据导入的表已经存在

插入表格select * from

openrowset(Microsoft . jet . oledb . 4.0

，excel 5.0hdr =是；database=c:test.xls，sheet1$)

-如果导入数据并生成表格。

Select * into表from

openrowset(Microsoft . jet . oledb . 4.0

，excel 5.0hdr =是；database=c:test.xls，sheet1$)

/*===========================================*/

-如果您将数据从sql数据库导出到excel，如果excel文档已经存在，并且已经根据要接收的数据创建了标题，您可以简单地使用:

插入到openrowset(Microsoft . jet . oledb . 4.0

，excel 5.0hdr =是；database=c:test.xls，sheet1$)

从表中选择*

-如果excel文档不存在，也可以使用bcp将其导入excel文档。注意大小写:

-导出表。

掌握..XP _ cmdshell BCP数据库名. dbo .表名out "c:test.xls" /c -/s "服务器名"/u "用户名"-p "密码"

-导出查询。

掌握..XP _ cmdshell BCP "select au _ fname，au _ lname from pubs..作者顺序按au _ lname " query out " 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

- */

如果存在(select * from dbo.sysobjects其中id = object_id(n[dbo])。[p_exporttb])和objectproperty(id，nisprocedure) = 1)

删除过程。[p_exporttb]

去

创建过程p_exporttb

@ tbnamesysname，-要导出的表的名称

@ pathnvarchar (1000)，-文件存储目录

@ fname nvarchar(250)=-文档名，默认为表名。

如同

声明@err int、@src nvarchar(255)、@desc nvarchar(255)、@out int

声明@obj int、@ const nvarchar(1000)、@sql varchar(8000)、@fdlist varchar(8000)

-参数检测

if isnull(@fname，)= set @fname=@tbname+。电子表格文档

-检查文档是否已经存在。

如果正确(@path，1)设置@path=@path+

创建表#tb(a位、b位、c位)

set @sql=@path+@fname

插入#tb exec master..xp_fileexist @sql

-数据库创建语句

set @sql=@path+@fname

如果存在(从#tb中选择1，其中a=1)

set @ const = driver = { Microsoft excel driver(*。xls)}；DSN =；只读=假

+;create _ db = "+；database=+@sql+"

-连接到数据库
exec @ err = sp _ oacreateadodb . connection，@ objout

if @err0转到lberr

exec @err=sp_oamethod @obj，open，null，@ constr

if @err0转到lberr

/*-如果覆盖现有的表，添加以下语句

-在创建之前删除该表/如果它存在。

select @ SQL = drop table[+@ TB name+]

exec @err=sp_oamethod @obj，execute，@out out，@sql

- */

-用于创建表的sql

select @sql=，@fdlist=

select @fdlist=@fdlist+，[+a.name+]

，@sql=@sql+，[+a.name+]

+b . name in(char，nchar，varchar，nvarchar)时的大小写，然后

文本(+强制转换(a.length>255，则255，否则a.length以varchar结尾)+)

when b.name in(tynyint，int，bigint，tinyint) then int

when b.name in(smalldatetime，datetime) then datetime

当b.name在(money，smallmoney)中时，那么money

else b.name结束

从syscolumns a左连接systypes b on a.xtype=b.xusertype

其中b.name不在(图像、文本、唯一标识符、sql_variant、ntext、varbinary、binary、时间戳)中

和object_id(@tbname)=id

select @ SQL = create table[+@ TB name

+](+子串(@sql，28000)+)

，@fdlist=substring(@fdlist，28000)

exec @err=sp_oamethod @obj，execute，@out out，@sql

if @err0转到lberr

exec @err=sp_oadestroy @obj

-导入数据

set @ SQL = openrowset(Microsoft . jet . oledb . 4.0，excel 5.0hdr =是

；database=+@path+@fname+，[+@tbname+$])

exec(insert into+@ SQL+(+@ FD list+)select+@ FD list+from+@ TB name)

返回

lberr:

exec sp_oageterrorinfo 0，@src out，@desc out

lbexit:

选择(@ err asvarbinary (4))作为错误号

，@src作为错误源，@desc作为错误描述

select @sql，@ constr，@fdlist

去

-上面是指南表，下面是指南查询语句。

/*-将数据导出到excel

将查询中的数据导出到excel，包括字段名，文档是真正的excel文档。

如果文档不存在，将自动创建。

如果该表不存在，将自动创建。

出于通用性考虑，仅支持导出标准数据类型。

-邹健，2003年10月-*/

/*-调用示例

P_exporttb @sqlstr=select *来自区域数据

，@ path = c，，@ fname = aa.xls，@ sheetname = regional data

- */

如果存在(select * from dbo.sysobjects其中id = object_id(n[dbo])。[p_exporttb])和objectproperty(id，nisprocedure) = 1)

删除过程。[p_exporttb]

去

创建过程p_exporttb

@ sqlstrvarchar (8000)，-查询语句。如果查询语句中使用了order by，请添加top 100。

@ pathnvarchar (1000)，-文件存储目录

@ fname nvarchar (250)，-文档名

@ sheet name varchar(250)=-要创建的工作表的名称，默认为文档名称。

如同

声明@err int、@src nvarchar(255)、@desc nvarchar(255)、@out int

声明@obj int、@ const nvarchar(1000)、@sql varchar(8000)、@fdlist varchar(8000)

-参数检测

if isnull(@fname，)= set @fname=temp.xls

if isnull(@sheetname，)= set @sheetname=replace(@fname，。,#)

-检查文档是否已经存在。

如果正确(@path，1)设置@path=@path+

创建表#tb(a位、b位、c位)

set @sql=@path+@fname

插入#tb exec master..xp_fileexist @sql

-数据库创建语句

set @sql=@path+@fname

如果存在(从#tb中选择1，其中a=1)

set @ const = driver = { Microsoft excel driver(*。xls)}；DSN =；只读=假

+;create _ db = "+；database=+@sql+"

-连接到数据库

exec @ err = sp _ oacreate adodb . connection，@obj out

if @err0转到lberr

exec @err=sp_oamethod @obj，open，null，@ constr

if @err0转到lberr

-用于创建表的sql

声明@tbname sysname

set @ tbname = # # tmp _+convert(varchar(38)，newid())

set @ SQL = select * into[+@ TB name+]from(+@ sqlstr+)a

exec(@sql)

select @sql=，@fdlist=

select @fdlist=@fdlist+，[+a.name+]

，@sql=@sql+，[+a.name+]

+b . name in(char，nchar，varchar，nvarchar)时的大小写，然后

文本(+强制转换(a.length>255，则255，否则a.length以varchar结尾)+)

when b.name in(tynyint，int，bigint，tinyint) then int

when b.name in(smalldatetime，datetime) then datetime

当b.name在(money，smallmoney)中时，那么money

else b.name结束

来自tempdb..syscolumns a左连接临时数据库..a.xtype=b.xusertype上的systypes b

其中b.name不在(图像、文本、唯一标识符、sql_variant、ntext、varbinary、binary、时间戳)中

和a.id=(从tempdb中选择id..sysobjects，其中name=@tbname)

select @ sql =创建表[+@sheetname

+](+子串(@sql，28000)+)

，@fdlist=substring(@fdlist，28000)

exec @err=sp_oamethod @obj，execute，@out out，@sql

if @err0转到lberr

exec @err=sp_oadestroy @obj

-导入数据

set @ SQL = openrowset(Microsoft . jet . oledb . 4.0，excel 5.0hdr =是

；database=+@path+@fname+，[+@sheetname+$])

exec(insert into+@ SQL+(+@ FD list+)select+@ FD list+from[+@ TB name+])

set @sql=drop table [+@tbname+]

exec(@sql)

返回

lberr:

exec sp_oageterrorinfo 0，@src out，@desc out

lbexit:

选择(@ err asvarbinary (4))作为错误号

，@src作为错误源，@desc作为错误描述

select @sql，@ constr，@fdlist

去