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:[email protected] ******/
- 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
为了方便, 我创建了一个存储过程:
- /****** SQL Export to xls ***************/
- /* Example */
- /*CPP_Export_To_Excel_With_Header 'Testdb2','Demo_A','C:\TestExxelWithHeader.xls'*/
- /* 2012.5.4 BY tony, 邀月, [email protected] */
- ---- 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 recordset
- declare @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_name
- from
- information_schema.columns
- where
- [email protected]_name
- select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')
- print @columns
- ----Generate column names file
- set @sql='exec master..xp_cmdshell''bcp "select * from (select'[email protected]+') as t" queryout "'[email protected]+'" -c'''
- print @sql
- exec(@sql)
- ----Create a dummy file to have actual data
- set @sql='exec master..xp_cmdshell''bcp "'[email protected]_name+'.dbo.'[email protected]_name+'" out "'[email protected]+'" -c -t -T'''
- print @sql
- exec(@sql)
- --Merge File into One Final Format
- set @sql='exec master..xp_cmdshell''copy /b '[email protected]+'+'[email protected]+' '[email protected]_path+''''
- print @sql
- exec(@sql)
- --Delete temp File
- set @sql='exec master..xp_cmdshell''del '[email protected]+''''
- exec(@sql)
- set @sql='exec master..xp_cmdshell''del '[email protected]+''''
- exec(@sql)
- 调用方法:
- CPP_Export_To_Excel_With_Header 'Testdb2','Demo_A','C:\TestExcelWithHeader.xls'
- 另外有类似的处理方法:(看这里:), 其主要思路如下:
- 方法二:
- 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
- [email protected]_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'[email protected]+') as t" queryout "'[email protected]_name+'" -c'''
- exec(@sql)
- --Generate data in the dummy file
- set @sql='exec master..xp_cmdshell''bcp "select * from'[email protected]_name+'..'[email protected]_name+'"queryout"'[email protected]_file+'"-c'''
- exec(@sql)
- --Copy dummy file to passed EXCEL file
- set @sql= 'exec master..xp_cmdshell''type '[email protected]_file+'>> "'[email protected]_name+'"'''
- exec(@sql)
- --Delete dummy file
- set @sql= 'exec master..xp_cmdshell''del '[email protected]_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 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 @[email protected]+'\'
- create table #tb(a bit,b bit,c bit)
- set @[email protected][email protected]
- insert into #tb exec master..xp_fileexist @sql
- -- 数据库创建语句
- set @[email protected][email protected]
- if exists(select 1 from #tb where a=1)
- set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE'+';CREATE_DB="'[email protected]+'";DBQ='[email protected]
- else
- set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES'+';DATABASE='[email protected]+'"'
-- 连接数据库
- 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 ['[email protected]+'] from('[email protected]+') a'
- exec(@sql)
- select @sql='',@fdlist=''
- select @[email protected]+',['+a.name+']'
- ,@[email protected]+',['+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 [email protected])
- if @@rowcount=0 return
- select @sql='create table ['[email protected]
- +']('+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='[email protected][email protected]+''',['[email protected]+'$])'exec('insert into '[email protected]+'('[email protected]+') select '[email protected]+' from ['[email protected]+']')
- set @sql='drop table ['[email protected]+']'
- 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://npoi.codeplex.com/ https://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 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 格式.
来源: http://www.bubuko.com/infodetail-3344039.html