SQL Query to Excel

[wreckingcru]

I'm trying to output a SQL query that is constructed thru my VB.net GUI
into an excel file.
Here is the code I'm using:

'Sqlstmt is the SQL query statement
'Conn is the SQL Connection object

cmd = New SqlCommand(Sqlstmt, Conn)

datareader = cmd.ExecuteReader()
'datareader is the SQLdatareader object

Dim objexcel As Excel.Application
Dim objwkb As Workbook
Dim objwksht As Worksheet

objexcel = New Excel.Application
objwkb = objexcel.Workbooks.Add
objwksht = objwkb.ActiveSheet()

Dim cols = datareader.FieldCount()

objwksht.Range(objwksht.Cells(1, 1), objwksht.Cells(5,
cols)).CopyFromRecordset(datareader)

The line above is causing an exception, i.e., I never receive any data
in the excel sheet. I put this in a try statement to catch the
exception and it is this:

System.InvalidCastException: No such interface supported
at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData&
msgData)
at Excel.Range.CopyFromRecordset(Object Data, Object MaxRows, Object MaxColumns)
at WindowsApplication1.Form1.btGenReport_Click(Object sender,
EventArgs e) in C:.....\Form1.vb:line 416

I'm a complete newbie at .NET (Unix C/C++ programming background).

Can anyone advise about what I'm doing wrong? I've sort of "learnt" the
code from others, so the Range command is kinda sketchy to me.....

What other alternative ways would there be to take the "datareader"
data and put it in an excel file (I'm trying to create a report based
on the data from a SQL table)?

Thanks!

 

[vis781]

Here's a snippet which may help, although it's a bit different from the way you are trying to do it.......

Dim excelApp As New Excel.Application
Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add
Dim excelWorksheet As Excel.Worksheet = _
CType(excelBook.Worksheets(1), Excel.Worksheet)
'Makes Excel invisible to the user until spreadsheet is populated
excelApp.Visible = False
With excelWorksheet
'Creates connections to pull data to DataSet
Dim ds As New DataSet("DataSetName")
Dim dr As DataRow
Dim myConnection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=//server/folder/file.mdb;Persist Security Info=False")
Dim myAdapter As New OleDb.OleDbDataAdapter
Dim myCommand As New OleDb.OleDbCommand(("SELECT LastName, FirstName, 
Details, DateWorkComplete FROM CompletedAll WHERE DateWorkComplete Between 
#" + beginDate.Value.ToShortDateString() + "# And #" + endDate.Value.ToShortDateString() +
 "# ORDER BY Division, DateWorkComplete"), myConnection)
Dim i As Integer = 2
'Bind myAdapter to myCommand
Try
myCommand.CommandType = CommandType.Text
myAdapter.SelectCommand = myCommand
myAdapter.SelectCommand.Connection = myConnection
myAdapter.Fill(ds)
Catch ex As Exception
MessageBox.show(ex.Message)
End Try
'Format cell headings
.Range("A1").Value = "Last Name"
.Range("A1").Font.Bold = True
.Range("A1").ColumnWidth = 15
.Range("B1").Value = "First Name"
.Range("B1").Font.Bold = True
.Range("B1").ColumnWidth = 15
.Range("C1").Value = "Date"
.Range("C1").Font.Bold = True
.Range("D1").Value = "Details"
.Range("D1").Font.Bold = True
.Range("D1").ColumnWidth = 150
'Populate Excel spreadsheet
Try
For Each dr In ds.Tables(0).Rows
.Range("A" & i.ToString).Value = dr("LastName")
.Range("B" & i.ToString).Value = dr("FirstName")
.Range("C" & i.ToString).Value = dr("DateWorkComplete")
.Range("D" & i.ToString).Value = dr("DetailsOfProblem")
i += 1
Next
Catch ex As Exception
MessageBox.Show(ex.message)
End Try
End With

You could probably play around with this and use a datareader instead and write the info directly into an excel worksheet. Shouldn't be too hard either. It'd definately be more efficient to use a datareader than to populate a datatable then write it to excel.