Exporting Gridview to Excel in web app

Question

Hopefully you guys can help me out. I tried a lot of different things and cant get this working.

I have a gridview as below in a update panel:

<asp:UpdatePanel ID="udpReport" runat="server" UpdateMode="Conditional" ChildrenAsTriggers="true">
      <ContentTemplate>
          <asp:GridView runat="server" ID="preferenceReportGrd" AutoGenerateColumns="false"
               AutoGenerateSelectButton="false" CaptionAlign="Top" EnableSortingAndPagingCallbacks="false" HorizontalAlign="left" CssSelectorClass="gvwPrefReport">
                   <Columns>
                       <asp:BoundField ReadOnly="true" DataField="ClientName" HeaderText="Company Name" />
                            <asp:BoundField ReadOnly="true" DataField="typeDescription" HeaderText="Preference" />
                            <asp:BoundField ReadOnly="true" DataField="defaultValue" HeaderText="Default Preference" />
                            <asp:BoundField ReadOnly="true" DataField="previousPreferenceValue" HeaderText="Previous Preference" />
                            <asp:BoundField ReadOnly="true" DataField="selectedValue" HeaderText="New Preference" />
                            <asp:BoundField ReadOnly="true" DataField="lastUpdated" HeaderText="Date Last Edited" />
                    </Columns>
          </asp:GridView>
          <div>
              <user:MsgLine runat="server" ID="MsgLine1" />
          </div>
     </ContentTemplate>
</asp:UpdatePanel>

I am trying to export this gridview out to excel. There is a button which the user clicks on it calls the on_click method for that button and in this on_click i have the following:

        string attachment = "attachment; filename=Employee.xls";            
        Response.AddHeader("content-disposition", attachment);
        Response.ContentType = "application/excel";
        StringWriter stw = new StringWriter();
        HtmlTextWriter htextw = new HtmlTextWriter(stw);
        preferenceReportGrd.RenderControl(htextw);
        Response.Write(stw.ToString());
        Response.End();

I get nothing from this tried debugging it seems that when i mouse over stw.tostring() all the values for the gridview are there but nothing gets written out.

weird not sure how to add the html for the gridview but just to give more info gridview is in an updatepanel and the update panel's updatemode is set to conditional. — , Jun 10 '09 at 15:53
Yup i tried getting rid of the update panel and still nothing. thanks — , Jun 10 '09 at 15:53
As a general StackOverflow rule, once you get an answer that solves your problem, you should mark it as such so that the answerer gets credit. But don't mark my answer correct if it did not solve your problem. — Matthew Jones, Jun 10 '09 at 16:28

niranjan raj · Answer 1 · 2012-03-15T12:54:09.123

3

Yes. within the update panel tag excel export is not working. I face the same problem. To solve this use the following code in end of the update panel tag

</ContentTemplate>          
       <Triggers>
        <asp:PostBackTrigger ControlID="BtnExport" />
    </Triggers>
        </asp:UpdatePanel>

it works even within the update panel also

edited Mar 15 '12 at 12:54

answered Mar 15 '12 at 12:36

niranjan raj

31
2

RSolberg · Answer 2 · 2009-06-10T16:01:44.233

2

If the GridView and/or the button is within in update panel with an async postback, I do not believe you can change the response headers or information. Try running it again with a full postback on the button trigger and see what happens. Your code did not look incorrect, but I've not tried it...

Take a look at this samples...
1. c-sharpcorner
2. Matt Berseth
3. Code Project

edited Jun 10 '09 at 16:01

answered Jun 10 '09 at 15:56

RSolberg

26,821
23
116
160

Those are the same examples i have been looking at to get my code to work. Thanks So i added a trigger for my button: I think i should also mention that the gridview and the button are in two different updatepanels. – Jun 10 '09 at 16:06
For starters, try putting the grid on a standard webform page without any ajax or update panels and see how that works... – RSolberg Jun 10 '09 at 16:11

score 1 · Answer 3 · edited May 23 '17 at 12:08

1

Check your content types and for a PostBackTrigger on the exporting button.

For Excel 2003

Response.ContentType = "application/vnd.ms-excel

For Excel 2007

Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

You can check out this blog post for the source of my content types.

EDIT: From your comments: You need to set a PostBackTrigger on the panel that contains the button doing the export, not the panel that contains the GridView.

EDIT EDIT: Your file-is-not-what-it-says-it-is error is due to a feature called Extension Hardening, present in Excel 2007. Check this StackOverflow question for more info; I linked to a blog post describing the reasoning for this error and a possible workaround.

edited May 23 '17 at 12:08

Community

1
1

answered Jun 10 '09 at 16:03

Matthew Jones

25,644
17
102
155

sir you are a Hero. Thank you so much for all your help. – Jun 10 '09 at 16:11
No problem. Also, you might want to be aware that in doing this kind of export, the file being exported is not a native Excel file. It is an HTML file that the Excel program can read. What I mean is, be sure to use Save As... and change the type to Microsoft Excel Workbook, otherwise it may be unreadable (I know because I ran into this exact problem myself). – Matthew Jones Jun 10 '09 at 16:13
the application/ms-excel seems to the standard used out there today for this. – RSolberg Jun 10 '09 at 16:16
@RSolberg: I tried that myself about a year ago and ran into almost the same problem as newbie. What you are suggesting should work, but maybe there are other factors we have not considered. – Matthew Jones Jun 10 '09 at 16:19
The only weird thing is when trying to open the file it says: The File you are tying to open, 'Employee.xls", is in a different format than specified by the file extension. I am thinking is exactly to what you mentioned in your comment. – Jun 10 '09 at 16:29
also i changed the contenttype to Response.ContentType = "application/vnd.ms-excel"; – Jun 10 '09 at 16:30
@newbie - If you hit "YES" or "OK" on that error message, does it work? – RSolberg Jun 10 '09 at 16:42
Good point RSolberg. It should open the file anyway, the dialog is just warning you that the file is not what it says it is. – Matthew Jones Jun 10 '09 at 16:43
yup file opens just fine by clicking yes. – Jun 10 '09 at 17:15

score 0 · Answer 4 · answered Jun 10 '09 at 18:04

The way I was able to work around this in a simple page that I was working on was to have a hidden gridview outside of the updatepanel and set it equal to the gridview in the update panel so that they are the same (except for the new one being hidden). Then export this hidden grid to Excel the same way that you were doing it before.

One issue with doing it this way is that the export hyperlink needs to be outside of the update panel as well which means that you might not be able to put the export hyperlink where you want it.

Granted this is probably not the best way to do it, it got me around this issue at the time. Hope it helps.

Exporting Gridview to Excel in web app

4 Answers4

Linked