Problem with transfering data from SQlServer to Excel

Question

HI,

I have to build the application which trafers the data from SQL Server to Existed Excel file. Now i get the data from SQL File but i dont know how to put that data into Already existed Excel file. Can you please help me.. Thanks in advance..

Bechir Bejaoui · Answer

It's quite simple, you can create a stored procedure within SQL server and call the OPENROWSET method responsible for exporting data to a targeted excel sheet and you can pass the excel file as a parameter via the stored procedure finally, you can call this procedure from within your C# code This is the style of your SP CREATE PROCEDURE ExportToExcel ( @filepath nvarchar(100), -- OK you can change from 100 to the numenr that --fits your needs @sheetname nvarchar(50) ) INSERT INTO OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0' , 'Excel 11.0;Database=C:\' + @filepath + ' ;' , 'SELECT Name, Date FROM [' + @sheetname + ']' ) SELECT * FROM yourdataTable then you can call the stored procedure from within the C# code string ConnectionString = "Integrated Security=SSPI;Initial Catalog=databasename;Data Source=servername;" ; SqlConnection conn = new SqlConnection ( ConnectionString ) ; SqlCommand StoredProcedureCommand = new SqlCommand ( " ExportToExcel " , conn ) ; StoredProcedureCommand.CommandType = CommandType.StoredProcedure; SqlParameter myParm1 = StoredProcedureCommand.Parameters.Add ( "@filepath " , SqlDbType.String ) ; myParm1.Value = @"C:\myfile.xlsx" ; SqlParameter myParm2 = StoredProcedureCommand.Parameters.Add ( "@sheetname" , SqlDbType.String ); myParm2.Value = "sheet$1" ; conn.Open () ; StoredProcedureCommand.excutenonequery;

Bechir Bejaoui · Answer

Not at all, you're welcome ;~}

Mohan Energy · Answer

Thanks for ur help.. now i can do this easily