Export DataTable to Excel and change and format cell color based on condition using ClosedXML Library in ASP.Net

Last Reply on Oct 24, 2016 09:12 AM By dharmendr

Posted on Oct 17, 2016 02:33 AM

Hi,

With the below code its successfully exporting to excel.

i want two things from below code

1) When Status="VALID" then background color should be green and when "NOTDONE" then red

2) After 

For Each dt As DataTable In ds.Tables
                                wb.Worksheets.Add(dt)
                            Next
// here i want to add multiple custom rows on Employee name column
after adding all employee on that dataset

 

Protected Sub ExportExcel(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click, btnExport.Click
        Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Dim query As String = "SELECT EmployeeName,CourseNo,CourseName,Status FROM Employee"

        Using con As New SqlConnection(constr)
            Using cmd As New SqlCommand(query)
                Using sda As New SqlDataAdapter()
                    cmd.Connection = con
                    sda.SelectCommand = cmd
                    Using ds As New DataSet()
                        sda.Fill(ds)

                        'Set Name of DataTables.
                        ds.Tables(0).TableName = "Employee"
                        Using wb As New XLWorkbook()
                            For Each dt As DataTable In ds.Tables
                                wb.Worksheets.Add(dt)
                            Next
                            Response.Clear()
                            Response.Buffer = True
                            Response.Charset = ""
                            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                            Response.AddHeader("content-disposition", "attachment;filename=" & Microsoft.VisualBasic.Strings.Format(Now, "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 Using
                End Using
            End Using
        End Using

    End Sub

 

Posted on Oct 17, 2016 09:05 AM Modified on on Sep 12, 2017 04:30 AM

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


Posted on Oct 24, 2016 09:12 AM

Closed XML doesn't have AutoFit functionality. So you need to assigh the height and width for this.

ws.ColumnWidth = 30;
ws.RowHeight = 20;

for border

XLBorderStyleValues bs = XLBorderStyleValues.Thin;
ws.Style.Border.SetOutsideBorder(bs);