Click here to Skip to main content
15,887,027 members
Articles / Web Development / ASP.NET

Export GridView Data to Excel using OpenXml

Rate me:
Please Sign up or sign in to vote.
4.92/5 (8 votes)
15 Apr 2012CPOL 88.3K   7.6K   18  
ASP.NET GridView to Excel conversion.
using System;
using System.Data;
using System.IO;
using System.Web;
using System.Web.UI.WebControls;
using GridToExcel.Data;
using GridToExcel.Helper;

namespace GridToExcel
{
    public partial class Home : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindGrid();
            }
            SetChache();
        }

        private void SetChache()
        {
            Response.Cache.SetCacheability(HttpCacheability.Public);
            Response.Cache.SetExpires(DateTime.Now.AddYears(1));
        }

        private void BindGrid()
        {
            grdvTest.DataSource = new TestData().GetSampleData();
            grdvTest.DataBind();
        }

        protected void btnExport_Click(object sender, EventArgs e)
        {
            DataTable table = new DataTable();
            CreateTable(grdvTest, ref table);

            string file = new ExcelHelper().ExportToExcel(table);
            string rootPath = HttpContext.Current.Server.MapPath("~").ToString();
            string localCopy = Guid.NewGuid().ToString() + ".xlsx";
            File.Copy(file, rootPath + localCopy);

            Response.Redirect(localCopy);
        }

        private void CreateTable(GridView grdvTest, ref DataTable table)
        {
            // create columns
            for (int i = 0; i < grdvTest.HeaderRow.Cells.Count; i++)
                table.Columns.Add(grdvTest.HeaderRow.Cells[i].Text);

            // fill rows
            foreach (GridViewRow row in grdvTest.Rows)
            {
                DataRow dr;
                dr = table.NewRow();

                for (int i = 0; i < row.Cells.Count; i++)
                {
                    dr[i] = row.Cells[i].Text.Replace("&nbsp;", " ");
                }
                table.Rows.Add(dr);
            }
        }
    }
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions