Here I will explain how to gridview export as text to excel in asp.net using c# with example.When we export data from gridview then auto data converting
according excel data type then our data may be lost. We can stop losting data if we convert gridview to text before export to excel file. Lets see code.
Code: GridViewExporttoExcel.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="GridGridViewExporttoExcel.aspx.cs"
Inherits="LearnAsp.Net.ControlDemo.GridView.GridViewExporttoExcel" %>
<!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>Displaying Column Summaries</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<h1>
Employee Info</h1>
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="conditional">
<Triggers>
<asp:PostBackTrigger ControlID="btnExportExcel" />
</Triggers>
<ContentTemplate>
<asp:Button ID="btnExportExcel" Text="Download Excel" runat="server" Style="margin-bottom: 10px;"
OnClick="btnExportExcel_Click" />
</ContentTemplate>
</asp:UpdatePanel>
<asp:GridView ID="grid" runat="server" AutoGenerateColumns="false" CellPadding="4"
ShowFooter="true" OnRowDataBound="grid_RowDataBound" ForeColor="#333333">
<Columns>
<asp:BoundField DataField="empno" HeaderText="Emp No" />
<asp:BoundField DataField="ename" HeaderText="Name" />
<asp:TemplateField HeaderText="Salary">
<ItemTemplate>
<%# Eval("sal", "{0:c}")%>
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lblSummary" runat="server" />
</FooterTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#5D7B01" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<EditRowStyle BackColor="#999999" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
</div>
</form>
</body>
</html>
Code: GridView09CustomHeader.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OracleClient;
using System.Data;
using System.Configuration;
using System.IO;
namespace LearnAsp.Net.ControlDemo.GridView
{
public partial class GridViewExporttoExcel : System.Web.UI.Page
{
private decimal _OfficeTotalSalary = 0;
static string strConn = ConfigurationManager.ConnectionStrings["LearnAsp"].ToString();
private OracleConnection objConn = new OracleConnection(strConn);
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack) { GridBind(); }
}
protected void GridBind()
{
String query = " select * from emp ";
OracleCommand objCmd= new OracleCommand(query, objConn);
DataTable dt = new DataTable();
objConn.Open();
OracleDataAdapter oda = new OracleDataAdapter(objCmd);
oda.Fill(dt);
objConn.Close();
if (dt.Rows.Count > 0)
{
grid.DataSource = dt;
grid.DataBind();
}
else
{
grid.DataSource = dt.NewRow();
grid.DataBind();
int columncount = grid.Rows[0].Cells.Count;
grid.Rows[0].Cells.Clear();
grid.Rows[0].Cells.Add(new TableCell());
grid.Rows[0].Cells[0].ColumnSpan = columncount;
grid.Rows[0].Cells[0].Text = "No Records Found";
}
}
protected void grid_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
decimal OfficeSalary = (decimal)DataBinder.Eval(e.Row.DataItem, "sal");
_OfficeTotalSalary += OfficeSalary;
}
if (e.Row.RowType == DataControlRowType.Footer)
{
Label lblSummary = (Label)e.Row.FindControl("lblSummary");
lblSummary.Text = String.Format("Total: {0:c}", _OfficeTotalSalary);
}
}
protected void btnExportExcel_Click(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.ClearContent();
Response.ClearHeaders();
Response.Charset = "";
string FileName = "Employee_Details_" + DateTime.Now.ToString("ddMMyyyyhhmmss") + ".xls";
StringWriter strwritter = new StringWriter();
HtmlTextWriter htmltextwrtter = new HtmlTextWriter(strwritter);
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename=" + FileName);
grid.GridLines = GridLines.Both;
grid.HeaderStyle.Font.Bold = true;
string style = @"<style> .textmode { mso-number-format:\@; } </style>";
foreach (GridViewRow row in grid.Rows)
{
for (int i = 0; i < row.Cells.Count; i++)
{
if (row.RowType == DataControlRowType.DataRow)
{
row.Cells[i].Attributes.Add("class", "textmode");
}
}
}
grid.RenderControl(htmltextwrtter);
String sTemp ="<table><tr><td>"+ strwritter.ToString()+"</td></tr></table>";
Response.Write("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" />");
Response.Write(style);
Response.Write(sTemp.ToString());
Response.End();
}
public override void VerifyRenderingInServerForm(Control control)
{ /* Do nothing */ }
}
}
Output:
In above code we have added one for loop for converting gridview all column data to text using row.Cells[i].Attributes.Add("class", "textmode") property data export as text in excel file.
No comments:
Post a Comment
Please do not enter any spam link in the comment box.