sqlserver带列名导出数据到excel的方法.docx

今天在项目中遇到一个问题，需要从SQL Server导出表到Excel，但需要带列名。尝试了几种方法，并小结如下：假定表如下：[sql]?view plaincopyprint?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，命令如下：[sql]?view plaincopyprint?--?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]?view plaincopyprint?/******?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?mast