In this article I will explain with an example, how to change color of row in Excel file based on condition using ClosedXml in ASP.Net using C# and VB.Net.
 
 

Database

I have made use of the following table Students with the schema as follows.
Change color of row in Excel with ClosedXml using C# and VB.Net
 
I have already inserted few records in the table.
Change color of row in Excel with ClosedXml using C# and VB.Net
 
Note: You can download the database table SQL by clicking the download link below.
          Download SQL file
 
 

HTML Markup

The HTML Markup consists of following controls:
GridView – For displaying data.

Columns

The GridView consists of three BoundField columns.
Button – For exporting GridView data into Excel file.
The Button has been assigned with an OnClick event handler.
<asp:GridView ID="gvStudents" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="StudentId" HeaderText="Student Id" />
        <asp:BoundField DataField="StudentName" HeaderText="Student Name" />
        <asp:BoundField DataField="Status" HeaderText="Status" />
    </Columns>
</asp:GridView>
<br />
<asp:Button ID="btnExport" runat="server" Text="Export" OnClick="OnExport" />
 
 

Populating GridView with data from Database using C# and VB.Net

Inside the Page_Load event handler, the GetData method is called which returns the DataTable.
Finally, the DataTable is assigned to the DataSource property of the GridView and the GridView is populated.
C#
protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        gvStudents.DataSource = this.GetData();
        gvStudents.DataBind();
    }
}
 
private DataTable GetData()
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    string sql = "SELECT StudentId, StudentName, Status FROM Students";
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter(sql, con))
        {
            using (DataTable dt = new DataTable())
            {
                sda.Fill(dt);
                return dt;
            }
        }
    }
}
 
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        gvStudents.DataSource = Me.GetData()
        gvStudents.DataBind()
    End If
End Sub
 
Private Function GetData() As DataTable
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim sql As String = "SELECT StudentId, StudentName, Status FROM Students"
    Using con As SqlConnection = New SqlConnection(constr)
        Using sda As SqlDataAdapter = New SqlDataAdapter(sql, con)
            Using dt As DataTable = New DataTable()
                sda.Fill(dt)
                Return dt
            End Using
        End Using
    End Using
End Function
 
 

Changing color of row in Excel with ClosedXml using C# and VB.Net

When the Export button is clicked, a DataTable object is created using the GetData method.
An object of XLWorkbook class is created and a WorkSheet is added with name Students and data returned from the GetData method will be used as data.
Then, a FOR EACH loop is executed over the used rows of Excel except Header row and default background color is set.
After that, based on a specific condition, the background color of the row and font color of the text in that row is set.
Next, the Response class properties are set.
1. Content-Disposition – It is a response header indicating, the download file is an attachment and allows setting the file name.
Note: For more details on Content-Disposition header, please refer What is Content Disposition Header in ASP.Net.
 
2. ContentType – It informs the Browser about the file type. In this case it is Excel file.
Finally, the Excel file is downloaded.
C#
protected void OnExport(object sender, EventArgs e)
{
    // Creating DataTable.
    DataTable dt = this.GetData();
 
    //Creating Excel Workbook.
    XLWorkbook wb = new XLWorkbook();
 
    //Adding Worksheet.
    wb.Worksheets.Add(dt, "Students");
 
    //Referencing Worksheet Rows except header.
    List<IXLRow> rows = wb.Worksheet("Students").RowsUsed().Skip(1).ToList();
    foreach (IXLRow row in rows)
    {
        //Setting the default background color for the row.
        row.CellsUsed().Style.Fill.BackgroundColor = XLColor.FromHtml("#FFFFFF");
        if (row.Cell(3).Value.ToString() == "A")
        {
            //Setting the background color for the row.
            row.CellsUsed().Style.Fill.BackgroundColor = XLColor.FromHtml("#F44336");
            //Setting the font color for the row.
            row.CellsUsed().Style.Font.FontColor = XLColor.FromHtml("#FFFFFF");
        }
    }
 
    //Adjust widths of Columns.
    wb.Worksheet(1).Columns().AdjustToContents();
 
    //Export to Excel file.
    Response.Clear();
    Response.Buffer = true;
    Response.Charset = "";
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    Response.AddHeader("content-disposition", "attachment;filename=Students.xlsx");
    using (MemoryStream stream = new MemoryStream())
    {
        wb.SaveAs(stream);
        stream.WriteTo(Response.OutputStream);
        Response.Flush();
        Response.End();
    }
}
 
VB.Net
Protected Sub OnExport(sender As Object, e As EventArgs)
    'Creating DataTable.
    Dim dt As DataTable = Me.GetData()
 
    'Creating Excel Workbook.
    Dim wb As XLWorkbook = New XLWorkbook()
 
    'Adding Worksheet.
    wb.Worksheets.Add(dt, "Students")
 
    'Referencing Worksheet Rows except header.
    Dim rows As List(Of IXLRow) = wb.Worksheet("Students").RowsUsed().Skip(1).ToList()
    For Each row As IXLRow In rows
        'Setting the default background color for the row.
        row.CellsUsed().Style.Fill.BackgroundColor = XLColor.FromHtml("#FFFFFF")
        'Setting the color of Excel Row.
        If row.Cell(3).Value.ToString() = "A" Then
            'Setting the background color for the row.
            row.CellsUsed().Style.Fill.BackgroundColor = XLColor.FromHtml("#F44336")
            'Setting the font color for the row.
            row.CellsUsed().Style.Font.FontColor = XLColor.FromHtml("#FFFFFF")
        End If
    Next
 
    'Adjust widths of Columns.
    wb.Worksheet(1).Columns().AdjustToContents()
 
    'Export to Excel file.
    Response.Clear()
    Response.Buffer = True
    Response.Charset = ""
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    Response.AddHeader("content-disposition", "attachment;filename=Students.xlsx")
    Using stream As MemoryStream = New MemoryStream()
        wb.SaveAs(stream)
        stream.WriteTo(Response.OutputStream)
        Response.Flush()
        Response.End()
    End Using
End Sub
 
 

Screenshots

The Form

Change color of row in Excel with ClosedXml using C# and VB.Net
 

Exported Excel File

Change color of row in Excel with ClosedXml using C# and VB.Net
 
 

Demo

View Demo
 
 

Downloads

Download Code