2023年7月13日发(作者：)

SQLServer带列名导出到Excel(ExporttoCSVwithheaders)的几个思路今天在项目中遇到一个问题，需要从SQLServer导出表到Excel，但需要带列名。尝试了几种方法，并小结如下：假定表如下：[sql]viewplaincopyprint?1718192021USEtestDb2GOIFNOTOBJECT_ID('Demo_A')ISNULLDROPTABLE[Demo_A]/******Object:Table[dbo].[Demo_A]downmoon:3w@******/CREATETABLE[dbo].[Demo_A]([ID]intnotnull,[Name][Nvarchar](20)NOTNULL)GOINSERT[dbo].[Demo_A]SELECT1,'郭靖'unionALLSELECT2,'胡一刀'unionALLSELECT3,'令狐冲'GO如果通常的思路，我们可以用BCP，命令如下：[sql]p_cmdshell'_Aoutc:_configure'showadvancedoptions',_configure'xp_cmdshell',IGUREGO-S"ap4Net2012"-U"sa"-P"sA"'这样得到的xls文件中，Sheet是不带列名的。但可以改进一下，得到如下命令(参考：/forums/en-US/sqlgetstarted/thread/812b8eec-5b77-42a2-bd23-965558ece5b9/)：方法一：使用BCP为了方便，我创建了一个存储过程：[sql]viewplaincopyprint?363738/******SQLExporttoxls***************//*Example*//*CPP_Export_To_Excel_With_Header'Testdb2','Demo_A','C:'*/3940/*2012.5.4BYtony,邀月,3w@*/----CPP_Export_To_Excel_With_Header'Testdb2','Demo_A','C:'44748495CreateProcedureCPP_Export_To_Excel_With_Header(@db_namevarchar(255),@table_namevarchar(255),@file_pathvarchar(255))as----Generatecolumnnamesasarecordsetdeclare@columnsvarchar(8000),@sqlvarchar(8000)declare@HeadersOnlyFilevarchar(255),@TableDataWithoutHeadersvarchar(255)set@HeadersOnlyFile=replace(cast(newid()asVARCHAR(40)),'-','')+''set@TableDataWithoutHeaders=replace(cast(newid()asVARCHAR(40)),'-','')+''565758596select@columns=coalesce(@columns+',','')+column_name+'as'+column_namefrominformation_swheretable_name=@table_nameselect@columns=''''''+replace(replace(@columns,'as','''''as'),',',',''''')print@columns----Generatecolumnnamesfileset@sql='p_cmdshell''bcp"select*from(select'+@columns+')ast"queryout"'+@HeadersOnlyFile+'"-c'''66676869----Createadummyfiletohaveactualdataprint@sqlexec(@sql)70set@sql='p_cmdshell''bcp"'+@db_name+'.dbo.'+@table_name+'"out"'+@TableDataWithoutHeaders+'"-c-t-T'''7172737475--MergeFileintoOneFinalFormatset@sql='p_cmdshell''copy/bprint@sqlexec(@sql)'+@HeadersOnlyFile+'+'+@TableDataWithoutHeaders+''+@file_path+''''7677787980818283--DeletetempFileset@sql='p_cmdshell''del'+@HeadersOnlyFile+''''exec(@sql)set@sql='p_cmdshell''del'+@TableDataWithoutHeaders+''''exec(@sql)print@sqlexec(@sql)调用方法：[sql]viewplaincopyprint?84CPP_Export_To_Excel_With_Header'Testdb2','Demo_A','C:'另外有类似的处理方法：(看这里：/forums/?TOPIC_ID=49926),其主要思路如下：方法二：[sql]viewplaincopyprint?85868788createprocedureproc_generate_excel_with_columns(@db_namevarchar(100),@table_namevarchar(100),8996979899@file_namevarchar(100))as--Generatecolumnnamesasarecordsetdeclare@columnsvarchar(8000),@sqlvarchar(8000),@data_filevarchar(100)select@columns=coalesce(@columns+',','')+column_name+'as'+column_namefrominformation_swhere100table_name=@table_name101select@columns=''''''+replace(replace(@columns,'as','''''as'),',',',''''')102103--Createadummyfiletohaveactualdata104select@data_file=substring(@file_name,1,len(@file_name)-charindex('',reverse(@file_name)))+'data_'105106--GeneratecolumnnamesinthepassedEXCELfile107set@sql='p_cmdshell''bcp"select*from(select'+@columns+')ast"queryout"'+@file_name+'"-c'''108exec(@sql)109110--Generatedatainthedummyfile111set@sql='p_cmdshell''bcp"select*from'+@db_name+'..'+@table_name+'"queryout"'+@data_file+'"-c'''112exec(@sql)113114--CopydummyfiletopassedEXCELfile115set@sql='p_cmdshell''type'+@data_file+'>>"'+@file_name+'"'''116exec(@sql)117118--Deletedummyfile119set@sql='p_cmdshell''del'+@data_file+''''120exec(@sql)调用示例：[sql]viewplaincopyprint?121EXECproc_generate_excel_with_columns'yourdbname','yourtablename','yourfilepath'如果，你的环境是SQLServer2005,那么可以有：方法三，使用sp_makewebtask,仅适用于SQLServer2005[sql]viewplaincopyprint?1220)表T1结构123aint124bint125xchar1261271)开启WebAssistantProcedures128129execsp_configure'showadvancedoptions',1130RECONFIGURE131execsp_configure'WebAssistantProcedures',1132RECONFIGURE1331342)执行如下语句135136EXECsp_makewebtask137@outputfile='d:',138@query='SelectTOP10*fromshenliang1985..T1',139@colheaders=1,140@FixedFont=0,@lastupdated=0,@resultstitle='Queryingdetails'1411423)查看生成的EXCEl的143144Queryingdetails8Lastupdated:2010-03-0301:02:59.263149150abx9164可惜SQLServer2008以后sp_makewebtask这个存储过程取消了，后续版本也不再启用。方法一和方法二其实生成的文件都不上真正的Excel文件，虽然后缀名为xls，为此，找到邹建写的一个存储过程。方法四,使用OpenRowSet：[sql]viewplaincopyprint?161/*--数据导出EXCEL162163导出查询中的数据到Excel,包含字段名,文件为真正的Excel文件164如果文件不存在,将自动创建文件165如果表不存在,将自动创建表166基于通用性考虑,仅支持导出标准数据类型167168--邹建2003.10(引用请保留此信息)--*/169170/*--调用示例171172p_exporttb@sqlstr='select*from地区资料'173,@path='c:',@fname='',@sheetname='地区资料'174--*/175createprocp_exporttb176@sqlstrvarchar(8000),--查询语句,如果查询语句中使用了orderby,请加上top100percent177@pathnvarchar(1000),--文件存放目录178@fnamenvarchar(250),--文件名179@sheetnamevarchar(250)=''--要创建的工作表名,默认为文件名180as181declare@errint,@srcnvarchar(255),@descnvarchar(255),@outint182declare@objint,@constrnvarchar(1000),@sqlvarchar(8000),@fdlistvarchar(8000)183184--参数检测185ifisnull(@fname,'')=''set@fname=''186ifisnull(@sheetname,'')=''set@sheetname=replace(@fname,'.','#')187188--检查文件是否已经存在189ifright(@path,1)<>''set@path=@path+''190createtable#tb(abit,bbit,cbit)191set@sql=@path+@p_fileexist@sql193194--数据库创建语句195set@sql=@path+@fname196ifexists(select1from#tbwherea=1)197set@constr='DRIVER={MicrosoftExcelDriver(*.xls)};DSN='''';READONLY=FALSE'198+';CREATE_DB="'+@sql+'";DBQ='+@sql199else200set@constr='Provider=.4.0;ExtendedProperties="Excel8.0;HDR=YES'201+';DATABASE='+@sql+'"'202203--连接数据库204exec@err=sp_oacreate'tion',@objout205if@err<>0gotolberr206207exec@err=sp_oamethod@obj,'open',null,@constr208if@err<>0gotolberr209210--创建表的SQL211declare@tbnamesysname212set@tbname='##tmp_'+convert(varchar(38),newid())213set@sql='select*into['+@tbname+']from('+@sqlstr+')a'214exec(@sql)215216select@sql='',@fdlist=''217select@fdlist=@fdlist+',['++']'218,@sql=@sql+',['++']'219+ke'%char'>255then'memo'222else'text('+cast(asvarchar)+')'ke'%int'='bit'then'int'ke'%datetime'then'datetime'ke'%money'then'money'ke'%text'then'memo'd228FROMtempdb..syscolumnsaleftjointempdb..=tin('image','uniqueidentifier','sql_variant','varbinary','binary','timestamp')=(selectidfromtempdb..sysobjectswherename=@tbname)231232if@@rowcount=0return233234select@sql='createtable['+@sheetname235+']('+substring(@sql,2,8000)+')'236,@fdlist=substring(@fdlist,2,8000)237238exec@err=sp_oamethod@obj,'execute',@outout,@sql239if@err<>0gotolberr240241exec@err=sp_oadestroy@obj242243--导入数据244set@sql='openrowset(''.4.0'',''Excel8.0;HDR=YES245;DATABASE='+@path+@fname+''',['+@sheetname+'$])'246247exec('insertinto'+@sql+'('+@fdlist+')select'+@fdlist+'from['+@tbname+']')248249set@sql='droptable['+@tbname+']'250exec(@sql)251return252253lberr:254execsp_oageterrorinfo0,@srcout,@descout255lbexit:256selectcast(@errasvarbinary(4))as错误号257,@srcas错误源,@descas错误描述258select@sql,@constr,@fdlist259go为了执行这个存储过程，你得先打开以下开关：[sql]viewplaincopyprint?260--Toallowadvancedoptionstobechanged.261EXECsp_configure'showadvancedoptions',1262GO263--Toallowadvancedoptionstobechanged.264EXECsp_configure'OleAutomationProcedures',1265GO266267EXECsp_configure'AdHocDistributedQueries',1268GO269270--Toupdatethecurrentlyconfiguredvalueforadvancedoptions.271RECONFIGURE272GO调用示例：[sql]viewplaincopyprint?273p_exporttb@sqlstr='select*from[Demo_A]'274,@path='c:',@fname='Export2xls_',@sheetname='员工名称'结果确实是正宗的xls文件。如果你是兼写程序的DBA，那么NPOI是你理想的选择，因为它是纯原生的不依赖于Office组件的开源第三方组件，它提供了一个“CreateExportDataTableSheetAndHeaderRow”方法可以让你方便的生成纯正的Excel，遗憾的是，目前好像只支持到Excel2003。示例请看这儿（/sowblog/archive/2011/06/08/）,该组件的源码：/方法五：[csharp]viewplaincopyprint?275protectedSheetCreateExportDataTableSheetAndHeaderRow(DataTableexportData,stringsheetName,CellStyleheaderRowStyle)276{277varsheet=Sheet(EscapeSheetName(sheetName));278279//Createtheheaderrow280varrow=Row(0);281282for(varcolIndex=0;colIndex<;colIndex++)283{284varcell=Cell(colIndex);lValue(s[colIndex].ColumnName);286287if(headerRowStyle!=null)yle=headerRowStyle;289}290291returnsheet;292}当然，如果你觉得以上方法门槛有点高，那么SSIS可能是你的首选，它的优势在于简单直观，并且可以导出为Excel2007格式。只要在导出时选择第一行包含列名，即可。方法六，使用SSIS因为有人觉得界面过于繁琐，于是仿照导出向导的思路写了一个批处理，你可以修改为自己适合的内容：方法七：(/forums/en-US/sqlgetstarted/thread/812b8eec-5b77-42a2-bd23-965558ece5b9/)[vb]viewplaincopyprint?293@ECHOOFF294REM-------------------------------------------------------------------------------295REMGenericscriptforexportingdatatofilefromSQLServerusingaSQLquery.296REMTheresultingfilewillbetabseparatedwithnewlineastherowdelimiter.297REMAlogfileisgeneratedandkeptincaseofanerrororwhenindebugmode.298REMSeecommandsyntaxfordetails.299REM300REMHistory:301REM20120327LarsRönnbäckCREATED302REM-------------------------------------------------------------------------------303:constants304SETmyCodePage=ACP305:variables306SETtheQuery=%~1307SETtheFile=%~2308SETtheServer=%~3309SETtheDebug=%~4310SET/aaRandomNumber=%random%%%1000311FOR/F"usebackqtokens=1-7*delims=.:/,-"%%aIN(`ECHO%DATE%_%TIME%`)DO(SETmyStartTime=%%a%%b%%c%%d%%e%%f%%g)312SETmyColumnQuery="selecttop0*into[#columns_%myStartTime%_%aRandomNumber%]from(%theQuery%)q;selectstuff((selectchar(9)+_id=__idforXMLpath(''),type).value('.','varchar(max)'),1,1,'')ke'#columns_%myStartTime%_%aRandomNumber%%%'"313SETmyHeaderFile=%theFile%.%aRandomNumber%.header314SETmyDataFile=%theFile%.%aRandomNumber%.data315SETmyLogFile=%theFile%.%myStartTime%_%aRandomNumber%.log316:checks317IF"%theQuery%"==""(318GOTOsyntax319)320IF"%theFile%"==""(321GOTOsyntax322)323IF"%theServer%"==""(324SETtheServer=%COMPUTERNAME%325)326:information327ECHOStartTime:%myStartTime%>>"%myLogFile%"2>&1328ECHORandomNumber:%aRandomNumber%>>"%myLogFile%"2>&1329ECHOFile:%theFile%>>"%myLogFile%"2>&1330ECHOServerName:%theServer%>>"%myLogFile%"2>&1331ECHOQuery:>>"%myLogFile%"2>&1332ECHO.>>"%myLogFile%"2>&1333ECHO%theQuery%>>"%myLogFile%"2>&1334:export335BCP%myColumnQuery%queryout"%myHeaderFile%"-T-S"%theServer%"-a65535-c-C%myCodePage%-q>>"%myLogFile%"2>&1336IFERRORLEVEL1GOTOerror337BCP"%theQuery%"queryout"%myDataFile%"-T-S"%theServer%"-a65535-c-C%myCodePage%-q>>"%myLogFile%"2>&1338IFERRORLEVEL1GOTOerror339ECHO.>>"%myLogFile%"2>&>>"%myLogFile%"2>&1341ECHO.>>"%myLogFile%"2>&1342COPY/A"%myHeaderFile%"+"%myDataFile%""%theFile%"/B/Y>>"%myLogFile%"2>&1343IFERRORLEVEL1GOTOerror344:cleanup345DEL"%myHeaderFile%">NUL2>&1346IFERRORLEVEL1GOTOerror347DEL"%myDataFile%">NUL2>&1348IFERRORLEVEL1GOTOerror349IF/INOT[%theDebug%]==[Y](350DEL"%myLogFile%"351)352IFERRORLEVEL1GOTOerror353GOTOend354:error355ECHO356ECHOERROR:Anexporterrorhasoccured!357IFNOT[%myLogFile:=%]==[](358ECHODetailscanbefoundin:359ECHO%myLogFile%360)361ECHO362EXIT/B1363:syntax364ECHO.365ECHOSYNTAX:%0"sqlquery""outputfile"[server][Y]366ECHO-------------------------------------------------------------------------------367ECHOYoumustspecifyanSQLqueryanyingrthargumentisgivenasYalogfileof370ECHOthecommandoutputswillbesavedinthesamefolderastheoutputfile.371ECHO-------------------------------------------------------------------------------372:end373REMThisistheend.小结：1、导出带有列名的Excel，可以用BCP，语句最少，但导出的不是真正的Excel文件；2、使用OpenRowset，可以导出真正的Excel;3、使用NPOI，可以最大化地满足编程人员的需求，另外也可在导出时再做适当的逻辑处理，另外也不需要xp_cmdshell等额外的权限；4、最简单的是使用SSIS的导出向导，界面直观，可以直接导出为Excel2003/2007格式。邀月注：本文版权由邀月和CSDN共同所有，转载请注明出处。O(∩_∩)O~文章由技术宅（)提供，欢迎各位童鞋访问技术宅，这里有给力的技术，有有趣的资源，~~O(∩_∩)O~，大家可以在这里交流学习各种电脑技s术@_@