none
Copy ADO.NET dataset to Excel

    Question

  • Hi,
    In my VSTO Excel project, I have created an ADO.NET Dataset manually.  So the dataset is just sitting there all by itself without a connection.  Now I want to display the contents of the dataset on an Excel worksheet.  I've seen posts that show how to read the rows one by one and fill each Excel cell one by one, but this is way too slow.  I've also seen posts on how to use "SELECT * FROM Table", but this always seems to require a connection object.

    What is the super fast way to simply populate the Excel cells with data from this standalone dataset?  I tried statements like this: Me.Range("B2").Value = myDataset.Tables("MyTable") and Me.Range("B2:J2").Value = ds.Tables(0).Rows(0) but these just throw exceptions.  I don't know if it's necessary, but the first row of my dataset has the column headers of my table.

    Thank you for any help.
    Monday, November 19, 2007 1:51 AM

Answers

  •  CerfurMark wrote:
    Thanks for the response.  This is a VSTO Code-Behind-Excel Workbook project.  I have already explored the two solutions you provided, but they have both issues:

    1. For the ListObject, I have found that it is very slow.  It seems to populate cell-by-cell.  A 2000 record dataset can be retrieved from a database almost instantly, but the actual population of the Excel cells then takes 20 seconds.  Is this normal, or should I be expecting 2000 records to display instantly?

    2. For CopyFromRecordset, I could switch to ADO, but I don't get what Microsoft is suggesting.  Isn't ADO.NET the newer and better thing with datasets instead of recordsets?  Is Microsoft telling us that ADO is actually a retrograde?  What's the equivalent CopyFromRecordset for ADO.NET?

    Maybe binding the dataset to a Range is what I'm looking for.  But I don't want to bind to a control (as per the MSDN examples),  I just want to bind directly to the Excel cell.  Is this possible?

     

    It's important to realize that VSTO is not the Office application. VSTO is a set of tools that helps the .NET developer do certain things with the Office applications that Office itself does not provide. But the Office applications (with the exception of InfoPath and OneNote) are still firmly integrated in their traditional, "native" COM world.

     

    From this POV it's not surprising that Excel contains nothing in its object model that does anything with ADO.NET. Excel itself is completely classical VB(A)-oriented. CopyFromRecordset is the "latest and greatest" within the Excel object model for dumping a large amount of data directly into an opened workbook.

     

    Following this same logic, it's therefore not possible to bind anything in Excel directly to a .NET data source, unless you're using a VSTO control as a "go-between". Excel does have data-binding capabilities, but those are to standard databases, via OLE DB (ADO), ODBC, or to "flat table" XML files (in Excel 2003 and later).

    Monday, November 19, 2007 9:12 PM

