Hi basit0079,
I have created sample that full-fill your requirement.
HTML
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="EmployeeName" HeaderText="EmployeeName" />
<asp:BoundField DataField="CourseNo" HeaderText="CourseNo" />
<asp:BoundField DataField="CourseName" HeaderText="CourseName" />
<asp:BoundField DataField="Status" HeaderText="Status" />
</Columns>
</asp:GridView>
<br />
<asp:Button ID="btnExport" runat="server" Text="Export To Excel" OnClick="ExportExcel" />
Code
VB.Net
Imports System.IO
Imports System.Data
Imports ClosedXML.Excel
Partial Class VB
Inherits System.Web.UI.Page
Private Function Data() As DataTable
Dim dt As New DataTable()
dt.Columns.AddRange(New DataColumn() {New DataColumn("EmployeeName"), New DataColumn("CourseNo"), New DataColumn("CourseName"), New DataColumn("Status")})
dt.Rows.Add("John Hammond", "0001", "Java", "NOTDONE")
dt.Rows.Add("Mudassar Khan", "0002", "ASP.Net", "VALID")
dt.Rows.Add("Suzanne Mathews", "0003", "SQL", "VALID")
dt.Rows.Add("Robert Schidner", "0004", "jQuery", "NOTDONE")
Return dt
End Function
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Dim dt As DataTable = Data()
GridView1.DataSource = dt
GridView1.DataBind()
End If
End Sub
Protected Sub ExportExcel(sender As Object, e As EventArgs)
Dim dt As DataTable = Data()
Using wb As New XLWorkbook()
Dim ws = wb.Worksheets.Add("worksheet")
If dt.Rows.Count > 0 Then
' Adding HeaderRow.
ws.Cell(1, 1).SetValue(dt.Columns(0).ColumnName)
ws.Cell(1, 2).SetValue(dt.Columns(1).ColumnName)
ws.Cell(1, 3).SetValue(dt.Columns(2).ColumnName)
ws.Cell(1, 4).SetValue(dt.Columns(3).ColumnName)
' Adding DataRows.
For i As Integer = 0 To dt.Rows.Count - 1
ws.Cell((i + 2), 1).SetValue(dt.Rows(i)(0))
ws.Cell((i + 2), 2).SetValue(dt.Rows(i)(1))
ws.Cell((i + 2), 3).SetValue(dt.Rows(i)(2))
ws.Cell((i + 2), 4).SetValue(dt.Rows(i)(3))
If dt.Rows(i)("status").ToString().ToUpper() = "VALID" Then
' Changing color to green.
ws.Cell((i + 2), 4).Style.Fill.BackgroundColor = XLColor.Green
ElseIf dt.Rows(i)("status").ToString().ToUpper() = "NOTDONE" Then
' Changing color to red.
ws.Cell((i + 2), 4).Style.Fill.BackgroundColor = XLColor.Red
End If
Next
End If
Response.Clear()
Response.Buffer = True
Response.Charset = ""
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
Response.AddHeader("content-disposition", "attachment;filename=" + DateTime.Now.ToString("dd-MMM-yyyy") + ".xlsx")
Using MyMemoryStream As New MemoryStream()
wb.SaveAs(MyMemoryStream)
MyMemoryStream.WriteTo(Response.OutputStream)
Response.Flush()
Response.[End]()
End Using
End Using
End Sub
End Class
C#
using System;
using System.Data;
using System.IO;
using ClosedXML.Excel;
public partial class CS : System.Web.UI.Page
{
private DataTable Data()
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[] {
new DataColumn("EmployeeName"),
new DataColumn("CourseNo"),
new DataColumn("CourseName"),
new DataColumn("Status") });
dt.Rows.Add("John Hammond", "0001", "Java", "NOTDONE");
dt.Rows.Add("Mudassar Khan", "0002", "ASP.Net", "VALID");
dt.Rows.Add("Suzanne Mathews", "0003", "SQL", "VALID");
dt.Rows.Add("Robert Schidner", "0004", "jQuery", "NOTDONE");
return dt;
}
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
DataTable dt = Data();
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
protected void ExportExcel(object sender, EventArgs e)
{
DataTable dt = Data();
using (XLWorkbook wb = new XLWorkbook())
{
var ws = wb.Worksheets.Add("worksheet");
if (dt.Rows.Count > 0)
{
// Adding HeaderRow.
ws.Cell("A" + 1).Value = dt.Columns[0].ColumnName;
ws.Cell("B" + 1).Value = dt.Columns[1].ColumnName;
ws.Cell("C" + 1).Value = dt.Columns[2].ColumnName;
ws.Cell("D" + 1).Value = dt.Columns[3].ColumnName;
// Adding DataRows.
for (int i = 0; i < dt.Rows.Count; i++)
{
ws.Cell("A" + (i + 2)).Value = dt.Rows[i][0];
ws.Cell("B" + (i + 2)).Value = dt.Rows[i][1];
ws.Cell("C" + (i + 2)).Value = dt.Rows[i][2];
ws.Cell("D" + (i + 2)).Value = dt.Rows[i][3];
if (dt.Rows[i]["status"].ToString().ToUpper() == "VALID")
{
// Changing color to green.
ws.Cells("D" + (i + 2)).Style.Fill.BackgroundColor = XLColor.Green;
}
else if (dt.Rows[i]["status"].ToString().ToUpper() == "NOTDONE")
{
// Changing color to red.
ws.Cells("D" + (i + 2)).Style.Fill.BackgroundColor = XLColor.Red;
}
}
}
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=" + DateTime.Now.ToString("dd-MMM-yyyy") + ".xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
}
}
Screenshot
