MSSQL - Output SQL Results to A Preformatted Excel File

/*

This SP uses a pre-formatted excel file as template then save the given sql results

into the template and email it to the user.

NOTE: sp_SQLNotify is used to send email; please see detail in

http://www.howtogeek.com/howto/database/sending-automated-job-email-notifications-in-sql-server-with-smtp/

EXAMPLE:

EXEC USP_ExportToExcel

     'C:\Template.xlsx',

     'Sheet1',

     'SELECT * from sys.servers',

     'from@email.com',

     'to@email.com',

     'Your daily missed sales data report'

*/

SET QUOTED_IDENTIFIER OFF

GO

ALTER PROCEDURE USP_ExportToExcel

@OutputTemplate VARCHAR(500),       -- PathName of the Excel containing header and format

@SheetName VARCHAR(100) = 'Sheet1', -- Name of the sheet

@SelectSQL VARCHAR(4000),                 -- Your query SQL

@FromEmail VARCHAR(100)='',               -- Email sender

@ToEmailList VARCHAR(1000)='',            -- Email to

@EmailSubject VARCHAR(1000)='',           -- Email subject

@NewFileName VARCHAR(500)=''        -- Email attachment; the output result

AS

DECLARE @SQL NVARCHAR(4000)

DECLARE @FileName VARCHAR(200)

SELECT @FileName = REVERSE(SUBSTRING(REVERSE(@OutputTemplate), 1, CHARINDEX('\', REVERSE(@OutputTemplate))-1))

PRINT @FileName

-- COPY TEMPLATE TO A TEMP FOLDER

SELECT @SQL = 'COPY /Y "' + @OutputTemplate + '" "C:\Temp"'

EXEC xp_cmdshell @SQL

-- RUN SELECT INTO A TEMP TABLE

SELECT @SQL = SUBSTRING(@SelectSQL, 1, CHARINDEX(' FROM', @SelectSQL)) + 'INTO ##EXPORTTEMP FROM' + SUBSTRING(@SelectSQL, CHARINDEX('FROM', @SelectSQL)+4, 8000)

EXEC sp_executesql @SQL

-- EXPORT DATA TO EXCEL FILE

IF (CHARINDEX('xlsx', @OutputTemplate) > 0)

BEGIN

    SELECT @SQL = 'INSERT INTO OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;Database=C:\TEMP\' + @FileName + ';HDR=NO;'',''SELECT * FROM [' + @SheetName + '$]'') SELECT * FROM ##EXPORTTEMP'

END

ELSE

BEGIN

    SELECT @SQL = 'INSERT INTO OPENROWSET(''Microsoft.JET.OLEDB.4.0'', ''Excel 8.0;Database=C:\TEMP\' + @FileName + ';HDR=YES'',''SELECT * FROM [' + @SheetName + '$]'') SELECT * FROM ##EXPORTTEMP'

END

EXEC sp_executesql @SQL

-- RENAME TO NEW FILENAME

IF @NewFileName <> ''

BEGIN

    SELECT @SQL = 'REN "C:\TEMP\' + @FileName + '" "' + @NewFileName + '"'

    EXEC xp_cmdshell @SQL

    SELECT @FileName = @NewFileName

END

-- SEND EMAIL IF REQUESTED

IF @FromEmail <> '' AND @ToEmailList <> ''

BEGIN

    SELECT @SQL = 'sp_SQLNotify ''' + @FromEmail + ''', ''' + @ToEmailList + ''', ''' + @EmailSubject + ''',  ''Automated report'', ''C:\TEMP\' + @FileName + ''''

    EXEC sp_executesql @SQL

END

-- DELETE FILE

SELECT @SQL = 'DEL "C:\Temp\' + @FileName + '"'

EXEC xp_cmdshell @SQL

DROP TABLE ##EXPORTTEMP