Export to EXCEL with column names

In the post Import/Export to Excel, I showed how to export data to EXCEL
The problem that most users find it is it wont include column names in the file and file should exists already with headings

This procedure would solve that problem

 

create procedure proc_generate_excel_with_columns
(
        @db_name varchar(100
),
        @table_name varchar(100), 
        @file_name varchar(100
)
)

as

--Generate column names as a recordset

declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)

select 
        @columns=coalesce(@columns+',','')+column_name+' as '+column_name
from 
        information_schema.columns
where 
        table_name=@table_name

select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''
exec(@sql)

--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''
exec(@sql)

--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> '+@file_name+''''
exec(@sql)

--Delete dummy file
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec(@sql
)

After creating the procedure, execute it by supplying database name, table name and file path
EXEC proc_generate_excel_with_columns 'your dbname', 'your table name',
'your file path'

For more informations and to know how to solve the errors, refer this thread Export to Excel

Published 10 October 2008 15:55 by Madhivanan

Comments

# re: Export to EXCEL with column names

04 June 2010 09:02 by sfqfirst

When The resords are more than 65536 for Excel2003,you may need use another "Sheet" save them.

That is my some codes for this problem:

Sorry,I don't translate them to English language.

-----------------------------------------------------

IF OBJECT_ID ( 'p_QueryToExcel', 'P' ) IS NOT NULL

   Drop PROCEDURE p_QueryToExcel;

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

/*--数据导出EXCEL

--创建者:邹建   2003.10(引用请保留此信息)--

--功能描述:

1、导出查询中的数据到Excel,包含字段名,文件为真正的Excel文件

2、如果文件不存在,将自动创建文件

3、如果表不存在,将自动创建表

4、基于通用性考虑,仅支持导出标准数据类型

--修改记录

--shifq 2008-05-28

1、修改邹建的错误:关于provider的书写

在openrowset时为database

在ADODb.connection.open时为data source,一个很不容易找到的错误,否则0x80004005 Microsoft JET Database Engine 不能执行选定查询

2、执行过程过长时,中途无法取消

3、一些限制

--Jet SQL无法修改列名,应使用ADOX.Catagory

--SQLServer2005索引最大为1284 bytes,order by 最大为8060 bytes

--grant exec分配不了权限

--不能对已存在的Excel操作

4、要对@varQuerySQL中结果集排序,请在@varOrderBy指定结果集中的列,存储过程会按此排序,不要再在@varQuerySQL中再排序

--shifq 2010-04-26

1、支持对已存在Excel文件进行创建Sheet、插入数据操作

2、小于单个Sheet最大记录时,不在Sheet名后增加1

3、一些限制

--只能对Excel的已存在的Sheet做追加操作,不能做Delete From Table/Drop Table操作

--当对Excel的已存在的Sheet做Create Table操作时,会锁死该Excel文件

*/

/*--调用示例--

--表的联合

 p_QueryToExcel   @varQuerySQL= 'select top 365000 A.* from a inner join B on A.a1=B.a1'

,@varExcelFullPath= 'D:\Temp\aa31.xls',@varSheetName= 'sheet'

*/

CREATE    proc   [dbo].[p_QueryToExcel]

@varQuerySQL varchar(8000), --@varQuerySQL:查询语句,各列必须有名称,没有的需要别名,由于

--如果查询语句中使用了order by,请加上top 100 percent

--对是否包含where分类限制不做要求

@varExcelFullPath nvarchar(4000), --@varExcelFullPath:生成Excel的完整路径

@varSheetName varchar(512) = 'sheet',

@varOrderBy varchar(512) = '类目', --必须是@varQuerySQl中的列名

@varArrayHiddenCols varchar(1024) = 'TopIdentitier,DocID',

@intAppendSheetName SMALLINT = 1 --扩展SheetName:0-不追加,1-追加,仅在<@intOneSheetMaxNumber有效

as

begin

--声明和初始化变量

declare @varSheetName_pre varchar(1024)

declare @intOneSheetMaxNumber int

declare @intRecCount int

declare @sinSheetNumber smallint

declare @sinCircle int

declare @varTempSQL varchar(8000)

declare @varTempSQL2 varchar(8000)

set @intOneSheetMaxNumber = 50000

set @sinSheetNumber = 1

set @varExcelFullPath = convert(varchar(8000),ltrim(rtrim(@varExcelFullPath)))  

--Notes:CHARINDEX performs comparisons based on the collation of the input. To perform a comparison in a specified collation, you can use COLLATE to apply an explicit collation to the input.

--set @varQuerySQL = lower(ltrim(rtrim(@varQuerySQL)))

set @varQuerySQL = ltrim(rtrim(@varQuerySQL))

set @sinCircle = 0

set @varSheetName_pre = @varSheetName

set @varOrderBy = ltrim(rtrim(@varOrderBy))

--set @varArrayHiddenCols = lower(ltrim(rtrim(@varArrayHiddenCols)))

set @varArrayHiddenCols = ltrim(rtrim(@varArrayHiddenCols))

--select top 1 1 from ast_document where metaid not in ('CategoryTypeID','DocID')

set @varArrayHiddenCols = replace(@varArrayHiddenCols,',',',') --CategoryTypeID,DocID

print @varArrayHiddenCols

-- set @varOrderByWithNoAliasTable = ltrim(rtrim(@varOrderByWithNoAliasTable))

-- --@varQuerySQL的别名这里取AAA

-- if @varOrderByWithNoAliasTable is null or @varOrderByWithNoAliasTable = ''

-- set @varOrderBy = 'Order by AAA.CategoryTypeid, AAA.HiberarchyCode'

-- else

-- set @varOrderBy = 'AAA.' + replace(@varOrderByWithNoAliasTable,',' ,',AAA.' )

/*=================================检测参数有效性====================*/

----判断@varExcelFullPath

if (@varExcelFullPath is null) or (@varExcelFullPath='')

begin

RAISERROR ('Excel文件路径不能为空。',1,1)

return 50001

end

----判断@varQuerySQL

if (@varQuerySQL is null) or (@varQuerySQL = '')

begin

RAISERROR ('查询语句不能为空。',1,2)

return 50001

end

----判断@varQuerySQL 'SQL语句'

----假设用户没有恶意调用

set @varTempSQL = @varQuerySQL

if left(@varTempSQL,1) = '('

begin

RAISERROR ('不能将整个SQL语句用括号包起来。',2,16)

return 50002

end

if charindex('select ',@varTempSQL,1) = 0

begin

RAISERROR ('Error 缺少select语句。',2,16)

return 50002

end

if charindex(' aaa',@varTempSQL,1) > 0 or charindex(' bbb',@varTempSQL,1) > 0 or charindex(' myrownumber',@varTempSQL,1) > 0

begin

RAISERROR ('请使用AAA、BBB、MyRowNumber以外的别名。',2,16)

return 50002

end

if left(@varTempSQL,1) = '('

begin

RAISERROR ('不能将整个SQL语句用括号包起来。',2,16)

return 50002

end

--针对'

   --set @varQuerySQL = replace(@varQuerySQL,char(39),char(39)+char(39))

/*

if charindex(char(39),@varTempSQL,1) > 0

begin

--RAISERROR ('Error 传输的SQL语句不能包含英文撇,请使用''代替。',3,17)

--return 50002

end

*/

if @varOrderBy is null or @varOrderBy =''

begin

RAISERROR ('排序不能为空。',1,5)

return 50001

end

else

begin

set @varOrderBy = 'BBB.' + replace(@varOrderBy,',',',BBB.')

end

print @varOrderBy

--exec ('select 1 from (select * from ast_document where docid < 0) as A')

--select 1以便得到count记录数,保证@varQuerySQL不包含AAA别名

set @varTempSQL = 'select 1 from (' + @varQuerySQL + ') as AAA'

exec (@varTempSQL)

--@@rowcount在下一个begin...end之后就成为0

set @intRecCount = @@rowcount

if @intRecCount = 0

begin

RAISERROR ('查询记录集为空。',1,5)

return 50001

end

print @intRecCount

declare @varTopCategoryCode varchar(256)

declare @varTopCategoryName varchar(512)

declare @err int, @src varchar(255), @desc varchar(255), @out int --Error跟踪

declare @obj int, @constr varchar(1000), @fdlist varchar(8000), @fdlist_AAA varchar(8000)

declare @tbname   sysname

--检查Excel文件是否已经存在

create table #tb(a bit,b bit,c bit)

set  @varExcelFullPath = ltrim(rtrim(@varExcelFullPath))

insert   into   #tb  

exec   master..xp_fileexist   @varExcelFullPath

print 'xp_fileexist'+@varExcelFullPath

--select * from #tb

/*

xp_fileexist   返回的三个列,       分别代表

文件已存在       文件是目录       父目录已存在      

-----       -----       ------      

0                       0                       1      

*/

/*

declare @saveas varchar(2048),@sheet int

set @saveas = 'ActiveWorkbook.SaveAs("'+@varExcelFullPath+'")'

exec @err = sp_oacreate 'excel.application' ,@obj output

if   @err <> 0   goto   lberr

exec @err = sp_oamethod @obj ,'workbooks.add' ,@sheet output

if   @err <> 0   goto   lberr

exec @err = sp_oamethod @obj ,@saveas

if   @err <> 0   goto   lberr

--exec @err = sp_oamethod @obj ,'ActiveWorkbook.Save'

if   @err <> 0   goto   lberr

exec @err = sp_oamethod @obj ,'Workbooks.Close'

if   @err <> 0   goto   lberr

exec @err = sp_oamethod @obj ,'quit'

exec @err = sp_oadestroy @obj

return

*/

--数据库创建语句

set @varTempSQL = @varExcelFullPath

if exists(select 1 from #tb where a=1)

begin

--set   @constr= 'DRIVER={Microsoft Excel Driver (*.xls)};DSN= '''';READONLY=FALSE '

