sql2008到如到excel.doc

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@ ******/ 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 -Sap4\Net2012 -Usa -PsA 这样得到的xls文件中，Sheet是不带列名的。但可以改进一下，得到如下命令(参考：/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,邀月, 3w@ */ 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 table_name=@table_name select @columns=+replace(replace(@columns, as , as ),,,,) print @columns Generate column names file set @sql=exec master..xp_cmdshell bcp select * from (select +@columns+) as t queryout +@HeadersOnlyFile+ -c print @sql exec(@sql