Export Grid View to Excel

Question

Need help bringing my gridview into a excel document. Ive tried several example and can't seem to get it right. Any help would be much appreciated. Here is my gridview code, it is being populated from a SQL database.

<div>
    <asp:GridView Align="Center" ID="gvResults" AutoGenerateColumns="false" runat="server" Width="608px"
        Style="width: 80%; margin-bottom: 0px" CellPadding="4" ForeColor="#333333" GridLines="None"
        OnRowDataBound="gvResults_RowDataBound">
        <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
        <Columns>
            <asp:TemplateField>
                <ItemTemplate>
                    <asp:Image ID="imgDataMoreInfo" ImageUrl="T:\Groups\IntResoures\pic\tabpic.jpg" CssClass="img_data_moreinfo" Height="7" Width="7" runat="server" />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Government Name & Address">
                <ItemTemplate>
                    <asp:Label ID="lblGovernmentNameAddress" runat="server" Text=""></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:BoundField DataField="DOB" HeaderText="DOB" />
            <asp:TemplateField HeaderText="SSFHS Name & Address">
                <ItemTemplate>
                    <asp:Label ID="lblSSFHSNameAddress" runat="server" Text=""></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:BoundField DataField="ssfhsdob" HeaderText="DOB" />
            <asp:TemplateField HeaderText="Match Status">
                <ItemTemplate>
                    <asp:RadioButtonList ID="rblMatchStatus" runat="server" RepeatDirection="Vertical"></asp:RadioButtonList>
                    <asp:Label ID="lblFirst" CssClass="displayNone" runat="server" />
                    <asp:Label ID="lblSecond" CssClass="displayNone" runat="server" />
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
        <EditRowStyle BackColor="#999999" />
        <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
        <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
        <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
        <SortedAscendingCellStyle BackColor="#E9E7E2" />
        <SortedAscendingHeaderStyle BackColor="#506C8C" />
        <SortedDescendingCellStyle BackColor="#FFFDF8" />
        <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
    </asp:GridView>
</div>

My export button and event

<asp:Button ID="btnExportToExcel" OnClick="btnExportToExcel_Click" runat="server" Text="Export to Excel" Width="89px" Font-Size="X-Small" />




 protected void btnExportToExcel_Click(object sender, EventArgs e)
{

}

Take a look at this: http://www.codeproject.com/Tips/477436/Export-Gridview-Data-to-Excel-in-ASP-NET — Ani, Jun 11 '13 at 15:18
Almost all answer here write a (HtmlTextWriter) string or have Interop code. DO NOT USE EITHER. This will cause you problems later on with DateTime and Decimal formatting. Also Excel will give a warning because you are not generating a "real" Excel file but a HTML page with an .xls extension. Start using a specialized library for creating Excel files, like [EPPlus](https://github.com/JanKallman/EPPlus). [Example here](https://stackoverflow.com/a/47293207/5836671) and [here](https://stackoverflow.com/a/39513057/5836671). — VDWWD, Dec 21 '18 at 09:25

score 2 · Accepted Answer · answered Jun 11 '13 at 15:47

2

Here's a more specific one with respective to your code:

 protected void btnExportToExcel_Click(object sender, EventArgs e)
{
    Response.Clear();

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

    Response.Charset = "";

    // If you want the option to open the Excel file without saving than

    // comment out the line below

    // Response.Cache.SetCacheability(HttpCacheability.NoCache);

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

    System.IO.StringWriter stringWrite = new System.IO.StringWriter();

    System.Web.UI.HtmlTextWriter htmlWrite =
    new HtmlTextWriter(stringWrite);

    gvResults.RenderControl(htmlWrite);

    Response.Write(stringWrite.ToString());

    Response.End();

}

answered Jun 11 '13 at 15:47

Ani

4,473
4
26
31

Thanks, Onclick its attempting to do something but then nothing happens it just go back to the table view. with this error Line: 940 Error: Sys.WebForms.PageRequestManagerServerErrorException: Control 'ContentPlaceHolder1_gvResults' of type 'GridView' must be placed inside a form tag with runat=server. – user2425793 Jun 11 '13 at 16:04
You have to place your gridview inside a form with runat=server – Ani Jun 11 '13 at 16:06
// GridView here – Ani Jun 11 '13 at 16:07
Thanks!. getting a "A page can have only one server-side Form tag." now. Sorry kinda new and not used to form tags – user2425793 Jun 11 '13 at 17:45
You need to remove any other form tag with runat=server. Otherwise, you can have place your Gridview inside the current form that is already present. – Ani Jun 11 '13 at 17:48

score 1 · Answer 2 · answered Jun 12 '13 at 02:44

I just did this last week.

Here's the code you'll need to place in your aspx file (you can play with the height and width to your liking):

<asp:Button ID="ButtonA" runat="server" height="20px" OnClick="ButtonA_Click" Text="Export me" Width="200px />

In the Page section at the top of your aspx page insert:

EnableEventValidation="false"

In your cs file place the following below the 'protected void Page_Load...' section:

Protected void ButtonA_Click(object sender, EventArgs e)
{
     Response.Clear();
     Response.AddHeader("content-disposition", "attachment;filename=Filename.xls");
     Response.ContentType = "application/vnd.xlsx";
     System.IO.StringWriter stringWrite = new System.IO.StringWriter();
     System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
     GridView1.RenderControl(htmlWrite);
     Response.Write(stringWrite.ToString());
     Response.End();
}

Ignore the errors appear and click 'Yes'. The errors appear because the file generated is not pure Excel file type. It produces a file that is essentually text. Save it as a spreadsheet and you'll be good to go.

Onclick it isn't doing anything. Attempting to but no excel doc is being generated. Unsure what im missing. Thanks for your help. — user2425793, Jun 12 '13 at 14:25
Can you elaborate on this? What do you mean 'isn't doing anything'? Try switching out the instances of ButtonA with Button1. Are you using IE for this or another browser? I've just tested this on IE and no other browser. — Techie Joe, Jun 12 '13 at 18:58

score 0 · Answer 3 · answered Jul 08 '13 at 06:56

normally while writing to excel as

GridView1.RenderControl(htmlWrite);
 Response.Write(stringWrite.ToString());

will simply save it as html table to the excel not binary. Check as if you are concern.

there is same problem in my project as well.

then,

i simply create template of the xcel file empty in my project folder ,

then i write data to it .

string filePath = Server.MapPath("~/ReportFiles/CenterMemberAddressDifferentReport_" + Emp_Session._UserMasterId + ".xls");
    System.IO.File.Copy(Server.MapPath("~/Images/ExcelTemplate.xls"), filePath, true);

        System.Threading.Thread.Sleep(1000);

        string driver = "provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filePath + "';Extended Properties=Excel 8.0;";
        System.Data.OleDb.OleDbConnection Con = new System.Data.OleDb.OleDbConnection(driver);
        System.Data.OleDb.OleDbCommand Com = Con.CreateCommand();
        System.Data.OleDb.OleDbDataAdapter reader = new OleDbDataAdapter();
        Con.Open();
        DataTable dtSheet = Con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        string SQL = "";

        foreach (DataRow Row in dt1.Rows)
        {
            string values = "";
            for (int ik = 0; ik < dt1.Columns.Count; ik++)
            {
                if (values != "") values += ",";
                values += "'" + Row[ik].ToString() + "'";
            }
            SQL = "INSERT INTO [Sheet1$] ([CENTER NAME],[CENTER ADDRESS],[MEMBER ID],[MEMBER NAME],[MEMBER ADDRESS],[MEMBER COUNT]) VALUES(" + values + ")";
            Com.CommandText = SQL;
            Com.ExecuteNonQuery();
        }
        Con.Close();

        System.Threading.Thread.Sleep(1000);

        System.IO.FileStream stream = System.IO.File.OpenRead(filePath);
        byte[] by = new byte[stream.Length];
        stream.Read(by, 0, by.Length);
        stream.Close();
        System.Threading.Thread.Sleep(1000);
        System.IO.File.Delete(filePath);

        Response.OutputStream.Write(by, 0, by.Length);
        Response.End();

basically i was in case of importing the same excel to another datasource and was seccesfull.

Hope it will help you aswell. Plz feedbak are requested. Thank you for the beautifull question.

Export Grid View to Excel

3 Answers3

there is same problem in my project as well.

Linked