Sunday, July 13, 2008

Exporting data to Excel Part 2

Another way to export to excel is to changing the content type. This is easily done by using Setting the response.contenttype property as follows:

Response.ContentType = "application/"

Since in the code behind will execute first before any of the aspx code will execute, the browser will receive this information and launch excel accordingly. However, the file will still show the original page name. To override that you need to add in the disposition as follows:

Response.AddHeader("content-disposition", "attachment;filename=Data.xls")

This will cause excel to recognize the page as Data.xls.

Additional things that you should do is if your page include menu controls like dropdownlist, checkbox etc, you may want to enclose them into a panel and set the panel visible properties to false so that it does not get rendered:
<asp:panel id="notInExcel" runat="server">
<asp:DropDownList id="frDy" runat="server" Width="40px"></asp:DropDownList>
<asp:DropDownList id="frMth" runat="server" Width="55px"></asp:DropDownList>
<asp:TextBox id="frYr" runat="server" Width="40px"></asp:TextBox>

This approach is better than the first approach because:
  1. You don't have to loosen the security in your browser.
  2. It works with almost any browser.
  3. It will open any program that supports the .xls file include (Open Office). You don't have render your data separately than what you are already doing to display on the screen.
Disadvantage of this approach is:
  1. When the person clicks the first time to display on the screen, and he submits a second time to export to excel, the program need to requery the data.
  2. If you refer to a css file using relative addressing, it will not carry over to excel so you will lose your formatting and also receive any error indicating that the css file cannot be found.
Relative addressing of css looks like this:

<LINK REL="stylesheet" HREF="stylesheets/pageFormat.css" TYPE="text/css">

Link to Previous article:
Part 1

No comments: