Suppose in showing data in a DataGrid, there are so many columns in table that we can not see them easily in DataGrid. Then we can show our data in an excel sheet. For doing this we use a rendercontrol.
The aspx code for this application is:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Grid Record In Excel Sheet</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DataGrid ID="GridAllRecord" runat="server" AllowPaging="true"
OnPageIndexChanged="gridedit_PageIndexChanged" PageSize="5" >
</asp:DataGrid>
</div>
</form>
</body>
</html>
The aspx.cs code:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
GridAllRecord.AllowPaging = false;
bindData();
Response.Clear();
Response.Buffer = true;
Response.ContentType="application/vnd.ms-excel";
Response.Charset = "";
this.EnableViewState = false;
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
GridAllRecord.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
}
SqlConnection con;
SqlDataAdapter da;
SqlCommand cmd = new SqlCommand();
DataSet ds = new DataSet();
public void bindData()
{
con = new SqlConnection("Data Source=MCN101;Initial Catalog=Data; Uid=sa; pwd=");
cmd.CommandText = "select pg_FirstName,pg_LastName,pg_StudioName, pg_Address, pg_Phone,pg_City, pg_State, pg_Country from tbl_PhotographerProfile";
cmd.Connection = con;
da = new SqlDataAdapter(cmd);
da.Fill(ds);
con.Open();
cmd.ExecuteNonQuery();
GridAllRecord.DataSource = ds;
GridAllRecord.DataBind();
}
public void gridedit_PageIndexChanged(object source, DataGridPageChangedEventArgs e)
{
GridAllRecord.CurrentPageIndex = e.NewPageIndex;
bindData();
}
}
When user run the application then the window will look like this:
Figure 1.
From here we can open the data in excel sheet and we can also save our data in excel sheet.
Figure 2.
Figure 3: If we want to save the excel sheet.