A stored procedure that exports data to excel

excel| Stored Procedures | data
/*--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
Author: Jiangjian
--*/

/*--Call Example

P_EXPORTTB @sqlstr = ' SELECT * from area information '
, @path = ' C:\ ', @fname = ' Aa.xls ', @sheetname = ' Area information '
--*/



Create proc P_EXPORTTB
@tbname sysname--Name of the table to be exported
@path nvarchar (1000),--File storage directory
@fname nvarchar (250) = '--filename, default to table name
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 = @tbname + '. xls '

--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 8.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

/*--If you overwrite a table that already exists, add the following statement
--Delete table before creation/if present
Select @sql = ' drop table [' + @tbname + '] '
EXEC @err =sp_oamethod @obj, ' execute ', @out out, @sql
--*/

--Create a table 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 syscolumns a LEFT join systypes B on A.xtype=b.xusertype
where B.name not in (' image ', ' text ', ' uniqueidentifier ', ' sql_variant ', ' ntext ', ' 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

--Import data
Set @sql = ' OpenRowset (' MICROSOFT. JET. oledb.4.0 ', ' Excel 8.0; Hdr=yes
;D atabase= ' + @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 error number
, @src as error source, @desc as error description
Select @sql, @constr, @fdlist

Go


========================================
Ningoo Note:
Excel files cannot exceed 65,536 records per worksheet


Solution:

. If the amount of data is greater than 65536, you can split the exported table into several small temporary table before calling the stored procedure, and then
Export to a separate worksheet