Click here to Skip to main content
11,484,402 members (65,608 online)
Click here to Skip to main content

Import and Export Data from GridView

, 17 Nov 2007 CPOL 45.9K 1.4K 26
Rate this:
Please Sign up or sign in to vote.
Import and Export Data from GridView

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>

Points of Interest

I hope I tried to explain well. If you like this code, please vote for this article. Please!
Thank you.

History

  • 17th November, 2007: Initial post

License

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

Share

About the Author

KennyPatel
Software Developer (Senior)
India India
No Biography provided

Comments and Discussions

 
QuestionA cleaner way Pin
CikaPero19-Jul-11 22:57
memberCikaPero19-Jul-11 22:57 
Generalfailed to map path Pin
orked mohamed27-Mar-09 23:42
memberorked mohamed27-Mar-09 23:42 
GeneralExport girdview with paging Pin
gargvijay18-Mar-09 21:48
membergargvijay18-Mar-09 21:48 
Generalproblem about the excel file's head Pin
shi_ly10-Dec-08 23:45
membershi_ly10-Dec-08 23:45 
i need a title in my exported excel file.
i add something like "<h1 align=\"center\">here is the title!!!!</h1>\r\n";

of course this code works fine.
but when the first colum is checkbox or something i donot want it to display in the excel file ,i set that colum's width to 0px,so the first colum cannot be seen. but here is the problem,the height of the title is not enough,i cannot solve the problem

can you help me.
GeneralCould not find installable ISAM Pin
cky4251-Jul-08 18:01
membercky4251-Jul-08 18:01 
Generalthanx Pin
devendrak2003@yahoo.co.in11-Jun-08 9:34
memberdevendrak2003@yahoo.co.in11-Jun-08 9:34 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150520.1 | Last Updated 17 Nov 2007
Article Copyright 2007 by KennyPatel
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid