The Circle of Dotnet: July 2014

Thursday, 31 July 2014

Export GridView data to excel using ASP.NET and c#

<title>Export Grid View to Excel</title>

</head>

<body>

<div>

<asp:ImageButton ID="imgbtnExcel" runat="server" ImageUrl="~/Images/excel_icon.png"

OnClick="imgbtnExcel_Click" />

<asp:GridView ID="gvDetails" runat="server" AutoGenerateColumns="false" ShowFooter="true"

Width="100%">

<asp:BoundField DataField="empid" HeaderText="Employee-ID" />

<asp:BoundField DataField="name" HeaderText="Name" />

<asp:BoundField DataField="designation" HeaderText="Designation" />

<asp:BoundField DataField="city" HeaderText="City" />

<asp:BoundField DataField="country" HeaderText="Country" />

</Columns>

</asp:GridView>

</div>

</form>

</body>

</html>

Here we have to use following method to avoid exception something like

Control 'gvDetails' of type 'GridView' must be placed inside a form tag with runat=server.

public override void VerifyRenderingInServerForm(Control control)

{

/* Verifies that the control is rendered */

}

Note:

You have to use following namespace

using System.IO;

C# Code :

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

using System.Data.SqlClient;

using System.IO;

public partial class _Default : System.Web.UI.Page

{

//get connection string from web.config

SqlConnection conn = new SqlConnection("Data Source=SPIDER;Initial Catalog=Demo; Integrated Security=True");

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

BindGrid();

}

public override void VerifyRenderingInServerForm(Control control)

{

/* Verifies that the control is rendered */

}

protected void BindGrid()

{

DataSet ds = new DataSet();

conn.Open();

string cmdstr = "Select * from EmployeeDetails";

SqlCommand cmd = new SqlCommand(cmdstr, conn);

SqlDataAdapter adp = new SqlDataAdapter(cmd);

adp.Fill(ds);

gvDetails.DataSource = ds;

gvDetails.DataBind();

conn.Close();

}

protected void imgbtnExcel_Click(object sender, ImageClickEventArgs e)

{

Response.ClearContent();

Response.Buffer = true;

Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Employees.xls"));

Response.ContentType = "application/ms-excel";

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

gvDetails.AllowPaging = false;

//Change the Header Row back to white color

gvDetails.HeaderRow.Style.Add("background-color", "#FFFFFF");

//Applying stlye to gridview header cells

for (int i = 0; i < gvDetails.HeaderRow.Cells.Count; i++)

{

gvDetails.HeaderRow.Cells[i].Style.Add("background-color", "#507CD1");

}

int j = 1;

//Set alternate row color

foreach (GridViewRow gvrow in gvDetails.Rows)

{

gvrow.BackColor = System.Drawing.Color.White;

if (j <= gvDetails.Rows.Count)

{

if (j % 2 != 0)

{

for (int k = 0; k < gvrow.Cells.Count; k++)

{

gvrow.Cells[k].Style.Add("background-color", "#EFF3FB");

}

j++;

}

gvDetails.RenderControl(htw);

Response.Write(sw.ToString());

Response.End();

}

Export GridView to word document:

If you want to export GridView records to word document you have to do very simple changes in above code

1.First change file extension to .doc instead of .xls

2.Change content type "application/ms-word" instead of "application/ms-excel"

Sample code:

Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Employees.doc"));

Response.ContentType = "application/ms-word";

- See more at: http://www.dotnetfox.com/articles/export-gridview-data-to-excel-using-Asp-Net-and-C-Sharp-1021.aspx#sthash.i8hUHiQ3.dpuf

How to export GridView data to PDF document in ASP.NET with C#

You could download iTextSharp.dll class library here

http://sourceforge.net/projects/itextsharp/

Then open your project and include that iTextSharp.dll class library.

Designer source code:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default"

EnableEventValidation="false" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"

"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

</head>

<body>

<div align="center"><h2><b>Export Gridview to PDF document</b></h2></div>

<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Export" />

<br />

<div>

<asp:GridView ID="gvExport" runat="server" EnableViewState="False" Width="60%" AllowPaging="false" AutoGenerateColumns="false">

<asp:BoundField DataField="ProductID" HeaderText="Product ID" />

<asp:BoundField DataField="ProductName" HeaderText="Product Name" />

<asp:BoundField DataField="SupplierID" HeaderText="Supplier ID" />

<asp:BoundField DataField="CategoryID" HeaderText="Category ID" />

<asp:BoundField DataField="QuantityPerUnit" HeaderText="Quantity Per Unit" />

</Columns>

</asp:GridView>

</div>

</form>

</body>

</html>

Here you have to use following namespaces

using System.IO;

using System.Net;

using iTextSharp.text;

using iTextSharp.text.pdf;

using iTextSharp.text.html.simpleparser;

C# code:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

using System.Data.SqlClient;

using System.IO;

using System.Net;

using iTextSharp.text;

using iTextSharp.text.pdf;

using iTextSharp.text.html.simpleparser;

public partial class _Default : System.Web.UI.Page

{

SqlConnection conn = new SqlConnection("Data Source=SPIDER;Initial Catalog=Northwind;Integrated Security=True");

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

BindData();

}

private void BindData()

{

DataSet ds = new DataSet();

conn.Open();

string cmdstr = " Select Top 10 ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit from Products ";

SqlDataAdapter adp = new SqlDataAdapter(cmdstr,conn);

adp.Fill(ds);

gvExport.DataSource = ds;

gvExport.DataBind();

conn.Close();

}

protected void Button1_Click(object sender, EventArgs e)

{

iTextSharp.text.Table table = new iTextSharp.text.Table(gvExport.Columns.Count);

table.Cellpadding = 2;

table.Width = 100;

BindData();

//Transfer rows from GridView to table

for (int i = 0; i < gvExport.Columns.Count; i++)

{

string cellText = Server.HtmlDecode

(gvExport.Columns[i].HeaderText);

iTextSharp.text.Cell cell = new iTextSharp.text.Cell(cellText);

cell.BackgroundColor = new Color(System.Drawing

.ColorTranslator.FromHtml("#93a31d"));

table.AddCell(cell);

}

for (int i = 0; i < gvExport.Rows.Count; i++)

{

if (gvExport.Rows[i].RowType == DataControlRowType.DataRow)

{

for (int j = 0; j < gvExport.Columns.Count; j++)

{

string cellText = Server.HtmlDecode

(gvExport.Rows[i].Cells[j].Text);

iTextSharp.text.Cell cell = new iTextSharp.text.Cell(cellText);

//Set Color of Alternating row

if (i % 2 != 0)

{

cell.BackgroundColor = new Color(System.Drawing.ColorTranslator.FromHtml("#dce0bc"));

}

table.AddCell(cell);

}

Document pdfDoc = new Document(PageSize.A4, 10f, 10f, 10f, 0f);

PdfWriter.GetInstance(pdfDoc, Response.OutputStream);

pdfDoc.Open();

pdfDoc.Add(table);

pdfDoc.Close();

Response.ContentType = "application/pdf";

Response.AddHeader("content-disposition", "attachment;" +

"filename=GridView.pdf");

Response.Cache.SetCacheability(HttpCacheability.NoCache);

Response.Write(pdfDoc);

Response.End();

}

Search This Blog

Thursday, 31 July 2014

Export GridView data to excel using ASP.NET and c#

How to export GridView data to PDF document in ASP.NET with C#