The--SQL statement uses the following stored procedure
/*--Data Export Excel
Export the data in the query to Excel, including the field name, and the file as a true Excel file
, if the file does not exist, the file will be created automatically
, the table is created automatically if the table does not exist
Only export standard data types are supported based on commonality considerations
--Jiangjian 2003.10--*/
/*--Call Example
P_EXPORTTB @sqlstr = ' SELECT * from area information '
, @path = ' C:\ ', @fname = ' Aa.xls ', @sheetname = ' Area information '
--*/
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
Create proc P_EXPORTTB
@sqlstr sysname,--the query statement, if you use the order by in the query, add the top percent
@path nvarchar (1000),--File storage directory
@fname nvarchar (250),--filename
@sheetname varchar (250) = ' "-Name of the sheet to create, default to filename
As
declare @err int, @src nvarchar (255), @desc nvarchar (255), @out int
declare @obj int, @constr nvarchar (1000), @sql varchar (8000), @fdlist varchar (8000)
--Parameter detection
If IsNull (@fname, ') = ' Set @fname = ' Temp.xls '
If IsNull (@sheetname, ') = ' Set @sheetname =replace (@fname, '. ', ' # ')
--Check if the file already exists
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
--Database Creation statement
Set @sql = @path + @fname
if exists (select 1 from #tb where a=1)
Set @constr = ' Driver={microsoft Excel DRIVER (*.xls)};D sn= '; Readonly=false '
+'; create_db= "' + @sql + '";D bq= ' + @sql
Else
Set @constr = ' provider=microsoft.jet.oledb.4.0; Extended properties= "Excel 5.0; Hdr=yes '
+ ';D atabase= ' + @sql + ' "'
--Connecting to the database
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
--CREATE TABLE SQL
declare @tbname sysname
Set @tbname = ' # #tmp_ ' +convert (varchar (), 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 in (' char ', ' nchar ', ' varchar ', ' nvarchar ') then
' Text (' +cast (case if a.length>255 then 255 else a.length end as varchar) + ') '
When b.name in (' Tynyint ', ' int ', ' bigint ', ' tinyint ') 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 = ' 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
--Import data
Set @sql = ' OpenRowset (' MICROSOFT. JET. oledb.4.0 ', ' Excel 5.0; Hdr=yes
;D atabase= ' + @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 error number
, @src as error source, @desc as error description
Select @sql, @constr, @fdlist