sql 生成excel

浏览数：26 / 时间：2015年06月12日

1创建存储过程

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

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

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

IF (LEN(ISNULL(@Subfix,‘‘))>0)
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(100)
SET @x = ‘飞远物流发货明细‘
EXEC [dbo].[UP_Export_Data_To_CSV] @Table = ‘[***].dbo.[view_***]‘,
    @Path = ‘d:\DataReport\‘, @Subfix = @x, @Server = ‘.‘, @User = ‘sa‘,
    @Password = ‘****‘

然后发送邮件

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

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

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