I am getting data from my database and I want to have that data as a table in excel file. So, I have written the following :
Dim sheetToPopulate As Excel.Worksheet = getSheet()
Dim reader As OleDbDataReader
Dim query As String = "SELECT * FROM dataTable"
Dim cmd As New OleDbCommand(query, oleConn)
Dim reader As OleDbDataReader
oleConn.Open()
reader = cmd.ExecuteReader()
Do While reader.Read()
// How use the reader to populate the sheet at once.
// I have the sheet object as sheetToPopulate.
// cell.Vaule = reader.GetString(0) ' It would be very in-efficient and complex.
// How can I dump the table to my excel sheet ?
Loop
reader.Close()
oleConn.Close()
There should be a straight obvious way of doing this ?
Dumping a database table to excel sheet ?
[ Should I ? ]
Should I use dataset of something.. ? If yes, how to proceed for that ?
Please help.. I am new to this !!
Here is how I solved this :
Private Function getData(ByVal query As String, ByVal conStr As String) As Object
Dim adapter As New Data.OleDb.OleDbDataAdapter(query, conStr)
Dim dataSet As New Data.DataSet
adapter.Fill(dataSet)
Dim dataTable As Data.DataTable = dataSet.Tables(0)
Dim data(dataTable.Rows.Count, dataTable.Columns.Count - 1) As Object
For col = 0 To dataTable.Columns.Count - 1
For row = 0 To dataTable.Rows.Count - 1
data(row, col) = dataTable.Rows(row).ItemArray(col)
Next
Next
Return data
End Function
Then Finally, do the following to the range where you want to have this data
range.Value = getDate(query,conStr)
That solved the whole problem !