Geeks With Blogs
Brian Biales because blogging is just the easiest way to remember things

I know this topic has been covered in a lot of places, but I thought I'd post a real simple solution to this simple problem.  If you have a web page with a table / grid, and your users then tell you it would be great if they could download that into Excel, you have a few options.

With IE, if you right click on the page, there may be an option to "Export to Excel".  This sometimes works, but I've had pages where this returns nothing for some reason.  Also any formatting such as background color and so forth will get lost.  Only the data gets transferred.

Another simple option (simple for you, that is) is tell the user to right click, select to "view Source", and when the source is displayed in a text editor, save the file as "yourfile.XLS".  Excel can read html, so the extension just lets the user double click it in Explorer to bring it up in Excel.

But for a much more elegant solution, you can add a button to your page which will "download" your page as an Excel spreadsheet file which the user can then select to open right in the browser, or save to disk as a file.

Below is a simple .asp script that will output a sample table in HTML, but there is a button the user can push to download it as an Excel spreadsheet instead.  The only HTML difference between the two, is I've programmatically removed the buttton if downloading in Excel format, there is no need to include it at that point...

You'll see that this button simply navigates to this same page again, but with the asXLS parameter set to 1.  This flag tells the script to make some very small changes so that the browser gets this html output as an Excel spreadsheet instead of as a page to display.  It sets the content type as application/x-msexcel and content disposition as inline; filename=excelfile.xls.

Another cool feature this shows is how to make Excel format a long string of digits as a string of digits, rather than displaying it as if it were a large number in scientific notation.  There is a special microsoft office attribute you add to the <td> that will force this style: 'mso-number-format:\@'

So, here is the code.  I added vbCrLf to the end of many lines just so if you View Source on it, it'll look nice...  The filename of this example is xlsTest.asp:

<% @Language = VBScript %>
<%
    Dim asXLS
    asXLS = Request.QueryString("asXLS")

    If asXLS <> "" Then
        'asXLS param set, make this an inline Excel Spreadsheet
        Response.Clear
        Response.ContentType = "application/x-msexcel"
        Response.AddHeader "Content-Disposition","inline; filename=excelfile.xls"
    End If
    Response.Expires = -1
    Response.Buffer = True

    Response.Write "<html>" & vbcrlf
    Response.Write "<head>" & vbcrlf
    Response.Write "  <title>Save as Excel Spreadsheet Example</title>" & vbcrlf
    Response.Write "</head>" & vbcrlf
    Response.Write "<body>" & vbcrlf

    'Now add a button if this is the HTML version
    If asXLS = "" Then
        Response.Write "<button onclick=window.location='xlsTest.asp?asXLS=1'>Download as Excel Spreadsheet</button><br>" & vbcrlf
    End If

    Response.Write "<table border='1' cellspacing='1' cellpadding='10'>" & vbcrlf
    Response.Write "  <tr align='Center'>" & vbcrlf
    Response.Write "    <td bgcolor='LightBlue'>Text</td>" & vbcrlf
    Response.Write "    <td bgcolor='LightBlue'>Lots of Digits</td>" & vbcrlf
    Response.Write "  </tr>" & vbcrlf
    Response.Write "  <tr>" & vbcrlf
    Response.Write "    <td nowrap='1'>Text goes here</td>" & vbcrlf
    Response.Write "    <td style='mso-number-format:\@' nowrap='1'>354592000601219</td>" & vbcrlf
    Response.Write "  </tr>" & vbcrlf
    Response.Write "</table>" & vbcrlf
    Response.Write "</body>" & vbcrlf
    Response.Write "</html>" & vbcrlf
  Response.End
%>

If you find any flaws in this simple example let me know, or if there is a better way to navigate back to oneself without hardcoding the name of the asp page, that would be nice to know as well...

I could provide a PHP example, too, if anyone is interested.

EDITED 2:28PM Eastern:
I just found a great example if you are using ASP.NET with a DataGrid control, how to output that as a file very simply, by using the same ContentType and Content Disposition headers as above, but then asking the DataGrid to "render" itself into the resultant output page.  This is server side ASP.NET code:

http://www.wwwcoder.com/main/parentid/254/site/5334/68/default.aspx

EDITED one more time...
Turns out the script above used the Window.Navigate() function, which works great in IE, but Firefox doesn't support it.  So as I found in this post, the correct syntax to be compatible with both browsers is Window.Location="...".
The code above has been corrected.

Ok, last edit...   Here is the PHP implementation:

<?php
    #if the asXLS param set, make this html into an inline Excel Spreadsheet for download
    If ($_GET["asXLS"] <> "")
    {
        header("Content-type: application/x-msexcel");
        header("Pragma: ");
        header("Cache-Control: ");
        # replace excelfile.xls with whatever you want the filename to  default to
        header("Content-Disposition: attachment; filename=excelfile.xls");
    }
?>
<html>
<head>
  <title>Save as Excel Spreadsheet Example</title>
</head>
<body>
<?php If ($asXLS == "")
  echo "<button onclick=window.navigate('xlsTest.php?asXLS=1')>Download as Excel Spreadsheet</button><br>\n"
?>
<table border='1' cellspacing='1' cellpadding='10'>
  <tr align='Center'>
    <td bgcolor='LightBlue'>Text</td>
    <td bgcolor='LightBlue'>Lots of Digits</td>
  </tr>
  <tr>
    <td nowrap='1'>Text goes here</td>
    <td style='mso-number-format:\@' nowrap='1'>354592000601219</td>
  </tr>
</table>
</body>
</html>

Posted on Thursday, March 1, 2007 12:22 PM .NET | Back to top


Comments on this post: Downloading your web page with table or grid information into Excel

# Living in a Windows world
Requesting Gravatar...
Unfortunately VBscript is only supported on IE, Firefox says "no-no" (let alone the snort of disdain coming out of my Mac when it hits that code).
I would think that an approach with Javascript could yield similar results that would be more platform and browser agnostic.
Left by Joe Arcuri on Mar 01, 2007 3:09 PM

# re: Downloading your web page with table or grid information into Excel
Requesting Gravatar...
Ah, well, you are correct that your Mac would not appreciate this script, but not for the reasons you specify. The script is actually a server side ASP script for IIS consumption, but it returns what I believe to be browser agnostic HTML (or application/x-msexcel file contents). This script can easily be ported to PHP or any other server side scripting language if you need this capability on you Apache server, for instance.
Left by Brian Biales on Mar 01, 2007 5:29 PM

Your comment:
 (will show your gravatar)


Copyright © Brian Biales | Powered by: GeeksWithBlogs.net