--   + ';CREATE_DB= "'+@sql+ '";DBQ='+@sql

--RAISERROR ('暂不支持对已经存在的Excel,做导出操作。',1,5)

--return 50001

set @constr= 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+@varExcelFullPath+ ';Extended Properties="Excel 8.0;HDR=YES"'

end

else

set @constr= 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+@varExcelFullPath+ ';Extended Properties="Excel 8.0;HDR=YES"'

--set   @constr= 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source='+@varExcelFullPath+ ';Extended Properties="Excel 12.0;HDR=YES"'

--连接数据库

EXEC @err=sp_OACreate 'ADODB.Connection', @obj OUT

print '连接数据库1'+convert(varchar(50),@obj)

if   @err <> 0   goto   lberr

EXEC @err=sp_OASetProperty @obj,'ConnectionString', @constr

if   @err <> 0   goto   lberr

exec @err=sp_oamethod @obj, 'Open'

print '连接数据库2'+@constr

if   @err <> 0   goto   lberr

print '创建临时表1'

--创建相同表结构的临时表

set   @tbname= '##tmp_'+convert(varchar(38),newid())

set   @varTempSQL= 'select top 1 AAA.* into ['+@tbname+ '] from ('+@varQuerySQL+ ') AAA'

print '创建临时表2'+@varTempSQL

exec(@varTempSQL)

set @varTempSQL = ''

set @fdlist = ''

set @fdlist_AAA = ''

select   @fdlist=@fdlist+ ',['+a.name+']'

 ,@varTempSQL=@varTempSQL+',['+a.name+'] '

+case  

 when   b.name   like  '%char'  

 then   case   when   a.length> 255   then  'text(255)'

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)

and charindex(a.name, @varArrayHiddenCols)=0

select   @fdlist_AAA=@fdlist_AAA+ ',AAA.['+a.name+']'  

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)

and charindex(a.name, @varArrayHiddenCols)=0

print @fdlist + ' ' +@fdlist_AAA

print'转换数据类型'

/*=====================分页插入Excel的Sheet=======================*/

--如果不按分类存放

--获得记录总数@intRecCount,上面判断时已经获得

----设置Sheet名称

----判断@intRecCount与@intOneSheetMaxNumber大小,定义Sheet名称后

----分页插入各个Sheet

--如果<=@intOneSheetMaxNumber

if @intRecCount <= @intOneSheetMaxNumber

begin

IF @intAppendSheetName = 0

BEGIN

set @varSheetName = @varSheetName_pre

END

ELSE

set @varSheetName = @varSheetName_pre + '1'

--直接创建Excel和导入数据

--"Microsoft Jet 数据库引擎找不到对象'sheet1$'。请确定对象是否存在,并正确地写出它的名称和路径。".

--以独占方式创建Excel.Application对象失败,文件仍然损坏

/*

declare @object int

,@hr int

,@varSheetName2 varchar(255)

,@workbook int

,@varTemp varchar(4000)

exec @hr = master.dbo.sp_OACreate 'Excel.Application', @object out

exec @hr = master.dbo.sp_oaMethod @Object,'WorkBooks.Open',@workbook out,@varExcelFullPath

SET @varTemp = 'Workbooks(1).Worksheets("'+ RTRIM(LTRIM(@varSheetName)) +'").Name'

exec @hr = master.dbo.sp_OAGetProperty @object, @varTemp,@varSheetName2 OUTPUT

PRINT '@varSheetName2:'+@varSheetName2

IF @varSheetName2 = @varSheetName

BEGIN

RAISERROR ('Sheet已存在!',1,1)

PRINT '判断Sheet是否存在:'+@varQuerySQL

RETURN 50002

END

exec @hr = master.dbo.sp_oaMethod @Object,'Application.Quit'

exec @hr = master.dbo.sp_OADestroy @workbook

exec @hr = master.dbo.sp_OADestroy @object

*/

--SET @varQuerySQL = 'IF exists (

--SELECT *

--FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',

--''Excel 8.0;Database=' + @varExcelFullPath + ';User=Admin;Password='',

--''SELECT * FROM ['+ @varSheetName +'$]'') )

--RAISERROR (''Sheet已存在!'',1,1)

--'

--PRINT '判断Sheet是否存在:'+@varQuerySQL

--EXEC(@varQuerySQL)

--IF @@ERROR > 0

--BEGIN

-- PRINT 'Sheet已存在!'

-- RETURN 50002

--END

--IF @@ROWCOUNT >0

--BEGIN

-- --set @varTempSQL = 'DROP TABLE  ['+@varSheetName + ']'

-- --print '准备删除Table '+@varTempSQL

-- --exec   @err=sp_oamethod   @obj, 'Execute',@out out,@varTempSQL

-- --if   @err <> 0   goto   lberr

--END

set @varTempSQL= 'create table ['+@varSheetName + '] ('

+ substring(@varTempSQL,2,8000)+ ')'

set @fdlist=substring(@fdlist,2,8000)

set @fdlist_AAA=substring(@fdlist_AAA,2,8000)

print '准备创建Table '+@varTempSQL

EXEC @err = sp_OAMethod @obj, 'Execute',@out out, @varTempSQL

if   @err <> 0   goto   lberr

--EXEC @err = sp_OAMethod @obj, 'Execute',@out out, @varTempSQL, NULL, 129

--IF @err = 0x80040E14

--BEGIN

-- /* --没有作用

-- set @varTempSQL2 = 'DELETE * FROM ['+@varSheetName + ']'

-- print '准备删除Table '+@varTempSQL2

-- exec   @err=sp_oamethod   @obj, 'Execute',@out out,@varTempSQL2

-- if   @err <> 0   goto   lberr

-- */

-- set @varTempSQL2 = 'DROP TABLE  ['+@varSheetName + ']'

-- print '准备删除Table '+@varTempSQL2

-- exec   @err=sp_oamethod   @obj, 'Execute',@out out,@varTempSQL2

-- if   @err <> 0   goto   lberr

-- exec   @err=sp_oamethod   @obj, 'Execute',@out out,@varTempSQL

-- if   @err <> 0   goto   lberr

--END

--为导入数据

