SQL Server带列名导出到Excel(Export to CSV with headers)的几个思路

SQL Server 2008中SQL应用系列及BI学习笔记系列--目录索引

今天在项目中遇到一个问题，需要从SQL Server导出表到Excel，但需要带列名。尝试了几种方法，并小结如下：

假定表如下：

USE testDb2GOIF NOT OBJECT_ID('Demo_A') IS NULLDROP TABLE [Demo_A]/****** Object: Table [dbo].[Demo_A] downmoon:[e-mail] ******/CREATE TABLE [dbo].[Demo_A]([ID] int not null,[Name] [Nvarchar](20) NOT NULL)GOINSERT [dbo].[Demo_A]SELECT 1,'郭靖'union ALL SELECT 2,'胡一刀'union ALL SELECT 3,'令狐冲'GO

如果通常的思路，我们可以用BCP，命令如下：

-- To allow advanced options to be changed.EXEC sp_configure 'show advanced options', 1GO-- To update the currently configured value for advanced options.RECONFIGUREGO-- To enable the feature.EXEC sp_configure 'xp_cmdshell', 1GO-- To update the currently configured value for this feature.RECONFIGUREGOEXEC master..xp_cmdshell 'bcp Testdb2.dbo.Demo_A out c:\Temp.xls -c -q -S"ap4\Net2012" -U"sa" -P"sA"' 

这样得到的xls文件中，Sheet是不带列名的。但可以改进一下，得到如下命令(参考：http://social.msdn.microsoft.com/forums/en-US/sqlgetstarted/thread/812b8eec-5b77-42a2-bd23-965558ece5b9/)：

方法一：使用BCP

为了方便，我创建了一个存储过程：

/****** SQL Export to xls ***************//* Example *//*CPP_Export_To_Excel_With_Header 'Testdb2','Demo_A','C:\TestExxelWithHeader.xls'*//* 2012.5.4 BY tony,邀月, [e-mail] */---- CPP_Export_To_Excel_With_Header 'Testdb2','Demo_A','C:\TestExcelWithHeader.xls'Create Procedure CPP_Export_To_Excel_With_Header(@db_name varchar(255),@table_name varchar(255),@file_path varchar(255))as----Generate column names as a recordsetdeclare @columns varchar(8000), @sql varchar(8000)declare @HeadersOnlyFile varchar(255),@TableDataWithoutHeaders varchar(255)set @HeadersOnlyFile=replace(cast(newid() as VARCHAR(40)),'-','')+'1.xls'set @TableDataWithoutHeaders=replace(cast(newid() as VARCHAR(40)),'-','')+'2.xls'select@columns=coalesce(@columns+',','')+column_name+' as '+column_namefrominformation_schema.columnswheretable_name=@table_nameselect @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')print @columns----Generate column names fileset @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@HeadersOnlyFile+'" -c'''print @sqlexec(@sql)----Create a dummy file to have actual dataset @sql='exec master..xp_cmdshell ''bcp "'+@db_name+'.dbo.'+@table_name+'" out "'+@TableDataWithoutHeaders+'" -c -t -T'''print @sqlexec(@sql)--Merge File into One Final Formatset @sql='exec master..xp_cmdshell ''copy /b '+@HeadersOnlyFile+'+'+@TableDataWithoutHeaders+' '+@file_path+''''print @sqlexec(@sql)--Delete temp Fileset @sql='exec master..xp_cmdshell ''del '+@HeadersOnlyFile+''''exec(@sql)set @sql='exec master..xp_cmdshell ''del '+@TableDataWithoutHeaders+''''exec(@sql)

调用方法：

CPP_Export_To_Excel_With_Header 'Testdb2','Demo_A','C:\TestExcelWithHeader.xls'

另外有类似的处理方法：(看这里：http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926),其主要思路如下：

方法二：

create procedure proc_generate_excel_with_columns(@db_namevarchar(100),@table_namevarchar(100),@file_namevarchar(100))as--Generate column names as a recordsetdeclare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)select @columns=coalesce(@columns+',','')+column_name+' as '+column_name from information_schema.columnswhere table_name=@table_nameselect @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')--Create a dummy file to have actual dataselect @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'--Generate column names in the passed EXCEL fileset @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''exec(@sql)--Generate data in the dummy fileset @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''exec(@sql)--Copy dummy file to passed EXCEL fileset @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)

调用示例：

EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'

如果，你的环境是SQL Server 2005,那么可以有：

方法三，使用sp_makewebtask,仅适用于SQL Server 2005

