<!-- /* Font Definitions */ @font-face {font-family:宋体; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-alt:SimSun; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 135135232 16 0 262145 0;} @font-face {font-family:"/@宋体"; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 135135232 16 0 262145 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0mm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; mso-pagination:none; font-size:10.5pt; mso-bidi-font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:宋体; mso-font-kerning:1.0pt;} pre {margin:0mm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:宋体; mso-bidi-font-family:宋体;} /* Page Definitions */ @page {mso-page-border-surround-header:no; mso-page-border-surround-footer:no;} @page Section1 {size:595.3pt 841.9pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:42.55pt; mso-footer-margin:49.6pt; mso-paper-source:0; layout-grid:15.6pt;} div.Section1 {page:Section1;} -->
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'
, 'Excel 5.0;HDR=YES;DATABASE=c:/test.xls' ,sheet1$)
3 、从SQL 数据库中, 导出数据到Excel(excel 存在),
insert into OPENROWSET ( 'MICROSOFT.JET.OLEDB.4.0'
, 'Excel 5.0;HDR=YES;DATABASE=c:/test.xls' ,sheet1$)
select * from 表
4 、从SQL 数据库中, 导出数据到Excel(excel 不存在),
---- 导出表
EXEC master..xp_cmdshell 'bcp 数据库名.dbo. 表名 out "c: est.xls" /c -/S" 服务器名" /U" 用户名" -P" 密码"'
---- 导出查询语句
EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout "c: est.xls" /c -/S" 服务器名" /U" 用户名" -P" 密码"'
5 、导入导出的存储过程
-- 下面是导出真正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
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