All replies

  • Excel has a CopyFromRecordset method that lets you "drop" an entire ADO (not .NET) Recordset into a specified Range. If you can change your code to create a standard ADO recordset, then you can use this method to quickly populate a range with data.

     

    You're not clear about whether this is a VSTO Add-in or a VSTO workbook project. If the latter, then you could try binding the data to a ListObject control or a Range. The discussion about data-binding in VSTO document-level customizations is here

    http://msdn2.microsoft.com/en-us/library/xx069ybh(VS.80).aspx

    Monday, November 19, 2007 8:52 AM
  • Hi Cindy,

    Thanks for the response.  This is a VSTO Code-Behind-Excel Workbook project.  I have already explored the two solutions you provided, but they have both issues:

    1. For the ListObject, I have found that it is very slow.  It seems to populate cell-by-cell.  A 2000 record dataset can be retrieved from a database almost instantly, but the actual population of the Excel cells then takes 20 seconds.  Is this normal, or should I be expecting 2000 records to display instantly?

    2. For CopyFromRecordset, I could switch to ADO, but I don't get what Microsoft is suggesting.  Isn't ADO.NET the newer and better thing with datasets instead of recordsets?  Is Microsoft telling us that ADO is actually a retrograde?  What's the equivalent CopyFromRecordset for ADO.NET?

    Maybe binding the dataset to a Range is what I'm looking for.  But I don't want to bind to a control (as per the MSDN examples),  I just want to bind directly to the Excel cell.  Is this possible?

    Thank you.

    Mark
    Monday, November 19, 2007 8:51 PM
  •  CerfurMark wrote:
    Thanks for the response.  This is a VSTO Code-Behind-Excel Workbook project.  I have already explored the two solutions you provided, but they have both issues:

    1. For the ListObject, I have found that it is very slow.  It seems to populate cell-by-cell.  A 2000 record dataset can be retrieved from a database almost instantly, but the actual population of the Excel cells then takes 20 seconds.  Is this normal, or should I be expecting 2000 records to display instantly?

    2. For CopyFromRecordset, I could switch to ADO, but I don't get what Microsoft is suggesting.  Isn't ADO.NET the newer and better thing with datasets instead of recordsets?  Is Microsoft telling us that ADO is actually a retrograde?  What's the equivalent CopyFromRecordset for ADO.NET?

    Maybe binding the dataset to a Range is what I'm looking for.  But I don't want to bind to a control (as per the MSDN examples),  I just want to bind directly to the Excel cell.  Is this possible?

     

    It's important to realize that VSTO is not the Office application. VSTO is a set of tools that helps the .NET developer do certain things with the Office applications that Office itself does not provide. But the Office applications (with the exception of InfoPath and OneNote) are still firmly integrated in their traditional, "native" COM world.

     

    From this POV it's not surprising that Excel contains nothing in its object model that does anything with ADO.NET. Excel itself is completely classical VB(A)-oriented. CopyFromRecordset is the "latest and greatest" within the Excel object model for dumping a large amount of data directly into an opened workbook.

     

    Following this same logic, it's therefore not possible to bind anything in Excel directly to a .NET data source, unless you're using a VSTO control as a "go-between". Excel does have data-binding capabilities, but those are to standard databases, via OLE DB (ADO), ODBC, or to "flat table" XML files (in Excel 2003 and later).

    Monday, November 19, 2007 9:12 PM
  • Hi Cindy,
    I had set this aside for a while, but now I am coming back to this issue.

    I understand and appreciate what you say about Excel being COM-centric and that CopyFromRecordset is the latest and greatest that Excel 2003 will ever have and that I therefore would need to use ADO for this.  So I am now trying to use ADO in my project.

    I actually now want to access the recordset that is returned when using QueryTable, but I am confused about what reference I need to include in my VSTO Visual Studio 2005 Excel workbook project.  I see a reference object on the .NET tabbed called ADODB 7.0.3300.0.  So I tried including this in my project and am now able to use the following code:

    Dim oQryTable As QueryTable
    dim rst as ADODB.Recordset
    rst = oQryTable.Recordset

    But I get an error if I try to run the code.  The error is "Not a DAO recordset datasource."  So I'm surprised and confused because I think I'm only working with ADO and now I get an error message about DAO.

    I have another possible reference I could include in the COM tab called Microsoft ActiveX Data Objects 2.8 Library.  Is this the reference I am supposed to use?  What's the difference between the .NET and the COM references?

    I'm assuming I would have this same issue if I tried the CopyFromRecordset method of Excel, so that's why I'm replying to this same post.

    Thank you.

    Mark


    Monday, December 03, 2007 8:20 AM
  • Hi Mark

     

    You're skating at the edges of my knowledge, here :-) You might get better help in the Data.ADO newsgroup. I hesitate to send you to the Data Access or SQL Server forums, as they don't seem to want to support ADO for anything but SQL Server. In a pinch, though... Anyway, here's the link for the Data.ADO newsgroup

     

    http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.data.ado&lang=en&cr=US

     

    You might want to look at these discussions. I think the code samples in them should get you going:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1622542&SiteID=1

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1622333&SiteID=1

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1098870&SiteID=1

     

    From what I know, the problem is that you're mixing-and-matching technologies. QueryTable is part of the Jet engine (Access) Data Access Objects (DAO) object model. ADO doesn't have this. I'm actually surprised Visual Studio even lets you compile the code snippet you show us...

     

     

     

    Monday, December 03, 2007 3:49 PM
  • I had the same issue and couldn't find a solution. Then, one day I tried the folowing and it worked. It runs 16-18 times faster now. The dataset just contains about 500 rows. For bigger datasets, the improvement may less or more. I guess more.

    Why no one else was able to point it out? I don't know. I am a visual studio developer and just starting Office programming.

    My project is a Visual Studio project that uses Excel library version 12.0 and not the VSTO (I had to remove VSTO 2005 SE from my machine for other reasons) . However, earlier, I have tested it with VSTO as well.

    Code Block

    int rowIdx = 3, colStart = 1;

    int colCount = tbl.Columns.Count;

    foreach (manloadDataSet.manloadExports2Row row in tbl.Rows)

    {

    object rowArray = row.ItemArray;

    ws.get_Range(ws.Cells[rowIdx, colStart], ws.Cells[rowIdx, colStart + colCount - 1]).Value2 = rowArray;

    rowIdx++;

    //for (int c = 0; c < colCount; c++)

    //{

    // ((Range)ws.Cells[rowIdx, colStart + c]).FormulaR1C1 = row[c].ToString();

    //}

    //rowIdx++;

    }

     

     

    The old code is commented out.

    Goodluck.

    Khalique Rehman
    • Proposed as answer by Shaun Hayward Tuesday, March 24, 2009 3:23 PM
    Tuesday, December 04, 2007 5:33 PM
  • export data to excel from a recordset is the best way to do it.
    I achievement export 15,000 rows in 9 seconds, including the query to the database.

    Code


    Private Sub ExportExcel()
            Try
                'Create a Recordset from all the records in the Orders table.
                Dim conn As New ADODB.Connection()
                Dim rs As ADODB.Recordset
                conn.Open("Provider=sqloledb;
                           user id=USERNAME;password=PASSWORD;
                           data source=SERVER;Initial Catalog=DATABASE")
                conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient

                Dim mSQL As String = ""
                mSQL = "SELECT * FROM TABLE_NAME"

                rs = conn.Execute(mSQL)

                Dim app As Application
                app = New Application()

                app.Visible = False

                Dim workbooks As Workbooks
                workbooks = app.Workbooks

                Dim workbook As _Workbook
                workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet)

                Dim sheets As Sheets
                sheets = workbook.Worksheets

                Dim worksheet As _Worksheet
                worksheet = CType(sheets.Item(1), _Worksheet)

                worksheet.Range("A2").CopyFromRecordset(rs)

                app.Visible = True

            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End Sub
     

     

     

    http://www.forosdotnet.com/viewtopic.php?f=2&t=61&start=10

    Tuesday, May 13, 2008 9:54 PM
  • Khalique, your solution worked priliantly. Creating the object array and pasting it into the range made HUGE performance gains. And while there is no way to directly paste a DataRow or DataTable, your solution is close enough. Brilliant!
    Tuesday, March 24, 2009 3:26 PM
  • Hi,

    you could try GemBox.Spreadsheet to export DataSet to Excel . Export/import DataTable to Excel is supported within only one method call.
    Tuesday, January 19, 2010 9:39 AM