Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Exporting a Recordset Result to XL

Options
  • 15-07-2011 12:37pm
    #1
    The Mighty Dubs
    Registered Users Posts: 224 ✭✭


    Hi

    I have an old site which is running ASP VBScript and reading from a SQL Server Database.

    On one of the pages of the site, I want to a facility to export the results of the recordset to an Excel. I have never done it before and would appreciate any help.

    Many thanks


Comments

  • Options
    #2
    Procasinator
    Registered Users Posts: 1,311 ✭✭✭Procasinator


    The easiest way to do is, unless you are concerned about formatting, is too use CSV.

    Something like:
    http://911-need-code-help.blogspot.com/2009/07/export-recordset-data-to-csv-using.html


  • Options
    #3
    COYW
    Closed Accounts Posts: 2,930 ✭✭✭COYW


    You can also write it out as a html table to a file named .xls and it will open fine in Excel. Below is a C# example, hope you get the idea.
    string _FileContent = "<html><body><table border='1'><tr><th>Header One</th></tr>";

// Loop through the rows in your table and inside add the contents to the string.
foreach(DataRow _drow in _dt.Rows){
    _FileContent += "<tr><td>" + _drow[0].ToString() + "</td></tr>";
}

_FileContent += "</table></body></html>";

    Then just write the string out to a file named .xls and its fine. _dt is the datatable.


  • Options
    #4
    Evil Phil
    Registered Users Posts: 7,468 ✭✭✭Evil Phil


    COYW wrote: »
    You can also write it out as a html table to a file named .xls and it will open fine in Excel. Below is a C# example, hope you get the idea.
    string _FileContent = "<html><body><table border='1'><tr><th>Header One</th></tr>";

// Loop through the rows in your table and inside add the contents to the string.
foreach(DataRow _drow in _dt.Rows){
    _FileContent += "<tr><td>" + _drow[0].ToString() + "</td></tr>";
}

_FileContent += "</table></body></html>";

    Then just write the string out to a file named .xls and its fine. _dt is the datatable.

    That's should do it, you'll need to set the mime type too IIRC.

    I've written directly to an excel spreadsheet using Jet OLE DB 4.0, I haven't done this with classic ADO though, but it should be possible. This can be overkill for a straight forward excel sheet though, COYW's solution would be best there. However if you need to run a macro when the sheet is open, to create a pivot table or something, the the Jet solution is better. Here's the connection string if you want to do this.


Advertisement