博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

Sql结果导出为excel文件(转)

Posted on 2012-12-26 21:02  linFen  阅读(7047)  评论(2编辑  收藏  举报

相信大家常常会遇到将SqlServer查询结果导出到Excel的问题。如果导出的次数少,直接“Save Results As...”就是了;但是当要分别在每个表取样,那就相当麻烦了。今天就为大家提供一个脱离office组件的可以将语句结果导出到Excel的过程,希望会对大家有帮助!

复制代码
---导出到Excel
---使用说明:
--        1.执行时所连接的服务器决定文件存放在哪个服务器
--        2.远程查询语句中,要加上数据库名
ALTER PROC ExportFile 
     @QuerySql VARCHAR(max) 
    ,@Server VARCHAR(20) 
    ,@User VARCHAR(20) 
    ,@Password VARCHAR(20) 
    ,@FilePath NVARCHAR(100) = 'c:\ExportFile.xls'
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 -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),是吧,会有不同的输出结果哦。