Sql結果匯出為excel檔案

　　相信大家常常會遇到將SqlServer查詢結果匯出到Excel的問題。如果匯出的次數少，直接“Save Results As...”就是了；但是當要分別在每個表取樣，那就相當麻煩了。今天就為大家提供一個脫離office組件的可以將語句結果匯出到Excel的過程，希望會對大家有協助！

---匯出到Excel---使用說明：--        1.執行時所已連線的服務器決定檔案存放在哪個伺服器--        2.遠程查詢語句中，要加上資料庫名--更新：--        2013.01.05:增加csv檔案的支援ALTER PROC ExportFile      @QuerySql VARCHAR(max)    ,@Server VARCHAR(20)     ,@User VARCHAR(20) = 'sa'    ,@Password VARCHAR(20)    ,@FilePath NVARCHAR(100) = 'c:\ExportFile.csv'AS    DECLARE @tmp VARCHAR(50) = '[##Table' + CONVERT(VARCHAR(36),NEWID())+']'    BEGIN TRY        DECLARE @Sql VARCHAR(max),@DataSource VARCHAR(max)='';        --判斷是否為遠程伺服器        IF @Server <> '.' AND @Server <> '127.0.0.1'            SET @DataSource = 'OPENDATASOURCE(''SQLOLEDB'',''Data Source='+@Server+';User ID='+@User+';Password='+@Password+''').'        --將結果集匯出到指定的資料庫        SET @Sql = REPLACE(@QuerySql,' from ',' into '+@tmp+ ' from ' + @DataSource)        PRINT @Sql        EXEC(@Sql)                DECLARE @Columns VARCHAR(max) = '',@Data NVARCHAR(max)=''        SELECT @Columns = @Columns + ',''' + name +''''--擷取列名（xp_cmdshell匯出檔案沒有列名）            ,@Data = @Data + ',Convert(Nvarchar,[' + name +'])'--將結果集所在的欄位更新為nvarchar（避免在列名和資料union的時候類型衝突）        FROM tempdb.sys.columns WHERE object_id = OBJECT_ID('tempdb..'+@tmp)        SELECT @Data  = 'SELECT ' + SUBSTRING(@Data,2,LEN(@Data)) + ' FROM ' + @tmp        SELECT @Columns =  'Select ' + SUBSTRING(@Columns,2,LEN(@Columns))        --使用xp_cmdshell的bcp命令將資料匯出        EXEC sp_configure 'xp_cmdshell',1        RECONFIGURE        DECLARE @cmd NVARCHAR(4000) = 'bcp "' + @Columns+' Union All ' + @Data+'" queryout ' + @FilePath + ' -c' + CASE WHEN RIGHT(@FilePath,4) = '.csv' THEN ' -t,' ELSE '' END + ' -T'        PRINT @cmd        exec sys.xp_cmdshell @cmd        EXEC sp_configure 'xp_cmdshell',0        RECONFIGURE        EXEC('DROP TABLE ' + @tmp)    END TRY    BEGIN CATCH        --處理異常        IF OBJECT_ID('tempdb..'+@tmp) IS NOT NULL            EXEC('DROP TABLE ' + @tmp)        EXEC sp_configure 'xp_cmdshell',0        RECONFIGURE                SELECT ERROR_MESSAGE()    END CATCH        

 

先不要著急使用，該版本是基於xp_cmdshell的，因為要建立檔案，所以要保證你的使用者能有檔案管理的許可權，通常簡單點的方法就是將sql server的啟動使用者佈建為本地系統使用者

 

好了，現在我們來執行看看：

--查詢分析器串連哪個伺服器，檔案就在哪個伺服器上--本地匯出EXEC dbo.ExportFile @QuerySql = 'select * from sys.objects', -- varchar(max)    @Server = '.', -- varchar(20)    @FilePath = N'c:\objects.xls' -- nvarchar(100)--遠程匯出EXEC dbo.ExportFile @QuerySql = 'select * from master.sys.objects', -- varchar(max)    @Server = '192.168.1.52', -- varchar(20)    @User = 'sa', -- varchar(20)    @Password = 'sa', -- varchar(20)    @FilePath = N'c:\52objects.xls' -- nvarchar(100)

執行結果如下，顯示匯出條數，就沒有報錯，再看看你的C盤，多了2個檔案就大功告成了：

 

另外，大家把輸出檔案的類型改成其他試試（例如.txt或.csv），是吧，會有不同的輸出結果哦。

符xp_cmdshell bcp參數說明：

用法: bcp {dbtable | query} {in | out | queryout | format} 資料檔案

  [-m 最大錯誤數]            [-f 格式檔案]         [-e 錯誤檔案]  [-F 首行]                   　[-L 末行]             [-b 批大小]  [-n 本機類型]               [-c 字元類型]         [-w 寬字元類型]  [-N 將非文本保持為本機類型] [-V 檔案格式版本]     [-q 引號識別項]  [-C 字碼頁說明符]           [-t 欄位結束字元]       [-r 行終止符]  [-i 輸入檔案]               [-o 輸出檔案]         [-a 資料包大小]  [-S 伺服器名稱]             [-U 使用者名稱]           [-P 密碼]  [-T 可信串連]               [-v 版本]             [-R 允許使用地區設定]  [-k 保留空值]               [-E 保留標識值]  [-h"載入提示"]              [-x 產生xml 格式檔案]