Export ADO.NET DataTable to New Excel workbook?

[Sorwen]

Ok so I needed to dump all the data my program pulled in to a new Excel Workbook. I found this awesome example, until I remembered I'm no longer using an ADODB record set, but an ADO.NET DataTable. Microsoft of course says that CopyFromRecordset doesn't work (make sense), but to look deeper in for an example how to do this with ADO.NET instead. Lo and behold instead of an example that tells me how to do this it gives me only an example of how to add data to an EXISTING workbook. If I don't believe in Microsoft will they die? No one clap now.

Here is the ADODB Code changed to what I needed if I was still using ADODB. Any ideas how to change this to what I need for ADO.NET?

 Public Delegate Sub tDataToExcel(ByVal rSet As ADODB.Recordset, ByVal WorkbookLocation As String, ByVal WorkbookName As String)

    Public Sub DataToExcel(ByVal rSet As ADODB.Recordset, ByVal WorkbookLocation As String, ByVal WorkbookName As String)
        Dim oExcel As Object
        Dim oBook As Object
        Dim oSheet As Object
        oExcel = CreateObject("Excel.Application")
        oBook = oExcel.Workbooks.Add
        oSheet = oBook.Worksheets(1)

        Dim n As Int32
        For n = 1 To rSet.Fields.Count
            oSheet.cells(1, n).value = rSet.Fields(n - 1).Name
        Next

        oSheet.Range("A2").CopyFromRecordset(rSet)

        oBook.SaveAs(WorkbookLocation & WorkbookName)
        oSheet = Nothing
        oBook = Nothing
        oExcel.Quit()
        oExcel = Nothing
    End Sub

-I hate Microsoft!
-Forever and always forward.

 

[ThatRickGuy]

If I don't believe in Microsoft will they die? No one clap now.
-I hate Microsoft!
-Forever and always forward.[/code]

You know, if you are so loath to work in an MS IDE on an MS designed language, there are plenty of other languages for you to work with. Java and PHP are both solid languages with a wide variety of employement opperturnities.

As for the above code, why not just create a function that will take an ADO.Net datatable and return an ADO Record Set? Then just pass the record set in.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[Sorwen]

I wish I could, but company will not let me. I use to work in C++ Builder. So I work, but hate. I was thinking about that, but the only way I know to do it is to loop through and set it field by field line by line from the DataTable to the Recordset. Is there a better way?

-I hate Microsoft!
-Forever and always forward.

 

[Sorwen]

That and the fact everyone tells me I should quit using ADODB for anything.

-I hate Microsoft!
-Forever and always forward.

 

[Sorwen]

The current table is a small one at 16266 records. Takes quite a while in a for loop.

-I hate Microsoft!
-Forever and always forward.

 

[SiriusBlackOp]

That and the fact everyone tells me I should quit using ADODB for anything."

I remember standing in your shoes on that statement, but trust me... once you learn the simplicity of ADO.NET you will dread going back to your old code that uses ADODB. I know I did.

Senior Software Developer

http://www.scoutsft.com

 

[ThatRickGuy]

What version of Excel are you using? you may be able to use Excel's data import to pull in an XML text file. the only other way that I know of it to loop through your table and manually update the Excel spreadsheet.

Do you have a link to the ADO.Net sample?

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[Sorwen]

I could use Excels odbc to pull in the data directly from the source, but I need the automation to be totally self-contained. So I need to dump to an excel workbook without actually opening one separate from the computer.

This isn't the exact link, but looks the same:

http://support.microsoft.com/kb/247412/

Here is the exact link:
ms-help://MS.VSCC.v80/dv_vscccommon/html/05f46ffd-ff64-4c94-a0fc-bb665d74d24d.htm

-I hate Microsoft!
-Forever and always forward.

 

[ThatRickGuy]

The ms-help link only works if we have the exact same help libraries registered, so no luck there.

You could use the 2nd sample from the kb article though. Convert your data into a 2 dimensional array, and send it in that way.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[PRPhx]

