Export C# Datatable to Excel Spreadsheet Css Style - Sample Application
Export to Excel with Cascading Style Sheets (css) format
In previous post I explained about Export Datatable to CSV C# - Sample Application, How to export web page as PDF with Image? - itextsharp, How to Export a Data Table to Excel - Asp.Net, XML, How to export dataset to excel in C#?, Export Gridview to Excel - Asp.Net.
In most of the application, we need to have export the data to Excel Spreadsheet file from grid view or data table. In this article we will see how to export datatable to Excel file in a very simple way, find the source code below:-
Default.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Export C# Datatable
to Excel Spreadsheet Css Style - Sample Application</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<br />
<asp:GridView ID="GridView1" runat="server"></asp:GridView>
<br />
<asp:Button ID="lnkbtnExporttoExcel" runat="server" Text="Export
Datatable to Formated Excel" OnClick="lnkbtnExporttoExcel_Click" />
</div>
</form>
</body>
</html>
Default.aspx.cs
using System;
using
System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using
System.Web.UI.WebControls;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Bindata();
}
}
private void Bindata()
{
DataTable productsDataTable =
new DataTable();
// create DataTable
object
// auto increment column for
productsDataTable
DataColumn dataColumn = new DataColumn("AutoID", System.Type.GetType("System.Int32"));
dataColumn.AutoIncrement = true;
dataColumn.AutoIncrementSeed = 1;
dataColumn.AutoIncrementStep = 1;
dataColumn.Unique = true;
productsDataTable.Columns.Add(dataColumn);
// product id column
dataColumn = new DataColumn("ProductID", System.Type.GetType("System.Int32"));
productsDataTable.Columns.Add(dataColumn);
// product name column
dataColumn = new DataColumn("ProductName", System.Type.GetType("System.String"));
productsDataTable.Columns.Add(dataColumn);
// quantity column
dataColumn = new DataColumn("ProductQuantity", System.Type.GetType("System.Int32"));
productsDataTable.Columns.Add(dataColumn);
// product price column
dataColumn = new DataColumn("ProductPrice", System.Type.GetType("System.Decimal"));
productsDataTable.Columns.Add(dataColumn);
// After Creating columns you
can add data rows to store the data dynamically
DataRow dataRow =
productsDataTable.NewRow();
dataRow["ProductID"] = 43;
dataRow["ProductName"] = "Ipoh
Coffee";
dataRow["ProductQuantity"] = 1;
dataRow["ProductPrice"] = 46.00;
productsDataTable.Rows.Add(dataRow);
dataRow = productsDataTable.NewRow();
dataRow["ProductID"] = 40;
dataRow["ProductName"] = "Boston Crab
Meat";
dataRow["ProductQuantity"] = 1;
dataRow["ProductPrice"] = 18.40;
productsDataTable.Rows.Add(dataRow);
dataRow = productsDataTable.NewRow();
dataRow["ProductID"] = 45;
dataRow["ProductName"] = "ABC";
dataRow["ProductQuantity"] = 12;
dataRow["ProductPrice"] = 95.00;
productsDataTable.Rows.Add(dataRow);
GridView1.DataSource =
productsDataTable;
GridView1.DataBind();
ViewState["GridData"] =
productsDataTable;
}
public override void
VerifyRenderingInServerForm(Control control)
{
//
}
/// <summary>
/// Export From
Gridview to formated Excel File
/// </summary>
/// <param
name="sender"></param>
/// <param
name="e"></param>
protected void lnkbtnExporttoExcel_Click(object sender, EventArgs e)
{
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", string.Format("attachment;
filename={0}",
"ProductMaster.xls"));
Response.ContentType = "application/ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
GridView1.AllowPaging = false;
DataTable dtExport = new DataTable();
dtExport = (DataTable)ViewState["GridData"];
GridView1.DataSource = dtExport;
GridView1.DataBind();
if
(dtExport.Rows.Count > 0)
{
//Change the Header Row back
to white color
GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");
//Applying stlye to gridview
header cells
for (int i = 0; i <
GridView1.HeaderRow.Cells.Count; i++)
{
GridView1.HeaderRow.Cells[i].Style.Add("background-color", "#507CD1");
}
int j = 1;
//This loop is used to apply
stlye to cells based on particular row
foreach (GridViewRow gvrow in GridView1.Rows)
{
gvrow.BackColor = Color.White;
if (j <= GridView1.Rows.Count)
{
if (j % 2 != 0)
{
for (int k = 0; k <
gvrow.Cells.Count; k++)
{
gvrow.Cells[k].Style.Add("background-color", "#EFF3FB");
}
}
}
j++;
}
GridView1.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}
else
{
//
}
}
}
Output:
Please leave your comments, suggestions and queries about this post in the comment sections in order for me to improve my writing skills and to showcase more useful posts. Thanks for reading .. :)
Download the source code click here
Export C# Datatable to Excel Spreadsheet Css Style - Sample Application
Reviewed by Ravi Kumar
on
4:29 PM
Rating:
Reviewed by Ravi Kumar
on
4:29 PM
Rating:
cheer
ReplyDelete