-->

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 : 
  1. How to export selected rows from gridview to excel in asp.net
  2. How to make Scrollable GridView with a Fixed Header (freeze row) in .NET
  3. How to group columns in gridview header row in ASP.NET (programmer friendly way)
  4. How to Marge Gridview adjacent cells depending on cells value in ASP.NET
  5. How to load gridview rows on demand from database through scrolling in ASP.NET
  6. How to apply Databar formatting on Gridview div like Excel conditional formatting options.
  7. How to apply formatting on Gridview based on condition div like Excel conditional formatting options.


Hello ! My name is Sourav Mondal. I am a software developer working in Microsoft .NET technologies since 2010.

I like to share my working experience, research and knowledge through my site.

I love developing applications in Microsoft Technologies including Asp.Net webforms, mvc, winforms, c#.net, sql server, entity framework, Ajax, Jquery, web api, web service and more.