set   @varTempSQL= 'openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=Yes;database='+@varExcelFullPath+ ''',['+@varSheetName+ '$])'

--set   @varTempSQL= 'openrowset(''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0;HDR=Yes;database='+@path+@fname+ ''',''select * from ['+@varSheetName+ '$]'')'

print '导入数据:'+'insert into '+@varTempSQL+ '('+@fdlist+ ')  select '+@fdlist_AAA+ ' from ('+@varQuerySQL+ ') as AAA'

exec('insert into '+@varTempSQL+ '('+@fdlist+ ')  select '+@fdlist_AAA+ ' from ('+@varQuerySQL+ ') as AAA')

end

--如果>@intOneSheetMaxNumber

else

begin

set @sinSheetNumber = @intRecCount / @intOneSheetMaxNumber

+ (case (@intRecCount % @intOneSheetMaxNumber) when 0 then 0 else 1 end)

set @fdlist=substring(@fdlist,2,8000)

set @fdlist_AAA=substring(@fdlist_AAA,2,8000)

while ( @sinCircle < @sinSheetNumber )

begin

set @varSheetName = @varSheetName_pre + convert(varchar(50),@sinCircle+1)

print '第 ' + convert(varchar(50),@sinCircle+1) + ' 个Sheet / 总共 ' + convert(varchar(50),@sinSheetNumber) + ' 个Sheet'

--直接创建Excel和导入数据

set @varTempSQL2 = ''

select   @varTempSQL2= 'create table ['+@varSheetName

 + '] ('+substring(@varTempSQL,2,8000)+ ')'

print '准备创建Table ' + @varTempSQL2

exec   @err=sp_oamethod   @obj, 'Execute',@out out,@varTempSQL2

print 'create table :'+@varTempSQL

if   @err <> 0   goto   lberr

--为导入数据

/*

WITH OrderedTable AS

(

SELECT BBB.*,ROW_NUMBER() OVER (ORDER BY Docid) AS 'MyRowNumber'

FROM (@varQuerySQL)  as BBB

)

insert into openrowset() (@fdlist)

SELECT @fdlist

FROM OrderedTable

WHERE MyRowNumber BETWEEN (@intOneSheetMaxNumber * @sinCircle) AND (@intOneSheetMaxNumber * (@sinCircle+1))

*/

--set   @varTempSQL= 'openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=Yes;database='+@varExcelFullPath+ ''',['+@varSheetName+ '$])'

--set   @varTempSQL= 'openrowset(''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0;HDR=Yes;database='+@path+@fname+ ''',''select * from ['+@varSheetName+ '$]'')'

set @varTempSQL2 = 'WITH OrderedTable AS

(

SELECT BBB.*,ROW_NUMBER() OVER (ORDER BY '+@varOrderBy +') AS ''MyRowNumber''

FROM (' + @varQuerySQL + ')  as BBB

)

insert into openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=Yes;database='+@varExcelFullPath+ ''',['+@varSheetName+ '$])'

+ ' select ' + @fdlist + ' FROM OrderedTable WHERE MyRowNumber BETWEEN ' + convert(varchar(50),@intOneSheetMaxNumber * @sinCircle+1) + ' AND ' + convert(varchar(50),@intOneSheetMaxNumber * (@sinCircle+1))

print '导入数据:'+@varTempSQL2

exec(@varTempSQL2)

set @sinCircle = @sinCircle + 1

end

end

--关闭和释放OA对象

EXEC @err=sp_OAMethod @obj, 'Close'

EXEC @err=sp_OAMethod @obj, 'Dispose'

print 'sp_oadestroy'+convert(varchar(50),@obj)

EXEC @err=sp_OADestroy @obj

set   @varTempSQL= 'drop table ['+@tbname+ ']'

print 'drop table '+@varTempSQL

exec(@varTempSQL)

return

lberr:

 exec   sp_oageterrorinfo   0,@src   out,@desc   out

lbexit:

 select   cast(@err   as   varbinary(4))   as   错误号

,@src   as   错误源,@desc   as   错误描述

 select   @varTempSQL,@constr,@fdlist

end