|
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
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
本文转自问道博客51CTO博客，原文链接http://blog.51cto.com/450236/1842864如需转载请自行联系原作者 crackernet
|
通过SQL语句直接实现Excel与数据库的导入导出
扫一扫
1619
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
