How to export gridview to excel & Word file with formatting in asp.net

Introduction

In this post I am explain how to export gridview to Excel & Word file with formatting (css file) in asp.net.

Steps :

Step - 1 : Create New Project.

Go to File > New > Project > Select asp.net web forms application > Entry Application Name > Click OK.

Step-2: Add a Database.

Go to Solution Explorer > Right Click on App_Data folder > Add > New item > Select SQL Server Database Under Data > Enter Database name > Add.

Step-3: Create table for fetch data.

Open Database > Right Click on Table > Add New Table > Add Columns > Save > Enter table name > Ok.

In this example, I have used one tables as below

Step-4: Add Entity Data Model.

Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select ADO.net Entity Data Model under data > Enter model name > Add.
A popup window will come (Entity Data Model Wizard) > Select Generate from database > Next >
Chose your data connection > select your database > next > Select tables > enter Model Namespace > Finish.

Step-5: Add a Webpage and Design for Show Data in Gridview.

Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select web form/ web form using master page under Web > Enter page name > Add.

HTML Code

    <link href="myGrid.css" rel="stylesheet" />
    <h3>Export Gridview in Word & Excel</h3>
    <br />
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" CellPadding="5" CssClass="myGrid" 
        AlternatingRowStyle-CssClass="alt" >
        <Columns>
            <asp:BoundField HeaderText="Rank" DataField="Rank" />
            <asp:BoundField HeaderText="River Name" DataField="RiverName" />
            <asp:BoundField HeaderText="Length (KM)" DataField="LengthInKM" />
            <asp:BoundField HeaderText="Drainage Area" DataField="DrainAgeArea" />
            <asp:BoundField HeaderText="Avarage Discharge" DataField="AvarageDischarge" />
            <asp:BoundField HeaderText="Outflow" DataField="Outflow" /> 
        </Columns>
    </asp:GridView>
    <div>
        <asp:Button ID="btnExportWord" runat="server" Text="Export To Word" OnClick="btnExportWord_Click" />&nbsp;
        <asp:Button ID="btnExportExcel" runat="server" Text="Export To Excel" OnClick="btnExportExcel_Click" />
    </div>

Step-6: Add a CSS (Style Sheet) file for format Gridview.

Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select Style Sheet under Web > Enter page name > Add.

CSS Code

    
.myGrid {
    width:100%;
    background-color:#fff;
    margin:5px 0px 10px 0px;
    border: solid 1px #525252;
    border-collapse:collapse;
}

.myGrid td {
    padding:2px;
    border: solid 1px #c1c1c1;
    color: #717171;
}

.myGrid th {
        padding:4px 2px;
        color:#fff;
        background-color:#424242;
        border-left:solid 1px #525252;
        font-size:0.9em;
}

.myGrid .alt {
    background-color:#EFEFEF;
}

Step-7: Write following code in Page_Load event for Show data in Gridview.

            if (!IsPostBack)
            {
                PopulateData();
            }

Here is the function...

            private void PopulateData()
            {
                using (MyDatabaseEntities dc = new MyDatabaseEntities())
                {
                    GridView1.DataSource = dc.Rivers.ToList();
                    GridView1.DataBind();
                }
            }

Step-8: Write below code in a function for export data from gridview to Excel/Word File with formatting.

        private void ExportGrid(string fileName, string contentType)
        {
            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment;filename="+fileName);
            Response.Charset = "";
            Response.ContentType = contentType;

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


            // Read Style file (css) here and add to response 
            FileInfo fi = new FileInfo(Server.MapPath("~/myGrid.css"));
            StringBuilder sb = new StringBuilder();
            StreamReader sr = fi.OpenText();
            while (sr.Peek() >= 0)
            {
                sb.Append(sr.ReadLine());
            }
            sr.Close();

            GridView1.RenderControl(HW);
            Response.Write("<html><head><style type='text/css'>"+sb.ToString()+"</style></head><body>"+sw.ToString()+"</body></html>");
            Response.Flush();
            Response.Close();
            Response.End();

        }

Step-9: Write code in button_click event for export gridview to Word file.

             protected void btnExportWord_Click(object sender, EventArgs e)
            {
                // Export Gridview to Word
                ExportGrid("GridviewData.doc", "application/vnd.ms-word");
            }

Step-10: Write code in button_click event for export gridview to Excel file.

            protected void btnExportExcel_Click(object sender, EventArgs e)
            {
                // Export Gridview to Excel
                ExportGrid("GridviewData.xls", "application/vnd.ms-excel");
            }

And this function is also required to add. This is required to solve this problem --> Control 'MainContent_GridView1' of type 'GridView' must be placed inside a form tag with runat=server.

             public override void VerifyRenderingInServerForm(Control control)
            {
                // this is required for avoid error (control must be placed inside form tag)
            }

Step-11: Run Application.

Download Live Demo

Related Post :