sql 生成excel

1创建存储过程

create PROCEDURE [dbo].[UP_Export_Data_To_CSV]
@Table NVARCHAR(),
@Path NVARCHAR(),
@Subfix NVARCHAR(),
@Server VARCHAR(),
@User VARCHAR(),
@Password VARCHAR()
AS
BEGIN
DECLARE @SQL NVARCHAR()
DECLARE @File NVARCHAR()

IF (LEN(ISNULL(@Path,''))>)
BEGIN
SELECT @File=@Path+'\'
END

--确定文件后缀
SELECT @File=@File+Convert( VARCHAR(),GETDATE(),)+'_'+CAST(DATEPART(HOUR,GETDATE()) as nvarchar()) 

IF (LEN(ISNULL(@Subfix,''))>)
BEGIN
SELECT @File=@File+'_'+@Subfix
END

SELECT @File=@File+'.csv'

--使用BCP命令
SELECT  @SQL = N'bcp '+@Table+' out '+@File+' -c -t, -T -S'+@Server+' -U'+@User+' -P '+@Password

EXEC  master..xp_cmdshell @SQL
END
GO

调用：

DECLARE @x NVARCHAR()
SET @x = '飞远物流发货明细'
EXEC [dbo].[UP_Export_Data_To_CSV] @Table = '[***].dbo.[view_***]',
    @Path = 'd:\DataReport\', @Subfix = @x, @Server = '.', @User = 'sa',
    @Password = '****'

然后发送邮件

DECLARE @s NVARCHAR()
DECLARE @b NVARCHAR(MAX)
DECLARE @c1 NVARCHAR()
DECLARE @c2 NVARCHAR()
DECLARE @c NVARCHAR(MAX)

SELECT
    @s = N'飞远物流发货明细_' + CONVERT(VARCHAR(), GETDATE(), )
SELECT
    @b = N'Hi All,
    附件为截止至(' + CONVERT(VARCHAR(10), GETDATE(), 120) + ' '
    + CAST(DATEPART(HOUR, GETDATE()) AS NVARCHAR()) + '_'
    + CAST(DATEPART(MINUTE, GETDATE()) AS NVARCHAR()) + N')的数据，请知晓！'
SELECT
    @c1 = N'd:\DataReport\' + CONVERT(VARCHAR(10), GETDATE(), 120) + '_'
    + CAST(DATEPART(HOUR, GETDATE()) AS NVARCHAR()) + N'_飞远物流发货明细.csv'
SELECT
    @c = @c1 

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'lifevc',--SQL Mail中配置的名称
    @recipients = '[e-mail]',
    @copy_recipients = 'locs@.com',
    @body = @b, @subject = @s, @file_attachments = @c