SQL Server 2008中SQL应用系列及BI学习笔记系列--目录索引
前几天在项目中遇到一个问题，需要从SQL Server导出表到Excel，但需要带列名。晚上尝试了几种方法，并作个小结。
假定表如下：
USE testDb2 GO IF NOT OBJECT_ID('Demo_A') IS NULL DROP TABLE [Demo_A] /****** Object: Table [dbo].[Demo_A] downmoon:3w@live.cn ******/ CREATE TABLE [dbo].[Demo_A]( [ID] int not null, [Name] [Nvarchar](20) NOT NULL ) GO INSERT [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', 1 GO -- To update the currently configured value for advanced options. RECONFIGURE GO -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1 GO -- To update the currently configured value for this feature. RECONFIGURE GO EXEC 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
为了方便，我创建了一个存储过程：
1 /****** SQL Export to xls ***************/ 2 /* Example */ 3 /*CPP_Export_To_Excel_With_Header 'Testdb2','Demo_A','服务器名\实例名','C:\TestExxelWithHeader.xls'*/ 4 /* 2012.5.4 BY tony,邀月, 3w@live.cn */ 5 ---- CPP_Export_To_Excel_With_Header 'Testdb2','Demo_A','localhost\SQLExpress','C:\TestExcelWithHeader.xls' 6 7 Create Procedure [dbo].[CPP_Export_To_Excel_With_Header] 8 ( 9 @db_name varchar(255), 10 @table_name varchar(255), 11 @server_name varchar(255), 12 @file_path varchar(255) 13 ) 14 as 15 16 ----Generate column names as a recordset 17 declare @columns varchar(8000), @sql varchar(8000) 18 declare @HeadersOnlyFile varchar(255),@TableDataWithoutHeaders varchar(255) 19 set @HeadersOnlyFile=replace(cast(newid() as VARCHAR(40)),'-','')+'1.xls' 20 21 set @TableDataWithoutHeaders=replace(cast(newid() as VARCHAR(40)),'-','')+'2.xls' 22 select 23 @columns=coalesce(@columns+',','')+column_name+' as '+column_name 24 from 25 information_schema.columns 26 where 27 table_name=@table_name 28 select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''') 29 print @columns 30 ----Generate column names file 31 set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@HeadersOnlyFile+'" -c -T -S'+@server_name+'''' 32 print @sql 33 exec(@sql) 34 35 ----Create a dummy file to have actual data 36 set @sql='exec master..xp_cmdshell ''bcp "'+@db_name+'..'+@table_name+'" out "'+@TableDataWithoutHeaders+'" -c -T -S'+@server_name+'''' 37 print @sql 38 exec(@sql) 39 40 --Merge File into One Final Format 41 set @sql='exec master..xp_cmdshell ''copy /b '+@HeadersOnlyFile+'+'+@TableDataWithoutHeaders+' '+@file_path+'''' 42 print @sql 43 exec(@sql) 44 45 --Delete temp File 46 set @sql='exec master..xp_cmdshell ''del '+@HeadersOnlyFile+'''' 47 exec(@sql) 48 set @sql='exec master..xp_cmdshell ''del '+@TableDataWithoutHeaders+'''' 49 exec(@sql)
调用方法：
exec CPP_Export_To_Excel_With_Header 'testdb2','Demo_A','myshop\SQLExpress','c:\Demo_A2.xls'
注意，报错的话：
1、SQL Server是否以wndows登录方式或混合模式安装，参数中的“-c -T -S机器名\SQLExpress”，即机器名\实例，如果默认实例名与机器一致，用机器名即可。
详见官方BCP命令。https://docs.microsoft.com/zh-cn/sql/tools/bcp-utility?view=sql-server-ver15
2、也可以SA用户登录，请修改过程中的参数为BCP相应参数，见上面示例。
另外有类似的处理方法：(看这里：http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926),其主要思路如下：
方法二：
1 ALTER procedure [dbo].[proc_generate_excel_with_header] 2 ( 3 @db_name varchar(100), 4 @table_name varchar(100), 5 @server_name varchar(255), 6 @file_name varchar(100) 7 ) 8 as 9 10 /****** SQL Export to xls ***************/ 11 /* Example */ 12 /*proc_generate_excel_with_header 'Testdb','Demo_A','服务器名\实例名','c:\Demo_A.xls'*/ 13 /* 2020.1.2 BY tony,邀月, 3w@live.cn */ 14 ---- proc_generate_excel_with_header 'Testdb','Demo_A','localhost\SQLExpress','C:\TestExcelWithHeader.xls' 15 16 17 --Generate column names as a recordset 18 DECLARE @raw_sql nvarchar(4000), @sql varchar(8000) 19 20 DECLARE @columnHeader VARCHAR(8000) 21 SELECT @columnHeader = COALESCE(@columnHeader+',' ,'')+ ''''''+column_name +'''''' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table_name 22 23 DECLARE @ColumnList VARCHAR(8000) 24 SELECT @ColumnList = COALESCE(@ColumnList+',' ,'')+ 'CAST('+column_name +' AS VARCHAR)' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table_name 25 26 SELECT @raw_sql = 'SELECT '+ @columnHeader +' UNION ALL SELECT ' + @ColumnList + ' FROM ' + @db_name+'..'+@table_name 27 PRINT @raw_SQL 28 --EXECUTE sp_executesql @raw_sql 29 30 --filepath 31 ----select @file_name=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls' 32 if isnull(@server_name,'')='' 33 set @server_name='localhost' 34 35 --Generate data in the dummy file 36 set @sql='exec master..xp_cmdshell ''bcp "'+@raw_sql+'" queryout "'+@file_name+'" -c -T -S'+@server_name+'''' 37 print @sql 38 exec(@sql)
调用示例：
exec proc_generate_excel_with_header 'testdb2','Demo_A','myshop\SQLExpress','c:\Demo_A.xls'
同样，报错的话：
1、SQL Server是否以wndows登录方式或混合模式安装，参数中的“-c -T -S机器名\SQLExpress”，即机器名\实例，如果默认实例名与机器一致，用机器名即可。
详见官方BCP命令。https://docs.microsoft.com/zh-cn/sql/tools/bcp-utility?view=sql-server-ver15
2、也可以SA用户登录，请修改过程中的参数为BCP相应参数，见上面示例。
如果，你的环境是SQL Server 2005,那么可以有：
方法三，使用sp_makewebtask,仅适用于SQL Server 2005
0)表T1结构 a int b int x char 1)开启Web Assistant Procedures exec 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)=''--要创建的工作表名,默认为文件名 as declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int declare @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+@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+'"' --连接数据库 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 --创建表的SQL declare @tbname sysname set @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+'] ' +case when b.name like '%char' then case when a.length>255 then 'memo' 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) if @@rowcount=0 return 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 --导入数据 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) return lberr: exec sp_oageterrorinfo 0,@src out,@desc out lbexit: select cast(@err as varbinary(4)) as 错误号 ,@src as 错误源,@desc as 错误描述 select @sql,@constr,@fdlist go
为了执行这个存储过程，你得先打开以下开关：
-- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO -- To allow advanced options to be changed. EXEC sp_configure 'Ole Automation Procedures', 1 GO EXEC sp_configure 'Ad Hoc Distributed Queries', 1 GO -- To update the currently configured value for advanced options. RECONFIGURE GO
调用示例：
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
因为有人觉得界面过于繁琐，于是仿照导出向导的思路写了一个批处理，你可以修改为自己适合的内容：
@ECHO OFF REM ------------------------------------------------------------------------------- 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. REM REM History: REM 20120327 Lars Rönnbäck CREATED REM ------------------------------------------------------------------------------- :constants SET myCodePage=ACP :variables SET theQuery=%~1 SET theFile=%~2 SET theServer=%~3 SET theDebug=%~4 SET /a aRandomNumber=%random%%%1000 FOR /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%.header SET myDataFile=%theFile%.%aRandomNumber%.data SET myLogFile=%theFile%.%myStartTime%_%aRandomNumber%.log :checks IF "%theQuery%"=="" ( GOTO syntax ) IF "%theFile%"=="" ( GOTO syntax ) IF "%theServer%"=="" ( SET theServer=%COMPUTERNAME% ) :information ECHO Start Time: %myStartTime% >> "%myLogFile%" 2>&1 ECHO Random Number: %aRandomNumber% >> "%myLogFile%" 2>&1 ECHO File: %theFile% >> "%myLogFile%" 2>&1 ECHO Server Name: %theServer% >> "%myLogFile%" 2>&1 ECHO Query: >> "%myLogFile%" 2>&1 ECHO. >> "%myLogFile%" 2>&1 ECHO %theQuery% >> "%myLogFile%" 2>&1 :export BCP %myColumnQuery% queryout "%myHeaderFile%" -T -S "%theServer%" -a 65535 -c -C %myCodePage% -q >> "%myLogFile%" 2>&1 IF ERRORLEVEL 1 GOTO error BCP "%theQuery%" queryout "%myDataFile%" -T -S "%theServer%" -a 65535 -c -C %myCodePage% -q >> "%myLogFile%" 2>&1 IF ERRORLEVEL 1 GOTO error ECHO. >> "%myLogFile%" 2>&1 ECHO Merging files... >> "%myLogFile%" 2>&1 ECHO. >> "%myLogFile%" 2>&1 COPY /A "%myHeaderFile%" + "%myDataFile%" "%theFile%" /B /Y >> "%myLogFile%" 2>&1 IF ERRORLEVEL 1 GOTO error :cleanup DEL "%myHeaderFile%" >NUL 2>&1 IF ERRORLEVEL 1 GOTO error DEL "%myDataFile%" >NUL 2>&1 IF ERRORLEVEL 1 GOTO error IF /I NOT [%theDebug%]==[Y] ( DEL "%myLogFile%" ) IF ERRORLEVEL 1 GOTO error GOTO end :error ECHO ECHO ERROR: An export error has occured! IF NOT [%myLogFile: =%]==[] ( ECHO Details can be found in: ECHO %myLogFile% ) ECHO EXIT /B 1 :syntax ECHO. 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 of ECHO the query will be stored. Specifying a server is optional and defaults to the ECHO server you are executing on. If a fourth argument is given as Y a log file of ECHO the command outputs will be saved in the same folder as the output file. ECHO ------------------------------------------------------------------------------- :end REM This is the end.
小结：
1、导出带有列名的Excel，可以用BCP，语句最少，但导出的不是真正的Excel文件；
2、使用OpenRowset，可以导出真正的Excel;
3、使用NPOI，可以最大化地满足编程人员的需求，另外也可在导出时再做适当的逻辑处理，另外也不需要xp_cmdshell等额外的权限；
4、最简单的是使用SSIS的导出向导，界面直观，可以直接导出为Excel 2003/2007格式。