ASP.Net: Dynamically set Excel cell font color based on condition using EPPLUS Library

Hi alibasha,

Check this example. Now please take its reference and correct your code.

Namespaces

C#

using System.Data;
using System.Drawing;
using System.IO;
using System.Threading;
using OfficeOpenXml;
using OfficeOpenXml.Table;

VB.Net

Imports System.Data
Imports System.Drawing
Imports System.IO
Imports System.Threading
Imports OfficeOpenXml
Imports OfficeOpenXml.Table

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[3]
    {
        new DataColumn("Id"),
        new DataColumn("Name"),
        new DataColumn("Country")
    });
    dt.Rows.Add(1, "John Hammond", "United States");
    dt.Rows.Add(2, "Mudassar Khan", "India");
    dt.Rows.Add(3, "Suzanne Mathews", "France");
    dt.Rows.Add(4, "Robert Schidner", "Russia");
    string filePath = Server.MapPath("~/Test.xls");
    FileInfo info = new FileInfo(filePath);
    bool isNew = !info.Exists ? true : false;
    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
    using (var package = new ExcelPackage(info))
    {
        ExcelWorksheet workSheet;
        if (isNew)
        {
            workSheet = package.Workbook.Worksheets.Add("Customers");
            ExcelWorksheetView wv = workSheet.View;
            wv.RightToLeft = Thread.CurrentThread.CurrentCulture.TextInfo.IsRightToLeft;
            // Load DataTable.
            workSheet.Cells[1, 1].LoadFromDataTable(dt, isNew, TableStyles.Light8);
        }
        else
        {
            workSheet = package.Workbook.Worksheets["Customers"];
            // Load DataTable.
            workSheet.Cells[2, 1].LoadFromDataTable(dt, isNew);
        }
        workSheet.PrinterSettings.Orientation = eOrientation.Landscape;
        workSheet.Cells.AutoFitColumns();

        int rows = workSheet.Dimension.End.Row;
        int columns = workSheet.Dimension.End.Column;
        for (int row = 0; row < rows; row++)
        {
            for (int column = 1; column <= columns; column++)
            {
                if (workSheet.Cells[row + 2, column].Value != null)
                {
                    if (workSheet.Cells[row + 2, column].Value.ToString().ToLower() == "india")
                    {
                        // Set dynamic Field Color.
                        workSheet.Cells[row + 2, column].Style.Font.Color.SetColor(Color.Red);
                        workSheet.Cells[row + 2, column].Style.Font.Bold = true;
                    }
                }
            }
        }
        package.Save();
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    Dim dt As DataTable = New DataTable()
    dt.Columns.AddRange(New DataColumn(2) {New DataColumn("Id"), New DataColumn("Name"), New DataColumn("Country")})
    dt.Rows.Add(1, "John Hammond", "United States")
    dt.Rows.Add(2, "Mudassar Khan", "India")
    dt.Rows.Add(3, "Suzanne Mathews", "France")
    dt.Rows.Add(4, "Robert Schidner", "Russia")
    Dim filePath As String = Server.MapPath("~/Test.xls")
    Dim info As FileInfo = New FileInfo(filePath)
    Dim isNew As Boolean = If(Not info.Exists, True, False)
    ExcelPackage.LicenseContext = LicenseContext.NonCommercial
    Using package = New ExcelPackage(info)
        Dim workSheet As ExcelWorksheet
        If isNew Then
            workSheet = package.Workbook.Worksheets.Add("Customers")
            Dim wv As ExcelWorksheetView = workSheet.View
            wv.RightToLeft = Thread.CurrentThread.CurrentCulture.TextInfo.IsRightToLeft
            workSheet.Cells(1, 1).LoadFromDataTable(dt, isNew, TableStyles.Light8)
        Else
            workSheet = package.Workbook.Worksheets("Customers")
            workSheet.Cells(2, 1).LoadFromDataTable(dt, isNew)
        End If
        workSheet.PrinterSettings.Orientation = eOrientation.Landscape
        workSheet.Cells.AutoFitColumns()

        Dim rows As Integer = workSheet.Dimension.End.Row
        Dim columns As Integer = workSheet.Dimension.End.Column
        For row As Integer = 0 To rows - 1
            For column As Integer = 1 To columns
                If workSheet.Cells(row + 2, column).Value IsNot Nothing Then
                    If workSheet.Cells(row + 2, column).Value.ToString().ToLower() = "india" Then
                        workSheet.Cells(row + 2, column).Style.Font.Color.SetColor(Color.Red)
                        workSheet.Cells(row + 2, column).Style.Font.Bold = True
                    End If
                End If
            Next
        Next

        package.Save()
    End Using
End Sub

Screenshot