Exporting a Recordset Result to XL

The Mighty Dubs · 15-07-2011 11:37am #1

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

Procasinator · 15-07-2011 3:59pm

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

COYW · 15-07-2011 4:12pm

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.

Evil Phil · 15-07-2011 6:26pm

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.

Exporting a Recordset Result to XL

Comments