0)表T1结构a    intb    intx    char1)开启Web Assistant Proceduresexec sp_configure 'show advanced options', 1 RECONFIGURE exec sp_configure 'Web Assistant Procedures', 1 RECONFIGURE 2)执行如下语句EXEC sp_makewebtask     @outputfile = 'd:\testing.xls',     @query = 'Select TOP 10 * from shenliang1985..T1',     @colheaders =1,     @FixedFont=0,@lastupdated=0,@resultstitle='Querying details'3)查看生成的EXCEl的Querying details                                                        Last updated: 2010-03-03 01:02:59.263                            a    b    x                    0    0    0                    2    5    1                    4    10    2                    6    15    3                    8    20    4                    10    25    5                    12    30    6                    14    35    7                    16    40    8                    18    45    9    

可惜SQL Server 2008以后sp_makewebtask 这个存储过程取消了，后续版本也不再启用。

方法一和方法二其实生成的文件都不上真正的Excel文件，虽然后缀名为xls，为此，找到邹建写的一个存储过程。

方法四,使用OpenRowSet：

/*--数据导出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)=''--要创建的工作表名,默认为文件名asdeclare @err int,@src nvarchar(255),@desc nvarchar(255),@out intdeclare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)--参数检测if isnull(@fname,'')=''set @fname='temp2012.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+@fnameinsert into #tb exec master..xp_fileexist @sql--数据库创建语句set @sql=@path+@fnameif exists(select 1 from #tb where a=1)set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE'+';CREATE_DB="'+@sql+'";DBQ='+@sqlelseset @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES'+';DATABASE='+@sql+'"'--连接数据库exec @err=sp_oacreate 'adodb.connection',@obj outif @err<>0 goto lberrexec @err=sp_oamethod @obj,'open',null,@constrif @err<>0 goto lberr--创建表的SQLdeclare @tbname sysnameset @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+'] '+casewhen b.name like '%char'then case when a.length>255 then 'memo'else 'text('+cast(a.length as varchar)+')' endwhen 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 endFROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertypewhere 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 returnselect @sql='create table ['+@sheetname+']('+substring(@sql,2,8000)+')',@fdlist=substring(@fdlist,2,8000)exec @err=sp_oamethod @obj,'execute',@out out,@sqlif @err<>0 goto lberrexec @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)returnlberr:exec sp_oageterrorinfo 0,@src out,@desc outlbexit:select cast(@err as varbinary(4)) as 错误号,@src as 错误源,@desc as 错误描述select @sql,@constr,@fdlistgo

为了执行这个存储过程，你得先打开以下开关：

-- To allow advanced options to be changed.EXEC sp_configure 'show advanced options', 1GO-- To allow advanced options to be changed.EXEC sp_configure 'Ole Automation Procedures', 1GOEXEC sp_configure 'Ad Hoc Distributed Queries', 1GO-- To update the currently configured value for advanced options.RECONFIGUREGO

调用示例：

p_exporttb @sqlstr='select * from [Demo_A]',@path='c:\',@fname='Export2xls_ByProc.xls',@sheetname='员工名称'

结果确实是正宗的xls文件。

如果你是兼写程序的DBA，那么NPOI是你理想的选择，因为它是纯原生的不依赖于Office组件的开源第三方组件，它提供了一个“CreateExportDataTableSheetAndHeaderRow”方法可以让你方便的生成纯正的Excel，遗憾的是，目前好像只支持到Excel 2003。示例请看这儿（http://scottonwriting.net/sowblog/archive/2011/06/08/export-an-ado-net-datatable-to-excel-using-npoi.aspx）,该组件的源码：http://npoi.codeplex.com/

方法五：

protected Sheet CreateExportDataTableSheetAndHeaderRow(DataTable exportData, string sheetName, CellStyle headerRowStyle){    var sheet = this.Workbook.CreateSheet(EscapeSheetName(sheetName));    // Create the header row    var row = sheet.CreateRow(0);    for (var colIndex = 0; colIndex < exportData.Columns.Count; colIndex++)    {        var cell = row.CreateCell(colIndex);        cell.SetCellValue(exportData.Columns[colIndex].ColumnName);        if (headerRowStyle != null)            cell.CellStyle = headerRowStyle;    }    return sheet;}

当然，如果你觉得以上方法门槛有点高，那么SSIS可能是你的首选，它的优势在于简单直观，并且可以导出为Excel2007格式。

只要在导出时选择第一行包含列名，即可。

方法六，使用SSIS

因为有人觉得界面过于繁琐，于是仿照导出向导的思路写了一个批处理，你可以修改为自己适合的内容：

方法七：(http://social.msdn.microsoft.com/forums/en-US/sqlgetstarted/thread/812b8eec-5b77-42a2-bd23-965558ece5b9/)

@ECHO OFFREM -------------------------------------------------------------------------------REM Generic script for exporting data to file from SQL Server using a SQL query.REM The resulting file will be tab separated with newline as the row delimiter.REM A log file is generated and kept in case of an error or when in debug mode.REM See command syntax for details.REMREM History:REM 20120327 Lars Rönnbäck CREATEDREM -------------------------------------------------------------------------------:constantsSET myCodePage=ACP:variablesSET theQuery=%~1SET theFile=%~2SET theServer=%~3SET theDebug=%~4SET /a aRandomNumber=%random%%%1000FOR /F "usebackq tokens=1-7* delims=.:/,- " %%a IN (`ECHO %DATE%_%TIME%`) DO (SET myStartTime=%%a%%b%%c%%d%%e%%f%%g)SET myColumnQuery="select top 0 * into [#columns_%myStartTime%_%aRandomNumber%] from (%theQuery%) q; select stuff((select char(9) + c.name from tempdb.sys.columns c where c.object_id = t.object_id order by c.column_id for XML path(''), type).value('.', 'varchar(max)'), 1,1,'') AS Header from tempdb.sys.tables t where t.name like '#columns_%myStartTime%_%aRandomNumber%%%'"SET myHeaderFile=%theFile%.%aRandomNumber%.headerSET myDataFile=%theFile%.%aRandomNumber%.dataSET myLogFile=%theFile%.%myStartTime%_%aRandomNumber%.log:checksIF "%theQuery%"=="" (GOTO syntax)IF "%theFile%"=="" (GOTO syntax)IF "%theServer%"=="" (SET theServer=%COMPUTERNAME%):informationECHO Start Time: %myStartTime% >> "%myLogFile%" 2>&1ECHO Random Number: %aRandomNumber% >> "%myLogFile%" 2>&1ECHO File: %theFile% >> "%myLogFile%" 2>&1ECHO Server Name: %theServer% >> "%myLogFile%" 2>&1ECHO Query: >> "%myLogFile%" 2>&1ECHO. >> "%myLogFile%" 2>&1ECHO %theQuery% >> "%myLogFile%" 2>&1:exportBCP %myColumnQuery% queryout "%myHeaderFile%" -T -S "%theServer%" -a 65535 -c -C %myCodePage% -q >> "%myLogFile%" 2>&1IF ERRORLEVEL 1 GOTO errorBCP "%theQuery%" queryout "%myDataFile%" -T -S "%theServer%" -a 65535 -c -C %myCodePage% -q >> "%myLogFile%" 2>&1IF ERRORLEVEL 1 GOTO errorECHO. >> "%myLogFile%" 2>&1ECHO Merging files... >> "%myLogFile%" 2>&1ECHO. >> "%myLogFile%" 2>&1COPY /A "%myHeaderFile%" + "%myDataFile%" "%theFile%" /B /Y >> "%myLogFile%" 2>&1IF ERRORLEVEL 1 GOTO error:cleanupDEL "%myHeaderFile%" >NUL 2>&1IF ERRORLEVEL 1 GOTO errorDEL "%myDataFile%" >NUL 2>&1IF ERRORLEVEL 1 GOTO errorIF /I NOT [%theDebug%]==[Y] (DEL "%myLogFile%")IF ERRORLEVEL 1 GOTO errorGOTO end:errorECHO ECHO ERROR: An export error has occured!IF NOT [%myLogFile: =%]==[] (ECHO Details can be found in:ECHO %myLogFile%)ECHO EXIT /B 1:syntaxECHO.ECHO SYNTAX: %0 "sql query" "output file" [server] [Y]ECHO -------------------------------------------------------------------------------ECHO You must specify an SQL query and an output file name in which the results ofECHO the query will be stored. Specifying a server is optional and defaults to theECHO server you are executing on. If a fourth argument is given as Y a log file ofECHO the command outputs will be saved in the same folder as the output file.ECHO -------------------------------------------------------------------------------:endREM This is the end.

小结：

1、导出带有列名的Excel，可以用BCP，语句最少，但导出的不是真正的Excel文件；

2、使用OpenRowset，可以导出真正的Excel;

3、使用NPOI，可以最大化地满足编程人员的需求，另外也可在导出时再做适当的逻辑处理，另外也不需要xp_cmdshell等额外的权限；

4、最简单的是使用SSIS的导出向导，界面直观，可以直接导出为Excel 2003/2007格式。

邀月注：本文版权由邀月和CSDN共同所有，转载请注明出处。
助人等于自助!   [e-mail]