In order to export to Excel, we need two things:
1. Some way to tell the page to switch into Excel rendering
mode (or a different page that only serves Excel).
2. Some way for the page to tell the browser that the
content should be interpreted as an Excel sheet.
The simplest way to achieve the first requirement is to add
a link to the page that says "Export to Excel" and simply links back
to the same page but adds a querystring parameter, like this:
It's not strictly required that this link have an id or a
runat="server" but this is useful if we want to avoid displaying this
link in our Excel report. With this in place, we can add a simple check to the
Page_Load() method in the codebehind to determine whether we should render
Excel:
Now, finally, we get to the good part. How do we convert
our simple HTML table into an Excel worksheet? The cool thing is, we don't
have to worry about that. Excel takes care of that for us, by automatically
converting HTML (and in particular, tables) into Excel's columns and rows
format. All we need to do is tell the browser that we want Excel to handle
this request, and provide a few clues like what the resulting download should
be named. We'll also use this opportunity to hide some HTML-only elements on
the page, like the Export to Excel link. The resulting RenderExcel() method
looks like this:
Whatever filename you specify is what the user will be
prompted to download, like so:
Excel may warn that the file you are opening is in a
different format than the extension (in this case, it's HTML even though we
told Excel it was an xls file). You can simply ignore this prompt, resulting
in Excel opening the file with results like this: