how to generate excel sheets from sql server data using .net

2/ Also , can we do that in sql server itself

Thanks.

Can you explain a bit more about what you are trying to do Do you intend to generate these on the server and return them from a stored procedure for example, or do you intend to read data from SQL Server and generate the Excel file Or something different

Marcelo - MSFT

1/ I have data on sql server, I wat first to treat the data, then out of it, I need to generate excel sheets. How do I do that

2/ P.S: u mean there re both ways to do that on .net and on sql server can you please explain to me both alternatives and where I can read about the details of implementing both possibilities and their details. It s for an interview, I got just the big idea but no details yet.

Thanks a lot for your help.

Couple of ways to do this. One is to use the Data Transformation Services (DTS) in SQL Server:

http://www.codeproject.com/useritems/DTS__VBNET_.asp

...and the other would be to use SQL:

Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & "c:\Test Files\Book20.xls" & ";" & _

"Extended Properties=""Excel 8.0;HDR=No""")

ExcelConnection.Open()

Dim ExcelCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [Orders] FROM [Orders] IN '' [ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes];", ExcelConnection)

ExcelCommand.ExecuteNonQuery()

ExcelConnection.Close()

That assumes excel file already exists but my need is that i only have datat in sql server and i need to create the excel file that contains the data.

Thanks Paul.

See if this article helps:

How to transfer data to an Excel workbook by using Visual C# 2005 or Visual C# .NET