|
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与数据库的导入导出
2017-11-13
2538
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《
阿里云开发者社区用户服务协议》和
《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写
侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：
目录
相关文章
|
1月前
|
SQL
缓存
监控
大厂面试高频：4 大性能优化策略（数据库、SQL、JVM等）
本文详细解析了数据库、缓存、异步处理和Web性能优化四大策略，系统性能优化必知必备，大厂面试高频。关注【mikechen的互联网架构】，10年+BAT架构经验倾囊相授。
64
6
6
|
12天前
|
SQL
关系型数据库
MySQL
MySQL导入.sql文件后数据库乱码问题
本文分析了导入.sql文件后数据库备注出现乱码的原因，包括字符集不匹配、备注内容编码问题及MySQL版本或配置问题，并提供了详细的解决步骤，如检查和统一字符集设置、修改客户端连接方式、检查MySQL配置等，确保导入过程顺利。
46
3
3
|
11天前
|
SQL
监控
安全
SQL Servers审核提高数据库安全性
SQL Server审核是一种追踪和审查SQL Server上所有活动的机制，旨在检测潜在威胁和漏洞，监控服务器设置的更改。审核日志记录安全问题和数据泄露的详细信息，帮助管理员追踪数据库中的特定活动，确保数据安全和合规性。SQL Server审核分为服务器级和数据库级，涵盖登录、配置变更和数据操作等事件。审核工具如EventLog Analyzer提供实时监控和即时告警，帮助快速响应安全事件。
29
1
1
|
22天前
|
SQL
关系型数据库
MySQL
体验使用DAS实现数据库SQL优化，完成任务可得羊羔绒加厚坐垫！
本实验介绍如何通过数据库自治服务DAS对RDS MySQL高可用实例进行SQL优化，包含购买RDS实例并创建数据库、数据导入、生成并优化慢SQL、执行优化后的SQL语句等实验步骤。完成任务，即可领取羊羔绒加厚坐垫，限量500个，先到先得。
127
11
11
|
18天前
|
SQL
存储
BI
|
18天前
|
SQL
数据库
|
23天前
|
SQL
存储
Linux
从配置源到数据库初始化一步步教你在CentOS 7.9上安装SQL Server 2019
【11月更文挑战第16天】本文介绍了在 CentOS 7.9 上安装 SQL Server 2019 的详细步骤，包括配置系统源、安装 SQL Server 2019 软件包以及数据库初始化，确保 SQL Server 正常运行。
37
4
4
|
1月前
|
SQL
存储
Linux
从配置源到数据库初始化一步步教你在CentOS 7.9上安装SQL Server 2019
【11月更文挑战第8天】本文介绍了在 CentOS 7.9 上安装 SQL Server 2019 的详细步骤，包括系统准备、配置安装源、安装 SQL Server 软件包、运行安装程序、初始化数据库以及配置远程连接。通过这些步骤，您可以顺利地在 CentOS 系统上部署和使用 SQL Server 2019。
69
1
2
|
21天前
|
SQL
Java
数据库连接
canal-starter 监听解析 storeValue 不一样，同样的sql 一个在mybatis执行 一个在数据库操作，导致解析不出正确对象
canal-starter 监听解析 storeValue 不一样，同样的sql 一个在mybatis执行 一个在数据库操作，导致解析不出正确对象
37
0
0
|
14天前
|
数据采集
数据可视化
数据挖掘
利用Python自动化处理Excel数据：从基础到进阶####
本文旨在为读者提供一个全面的指南，通过Python编程语言实现Excel数据的自动化处理。无论你是初学者还是有经验的开发者，本文都将帮助你掌握Pandas和openpyxl这两个强大的库，从而提升数据处理的效率和准确性。我们将从环境设置开始，逐步深入到数据读取、清洗、分析和可视化等各个环节，最终实现一个实际的自动化项目案例。
####
48
10
10
热门文章
最新文章
1
连接Oracle数据库失败（ORA-12514）故障排除
2
若依系统分离版去除redis数据库
3
Mysql数据库初始化后更改数据存储目录
4
使用托管代码开发WINCE自带数据库
5
Android 数据库ORM框架GreenDao学习心得及使用总结<二>
6
归档问题导致的数据库无法启动
7
EF-CodeFirst实现过程+数据库迁移
8
为什么NTFS删除超过4G大文件或数据库文件后FILE RECORD大小表现为0
9
Oracle 12c数据库升级实战
10
adb shell查看sqlite数据库
1
Java无模版导出Excel 0基础教程
110
2
使用Postman导出excel
248
3
Excel 导入 sql3
47
4
`openpyxl`是一个用于读写Excel 2010 xlsx/xlsm/xltx/xltm文件的Python库。它不需要Microsoft Excel，也不需要.NET或COM组件。
113
5
Python代码示例，读取excel表格，将行数据转为列数据。（10）
196
6
2024年新一代WebOffice内嵌网页组件，Web网页在线编辑Word/Excel/PPT
319
7
技术心得：前端点击按钮，导入excel文件，上传到后台，excel接收和更新数据
67
8
这份Excel+Python飞速搞定数据分析手册，简直可以让Excel飞起来
83
9
Python对Excel两列数据进行运算【从基础到高级的全面指南】
314
10
【Python】已解决：（pandas read_excel 读取Excel报错）ImportError: Pandas requires version ‘2.0.1’ or newer of ‘x
312