Create Excel File Using OPENROWSET and XP_CMDSHELL
Create PROCEDURE MCA_SP_CREATE_EXCEL @File_Name as varchar(50) = ”
AS
BEGIN
SET NOCOUNT ON
declare @File_Name as varchar(50)
DECLARE @Cmd varchar(1000)
DECLARE @fn varchar(500)
DECLARE @provider varchar(100)
DECLARE @ExcelString varchar(100)
– New File Name to be created
IF @File_Name = ”
Select @fn = ‘C:Test.xls’
ELSE
Select @fn = ‘C:’ + @File_Name + ‘.xls’
– FileCopy command string formation
SELECT @Cmd = ‘Copy C:Template.xls ‘ + @fn
– FielCopy command execution through Shell Command
EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT
– Mentioning the OLEDB Rpovider and excel destination filename
set @provider = ‘Microsoft.Jet.OLEDB.4.0′
set @ExcelString = ‘Excel 8.0;Database=’ + @fn
– Executing the OPENROWSET Command for copying the select contents to Excel sheet.
exec(‘insert into OPENrowset(”’ + @provider + ”’,”’ + @ExcelString + ”’,”SELECT FirstName,LastName,Phone,Address,City,State,Zip FROM [Sheet1$]”)
select au_fname as FirstName,au_lname as LastName,phone,address,city,State,Zip from authors’)
exec(‘insert into OPENrowset(”’ + @provider + ”’,”’ + @ExcelString + ”’,”SELECT StoreId, OrderNo, OrderDate, Quantity FROM [Sheet2$]”)
select stor_id as StoreId,Ord_Num as OrderNo,Ord_Date as OrderDate,qty as Quantity from sales’)
SET NOCOUNT OFF
END