The following is from "Using Microsoft Visual Basic.Net", QUE, Brian Siler and Jeff Spotts, pg 704:

Dim myWB as Workbook

ExcelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet).

To add more than one worksheet use:

MyWB.Worksheets.Add(,MyWB.Worksheets(1)). When you run your app, this code will also set the new worksheet as the active one.

NOTE: I have not tested this code myself, so I don't kbnow if it actually works or not.

 

[Sorwen]

Rick - Unless there is a quick way to dump from a datatable to an array then I still have the same problem as I do with dumping into a recordset. I'm hoping you will tell me there is a easy way. I have 16 columns so that would take quite a long time for it to load the data into an array if I had to use a for loop. 16x16266 = 260,256 VB.NET may be faster than vba, but I know that would kill (take for ever in) any of my vba apps to do that.

PRPhx - Thanks for the excerpt. Their doesn't seem to be any problems in it creating the workbook at the moment. I may find differently later, but right now the only problem is dumping the data into the workbook. If you don't mind looking see if they show a command that will dump data from a DataTable in to that workbook.

-I hate Microsoft!
-Forever and always forward.

 

[Sorwen]

That is it. I'm giving ADO.NET the raspberry on this one. I'm going to pull the data in with ADODB. I found while looking for a way to dump data into a recordset that .fill will fill a DataTable from a Recordset. That will keep me from having to recode everything to just recoding the data pull and no for loop to fill the recordset. Thanks for the help everyone.

-I hate Microsoft!
-Forever and always forward.

 

[PRPhx]

Unfortunetly, the only cell fill is for a single cell:

MyWS.Range("A1").Value = "Some Value"
MyWS.Range("A1").Value = SomeTextBox.Text
MyWS.Range("A1").Formula = "=B1-B2"

Hope it helps!

 

[Sorwen]

Thank you very much for looking. Maybe with ADO the only way you can do it is cell by cell. That just seems very inefficient, compared with ADO the way it normally is, when dealing with large amounts of data.

-I hate Microsoft!
-Forever and always forward.

 

[ThatRickGuy]

Can you post the code for the solution you got from microsoft that you mentioned in your first post?

Lo and behold instead of an example that tells me how to do this it gives me only an example of how to add data to an EXISTING workbook.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[Sorwen]

I had thought it was in the first one.

Transfer Data to a Worksheet Using ADO.NET
You can use the Microsoft Jet OLE DB provider to add records to a table in an existing Excel workbook. A "table" in Excel is merely a range of cells; the range may have a defined name. Typically, the first row of the range contains the headers (or field names), and all later rows in the range contain the records.

The following code adds two new records to a table in Book7.xls. The table in this case is Sheet1: 
       'Establish a connection to the data source.
        Dim sConnectionString As String
        sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & sSampleFolder & _
            "Book7.xls;Extended Properties=Excel 8.0;"
        Dim objConn As New System.Data.OleDb.OleDbConnection(sConnectionString)
        objConn.Open()

        'Add two records to the table.
        Dim objCmd As New System.Data.OleDb.OleDbCommand()
        objCmd.Connection = objConn
        objCmd.CommandText = "Insert into [Sheet1$] (FirstName, LastName)" & _
            " values ('Bill', 'Brown')"
        objCmd.ExecuteNonQuery()
        objCmd.CommandText = "Insert into [Sheet1$] (FirstName, LastName)" & _
            " values ('Joe', 'Thomas')"
        objCmd.ExecuteNonQuery()

        'Close the connection.
        objConn.Close()
				
When you add records with ADO.NET as shown, the formatting in the workbook is maintained. Each record that is added to a row borrows the format from the row before it. For example, new fields that are added to column B are formatted with right alignment because cell B1 is right-aligned. 

Note that when a record is added to a cell or cells in the worksheet, it overwrites any data that those cells previously contained. In other words, rows in the worksheet are not "pushed down" when new records are added. Keep this in mind when you design the layout of data on your worksheets if you plan to the insert new records by using ADO.NET.
[CODE]

-I hate Microsoft!
-Forever and always forward.