Issue Exporting Grid View Data To Excel

Question

I have to page in my web page that allows you to search for a list of names and it returns the results into a grid view. When the search result appears, the export button appears. I have the export button there for the user to click and it export the data from the grid view into excel in xlsx format. This is my current code:

ASP

<asp:TableCell>
     <asp:DataGrid ID="ResultGrid" runat="server"
      AutoGenerateColumns="false"
      EnableViewState="false" 
</asp:TableCell>
<asp:TableCell>
     <asp:Button class="myClass" ID="export" runat="server" OnClick="ExportBtn_Click" Text="Export" />
</asp:TableCell>

C#

protected void ExportBtn_Click(object sender, EventArgs e)
    {
        Response.Clear();
        Response.AddHeader("content-disposition", "attachment;filename=myexcelfile.xls");

        Response.ContentType = "application/vnd.xls";

        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);

        ResultGrid.RenderControl(htw);
        Response.Write(sw.ToString());
        Response.End();
    }

    public override void VerifyRenderingInServerForm(Control control)
    {

    }

Currently this downloads a blank excel file... even though my grid view has one item in it upon search.

My additional question (even though I know it is frowned upon), is how to export as xlsx instead of xls. If I replace the xls with xlsx, it tells me it is in the wrong format when I click my export button (when it tries to open up in excel).

To wrap up, my two question are: why is it returning nothing in excel even though I have a row returned in grid view and how to export as xlsx instead of xls?

Your aren't exporting as XLSX or XLS. You're exporting to HTML, which is masquerading as XLS because of the file extension. That's also frowned upon, because the user will get a warning when they open the document that it might be corrupted. Instead, you need to find a library capable of generating actual Open Office XML Spreadsheet files (.xlsx) and use that. I'm particularly fond of EPPlus, but note that asking for us to recommend a particular library is off topic. — mason
– mason, Commented Mar 1, 2015 at 20:10
I don't even understand your last comment. You're asking if there's a way to fix your code without changing your code? Clearly if you want to change the way it works, you have to change the code. — mason
– mason, Commented Mar 1, 2015 at 20:19
Correct, asking if you have any other input on how to fix my issue using my existing code — codeBoy
– codeBoy, Commented Mar 1, 2015 at 20:23
My first comment explains why your existing code cannot work. Therefore your existing code cannot and should not be used. — mason
– mason, Commented Mar 1, 2015 at 20:25

Rajesh Paliwal · Accepted Answer · 2016-05-10 10:55:06Z

0

Solved !!! the excel is blank because you are not passing any value to it

protected void ExportBtn_Click(object sender, EventArgs e)
{     
  ResultGrid();// Datagrid function to fill value 
 Response.ClearContent();
 Response.Buffer = true;
 Response.AddHeader("content-disposition", string.Format("attachment;    filename={0}", "myexcelfile.xls"));
 Response.ContentType = "application/ms-excel";
 StringWriter sw = new StringWriter();
 HtmlTextWriter ht = new HtmlTextWriter(sw);
 ResultGrid.RenderControl(ht);
 Response.Write(sw.ToString());
 Response.End();
}

answered May 10, 2016 at 10:55

Rajesh Paliwal

1061 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Harold Javier · Accepted Answer · 2015-03-02 07:33:25Z

-1

The reason why you're getting a blank Spreadsheet when you export to Excel because you're not doing it the right way.

You may use a third party like iTextSharp to do it correctly.

Actually, I wrote a working sample here in my blog: GridView To Excel

Hope it could help you.

answered Mar 2, 2015 at 7:33

Harold Javier

8972 gold badges7 silver badges16 bronze badges

3 Comments

mason Over a year ago

iTextSharp is a PDF library. While you may have found a way to get Excel to open the files you're making, there are much better ways of going around it. And linking to your blog is fine, but that can't be your entire answer. It'd be more appropriate to post that as a comment, as answers should be self contained.

Harold Javier Over a year ago

Understand that. But what I did was to simply offer a solution (a working solution). I gave a link to a working sample to guide him in finding answer to his problem.

mason Over a year ago

I understand you, but clearly you're not understanding me. The bulk of your answer can't rely on an external link which could break. You need to include the relevant portions from your link in your answer, or delete your answer and turn it into a comment.

Collectives™ on Stack Overflow

Issue Exporting Grid View Data To Excel

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related