1

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 !!

Yugal Jindle
  • 44,057
  • 43
  • 129
  • 197

4 Answers4

1

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 !

Yugal Jindle
  • 44,057
  • 43
  • 129
  • 197
0

The simplest solution is if you write a csv file. In the csv the columns are separated with comma (;) and the excel can read this file.

The best way is the OLE Automation but for this you have to install an excel to the pc where your program runs. Here is a sample: OLE Automation

Csujo
  • 507
  • 2
  • 6
  • You are not getting the point, this is a part of an application. I can-not do these semi automatic tasks.. It has to do all on its own. – Yugal Jindle Jul 28 '11 at 06:23
  • Did you watch the link? The OLE Automation is a programmatic technology for automate Excel creation and modification. – Csujo Jul 28 '11 at 06:37
  • you don't really want to have a dependency on Excel if you don't need to. A .csv file would be the way to go, unless you needed more advanced features .. worksheets, etc – brodie Jul 28 '11 at 06:43
  • God I have a SQl Server database, where did mentioned csv ? This won't work for me.. and yes I did saw the link ! – Yugal Jindle Jul 28 '11 at 07:03
0

Best way is to use XmlSerialization of the data table and then perform Excel Loading of the serialised Xml...

There is a nice open source utility (which you can add to your project and make changes to as well) called ExcelLibrary ... please refer to this article... Create Excel (.XLS and .XLSX) file from C#

This is one of the most efficient ways of Bulk Excel Loading.

Community
  • 1
  • 1
WPF-it
  • 19,625
  • 8
  • 55
  • 71
-1

Look at something like EPlus which seems to be a popular library.

... or FileHelpers, where there is a good quickstart showing you how to create a .csv file

You can just use the export functionality of Sql Server Management studio if it's a one off type job?

brodie
  • 5,354
  • 4
  • 33
  • 28