1、在SQL数据库中直接从Excel里面查询数据：
select \* from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:\\test.xls',sheet1$)
2、从Excel文件中,导入数据到SQL数据库中,
select\*into表from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel5.0;HDR=YES;DATABASE=c:\\test.xls',sheet1$)
3、从SQL数据库中,导出数据到Excel(excel存在),
insertintoOPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel5.0;HDR=YES;DATABASE=c:\\test.xls',sheet1$)
select\*from表
4、从SQL数据库中,导出数据到Excel(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"密码"'
5、导入导出的存储过程
\--下面是导出真正Excel文件的方法:(请将一下所有代码复制到存储过程中)
ifexists(select\*fromdbo.sysobjectswhereid\=object\_id(N'\[dbo\].\[p\_exporttb\]')andOBJECTPROPERTY(id,N'IsProcedure')\=1)
dropprocedure\[dbo\].\[p\_exporttb\]
GO
/\*\--数据导出EXCEL
导出表中的数据到Excel,包含字段名,文件为真正的Excel文件
,如果文件不存在,将自动创建文件
,如果表不存在,将自动创建表
基于通用性考虑,仅支持导出标准数据类型
\--邹建.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
ifexists(select\*fromdbo.sysobjectswhereid\=object\_id(N'\[dbo\].\[p\_exporttb\]')andOBJECTPROPERTY(id,N'IsProcedure')\=1)
dropprocedure\[dbo\].\[p\_exporttb\]
GO
/\*\--数据导出EXCEL
导出查询中的数据到Excel,包含字段名,文件为真正的Excel文件
如果文件不存在,将自动创建文件
如果表不存在,将自动创建表
基于通用性考虑,仅支持导出标准数据类型
\--邹建.10(引用请保留此信息)--\*/
/\*\--调用示例
p\_exporttb@sqlstr='select\*from地区资料'
,@path='c:',@fname='aa.xls',@sheetname='地区资料'
\--\*/
createprocp\_exporttb
@sqlstrvarchar(8000),\--查询语句,如果查询语句中使用了orderby,请加上top100percent
@pathnvarchar(1000),\--文件存放目录
@fnamenvarchar(250),\--文件名
@sheetnamevarchar(250)\=''\--要创建的工作表名,默认为文件名
as
declare@errint,@srcnvarchar(255),@descnvarchar(255),@outint
declare@objint,@constrnvarchar(1000),@sqlvarchar(8000),@fdlistvarchar(8000)
\--参数检测
ifisnull(@fname,'')\=''set@fname\='temp.xls'
ifisnull(@sheetname,'')\=''set@sheetname\=replace(@fname,'.','#')
\--检查文件是否已经存在
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
\--创建表的SQL
declare@tbnamesysname
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
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
FROMtempdb..syscolumnsaleftjointempdb..systypesbona.xtype\=b.xusertype
whereb.namenotin('image','uniqueidentifier','sql\_variant','varbinary','binary','timestamp')
anda.id\=(selectidfromtempdb..sysobjectswherename\=@tbname)
if@@rowcount\=0return
select@sql\='createtable\['+@sheetname
+'\]('+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
;DATABASE='+@path+@fname+''',\['+@sheetname+'$\])'
exec('insertinto'+@sql+'('+@fdlist+')select'+@fdlist+'from\['+@tbname+'\]')
set@sql\='droptable\['+@tbname+'\]'
exec(@sql)
return
lberr:
execsp\_oageterrorinfo0,@srcout,@descout
lbexit:
selectcast(@errasvarbinary(4))as错误号
,@srcas错误源,@descas错误描述
select@sql,@constr,@fdlist
go
