Import and Export Data from GridView

Download source - 1.68 KB

Introduction

I am not a good article writer. I am glad to have this opportunity to present this article to you. This article will help you understand how to Import and Export the GridView data into Microsoft Excel format.

Using the Code

Use this code in your codebehind file:

//
// Code Behind
//

using System.Data.OleDb;
using System.IO;

public partial class Forms_WebImportExport : System.Web.UI.Page
{
    public void fillGrid()
    {
        DataSet ds = new DataSet();
        ds.ReadXml(Server.MapPath("../xmlTest.xml"));
        GridView1.DataSource = ds;
        GridView1.DataBind();
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {    
            fillGrid();
        }
    }
    protected void btImport_Click(object sender, EventArgs e)
    {
        string conn = ("Provider=Microsoft.Jet.OLEDB.4.0;" + 
			("Data Source=C:\\path\\test.xls;" + 
                       	// or use instead of Excel 8.0 - Excel 5.0
			"Extended Properties=\"Excel 8.0;\"")); 
			 string SSQL = "SELECT name , dept, salary from [sheet1$]";

        // here use oleDataReader
        OleDbDataAdapter oleDA = new OleDbDataAdapter(SSQL, conn); 
					
        DataSet ds = new DataSet();
        //oleDA.TableMappings.Add("Table","ExcelTest"); // Require
        oleDA.Fill(ds);
        GridView1.DataSource = ds.Tables[0].DefaultView; // or [ ds ]
        GridView1.DataBind();
    }
    protected void btExport_Click(object sender, EventArgs e)
    {
        ExportGridToExcel(GridView1, "myExcel");
    }
    public void ExportGridToExcel(GridView grdGridView, string fileName)
    {
        Response.Clear();
        Response.AddHeader("content-disposition",string.Format
			("attachment;filename={0}.xls",fileName));
        Response.Charset = "";
        Response.ContentType = "application/vnd.xls";
        StringWriter stringWrite = new StringWriter();
        HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
        GridView1.RenderControl(htmlWrite);
        Response.Write(stringWrite.ToString());
        Response.End();
    }
    public override void VerifyRenderingInServerForm(Control control)
    {
        /* method must be implemented when using RenderControl */
    }
}

HTML CODE

// HTML CODE
<%@ Page Language="C#" AutoEventWireup="true" 
    CodeFile="WebImportExport.aspx.cs" Inherits="Forms_WebImportExport" %>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="btImport" runat="server" OnClick="btImport_Click" Text="Import" /> 
<asp:Button ID="btExport" runat="server" OnClick="btExport_Click" Text="Export" /><br />
<br />
<asp:GridView ID="GridView1" runat="server" BackColor="White" BorderColor="#E7E7FF"
BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Horizontal">
<FooterStyle BackColor="#B5C7DE" ForeColor="#4A3C8C" />
<RowStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="#F7F7F7" />
<PagerStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" HorizontalAlign="Right" />
<HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#F7F7F7" />
<AlternatingRowStyle BackColor="#F7F7F7" />
</asp:GridView>
<br />
</div>
</form>
</body>
</html>