数据库导入导出Excel

--从Excel文件中,导入数据到SQL数据库中,很简单,直接用下面的语句:
/**//**//**//*===================================================================*/
--如果接受数据导入的表已经存在
insertinto表select*from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel5.0;HDR=YES;DATABASE=c: est.xls',sheet1$)

--如果导入数据并生成表
select*into表from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel5.0;HDR=YES;DATABASE=c: est.xls',sheet1$)

--补充几句如果系统显示
/**//*

SQLServer阻止了对组件'AdHocDistributedQueries'的STATEMENT'OpenRowset/OpenDatasource'的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用sp_configure启用'AdHocDistributedQueries'。有关启用'AdHocDistributedQueries'的详细信息，请参阅SQLServer联机丛书中的"外围应用配置器"。
因为SQL2005默认是没有开启'AdHocDistributedQueries'组件，开启方法如下*/


EXECsp_configure'showadvancedoptions',1
GO
RECONFIGURE
GO
EXECsp_configure'AdHocDistributedQueries',1
GO
RECONFIGURE
GO


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


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

--导出查询的情况
EXECmaster..xp_cmdshell'bcp"SELECTau_fname,au_lnameFROMpubs..authorsORDERBYau_lname"queryout"c: est.xls"/c-/S"服务器名"/U"用户名"-P"密码"'

/**//**//**//*--说明:
c: est.xls为导入/导出的Excel文件名.
sheet1$为Excel文件的工作表名,一般要加上$才能正常使用.
--*/


--下面是导出真正Excel文件的方法:

ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[p_exporttb]')andOBJECTPROPERTY(id,N'IsProcedure')=1)
dropprocedure[dbo].[p_exporttb]
GO

/**//**//**//*--数据导出EXCEL

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

--邹建2003.10(引用请保留此信息)--*/

/**//**//**//*--调用示例

p_exporttb@tbname='地区资料',@path='c:',@fname='aa.xls'
--*/
createprocp_exporttb
@tbnamesysname,--要导出的表名
@pathnvarchar(1000),--文件存放目录
@fnamenvarchar(250)=''--文件名,默认为表名
as
declare@errint,@srcnvarchar(255),@descnvarchar(255),@outint
declare@objint,@constrnvarchar(1000),@sqlvarchar(8000),@fdlistvarchar(8000)

--参数检测
ifisnull(@fname,'')=''set@fname=@tbname+'.xls'

--检查文件是否已经存在
ifright(@path,1)<>''set@path=@path+''
createtable#tb(abit,bbit,cbit)
set@sql=@path+@fname
insertinto#tbexecmaster..xp_fileexist@sql

--数据库创建语句
set@sql=@path+@fname
ifexists(select1from#tbwherea=1)
set@constr='DRIVER={MicrosoftExcelDriver(*.xls)};DSN='''';READONLY=FALSE'
+';CREATE_DB="'+@sql+'";DBQ='+@sql
else
set@constr='Provider=Microsoft.Jet.OLEDB.4.0;ExtendedProperties="Excel8.0;HDR=YES'
+';DATABASE='+@sql+'"'


--连接数据库
exec@err=sp_oacreate'adodb.connection',@objout
if@err<>0gotolberr

exec@err=sp_oamethod@obj,'open',null,@constr
if@err<>0gotolberr

/**//**//**//*--如果覆盖已经存在的表,就加上下面的语句
--创建之前先删除表/如果存在的话
select@sql='droptable['+@tbname+']'
exec@err=sp_oamethod@obj,'execute',@outout,@sql
--*/

--创建表的SQL
select@sql='',@fdlist=''
select@fdlist=@fdlist+',['+a.name+']'
,@sql=@sql+',['+a.name+']'
+case
whenb.namelike'%char'
thencasewhena.length>255then'memo'
else'text('+cast(a.lengthasvarchar)+')'end
whenb.namelike'%int'orb.name='bit'then'int'
whenb.namelike'%datetime'then'datetime'
whenb.namelike'%money'then'money'
whenb.namelike'%text'then'memo'
elseb.nameend
FROMsyscolumnsaleftjoinsystypesbona.xtype=b.xusertype
whereb.namenotin('image','uniqueidentifier','sql_variant','varbinary','binary','timestamp')
andobject_id(@tbname)=id
select@sql='createtable['+@tbname
+']('+substring(@sql,2,8000)+')'
,@fdlist=substring(@fdlist,2,8000)
exec@err=sp_oamethod@obj,'execute',@outout,@sql
if@err<>0gotolberr

exec@err=sp_oadestroy@obj

--导入数据
set@sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel8.0;HDR=YES;IMEX=1
;DATABASE='+@path+@fname+''',['+@tbname+'$])'

exec('insertinto'+@sql+'('+@fdlist+')select'+@fdlist+'from'+@tbname)

return

lberr:
execsp_oageterrorinfo0,@srcout,@descout
lbexit:
selectcast(@errasvarbinary(4))as错误号
,@srcas错误源,@descas错误描述
select@sql,@constr,@fdlist
go