SET
QUOTED_IDENTIFIER
ON
GO
SET
ANSI_NULLS
ON
GO
ALTER
proc
stp_ExportTable
@sqlstr
nvarchar
(
4000
),
--
查询语句,如果查询语句中使用了order by ,请加上top 100 percent,注意,如果导出表/视图,用上面的存储过程
@orderstr
nvarchar
(
255
),
--
Order by Field
@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
(
4000
),
@fdlist
varchar
(
8000
)
declare
@IstmpTB
as
bit
declare
@tmpsql
as
varchar
(
4000
)
SET
@IstmpTB
=
0
--
参数检测
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
+
'
"
'
--
连接数据库
--
print 'nn33'
exec
@err
=
sp_oacreate
'
adodb.connection
'
,
@obj
out
if
@err
<>
0
goto
lberr
--
print 'nn44'
exec
@err
=
sp_oamethod
@obj
,
'
open
'
,
null
,
@constr
if
@err
<>
0
goto
lberr
--
构造temp表的SQL
declare
@tbname
sysname
set
@tbname
=
'
##tmp_
'
+
convert
(
varchar
(
38
),
newid
())
set
@sql
=
'
select * into [
'
+
@tbname
+
'
] from(
'
+
@sqlstr
+
'
) a
'
--
print @sql
exec
(
@sql
)
set
@IstmpTB
=
1
select
@sql
=
''
,
@fdlist
=
''
select
@fdlist
=
@fdlist
+
'
,[
'
+
a.name
+
'
]
'
,
@sql
=
@sql
+
'
,[
'
+
a.name
+
'
]
'
+
case
when
b.name
in
(
'
char
'
,
'
nchar
'
,
'
varchar
'
,
'
nvarchar
'
)
then
'
text(
'
+
cast
(
case
when
a.length
>
255
then
255
else
a.length
end
as
varchar
)
+
'
)
'
when
b.name
in
(
'
bit
'
,
'
int
'
,
'
bigint
'
,
'
tinyint
'
,
'
smallint
'
)
then
'
int
'
when
b.name
in
(
'
smalldatetime
'
,
'
datetime
'
)
then
'
datetime
'
when
b.name
in
(
'
money
'
,
'
smallmoney
'
)
then
'
money
'
else
b.name
end
FROM
tempdb..syscolumns a
left
join
tempdb..systypes b
on
a.xtype
=
b.xusertype
where
b.name
not
in
(
'
image
'
,
'
text
'
,
'
uniqueidentifier
'
,
'
sql_variant
'
,
'
ntext
'
,
'
varbinary
'
,
'
binary
'
,
'
timestamp
'
)
and
a.id
=
(
select
id
from
tempdb..sysobjects
where
name
=
@tbname
)
select
@sql
=
substring
(
@sql
,
2
,
2000
),
@fdlist
=
substring
(
@fdlist
,
2
,
2000
)
--
create table
select
@sql
=
'
create table [
'
+
@sheetname
+
'
](
'
+
@sql
+
'
)
'
--
print @sql
exec
@err
=
sp_oamethod
@obj
,
'
execute
'
,
@out
out,
@sql
if
@err
<>
0
goto
lberr
--
print 'nn'
--
destroy ole object
exec
@err
=
sp_oadestroy
@obj
if
@err
<>
0
goto
lberr
--
print 'nn1'
--
导入数据
set
@sql
=
'
openrowset(
''
MICROSOFT.JET.OLEDB.4.0
''
,
''
Excel 8.0;HDR=YES
;DATABASE=
'
+
@path
+
@fname
+
'''
,[
'
+
@sheetname
+
'
$])
'
set
@tmpsql
=
'
insert into
'
+
@sql
+
'
(
'
+
@fdlist
+
'
) select
'
+
@fdlist
+
'
from [
'
+
@tbname
+
'
]
'
--
print @tmpsql
if
@orderstr
is
not
null
or
@orderstr
<>
''
begin
set
@tmpsql
=
@tmpsql
+
'
order by
'
+
@orderstr
end
--
print @tmpsql
exec
(
@tmpsql
)
set
@sql
=
'
drop table [
'
+
@tbname
+
'
]
'
exec
(
@sql
)
set
@IstmpTB
=
0
return
0
lberr:
EXEC
sp_displayoaerrorinfo
@obj
,
@err
--
DELETE TmpTable While Error
IF
@IstmpTB
=
1
BEGIN
set
@sql
=
'
drop table [
'
+
@tbname
+
'
]
'
exec
(
@sql
)
END
return
-
1
lbexit:
SELECT
@sql
,
@constr
,
@fdlist
GO
SET
QUOTED_IDENTIFIER
OFF
GO
SET
ANSI_NULLS
ON
GO
