通过SQL语句直接实现Excel与数据库的导入导出

  1. 云栖社区>
  2. 博客>
  3. 正文

通过SQL语句直接实现Excel与数据库的导入导出

科技探索者 2017-11-13 23:46:00 浏览1012
展开阅读全文
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
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 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 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
本文转自问道博客51CTO博客,原文链接http://blog.51cto.com/450236/1842864如需转载请自行联系原作者                                                                        crackernet

网友评论

登录后评论
0/500
评论
科技探索者
+ 关注