Export DataTable to Excel with row color based on condition using ClosedXml in C# and VB.Net

Hi Vincenzo67,

Please refer the below example.

Database

I have made use of the following table Students with the schema as follows.

I have already inserted few records in the table.

You can download the database table SQL by clicking the download link below.

Download SQL file

HTML Markup

<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" />

Namespaces

You need to import the following namespaces.

C#

using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using ClosedXML.Excel;

VB.Net

Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports ClosedXML.Excel

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        gvStudents.DataSource = this.GetData();
        gvStudents.DataBind();
    }
}
 
private DataTable GetData()
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    string query = "SELECT StudentId, StudentName, Status FROM Students";
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter(query, con))
        {
            using (DataTable dt = new DataTable())
            {
                sda.Fill(dt);
                return dt;
            }
        }
    }
}

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 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 conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim query As String = "SELECT StudentId, StudentName, Status FROM Students"
    Using con As SqlConnection = New SqlConnection(conString)
        Using sda As SqlDataAdapter = New SqlDataAdapter(query, con)
            Using dt As DataTable = New DataTable()
                sda.Fill(dt)
                Return dt
            End Using
        End Using
    End Using
End Function

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

Exported Excel File