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

alibasha
 
on Mar 29, 2021 11:47 PM
Sample_256852.zip
5955 Views

Hi,

I have class (which contains collection of records), I am passing it to a method to generate report in Excel, however based on certain condition, like one of the field of current class is true, then I need to change one Excel field color. My code is shown below.

"CurrentQuotation" is table row in excel "tbv.CurrentQuotation", which is contains fields like fld.MatCost, fld.ManfCost and so on, fld.MatCost need to be changed to red color if fld.MaterialCost_ByUser is true

var costStructureReport = new
{
    CurrentQuotation = ptCostStructure.GetCostStructureReport()
};
var reportEngine = new ReportEngine();
string fileName = reportEngine.ProcessReport(ReportNames.ProjectDownload_Template, reportname + ".xlsx", costStructureReport);
var ep = new ExcelPackage(new FileInfo(fileName));
var sheet1 = ep.Workbook.Worksheets["SPR_ProjectDownload"];
var row = sheet1.Dimension.End.Row;

for(int i=0;i< costStructureReport.CurrentQuotation.Count;i++)
{
    if (costStructureReport.CurrentQuotation[i].MaterialCost_ByUser)
    {
        sheet1.Cells[i+2, 2].Style.Font.Color.SetColor(System.Drawing.Color.Red);
        sheet1.Cells[i + 2, 12].Style.Font.Color.SetColor(System.Drawing.Color.Red);
        sheet1.Cells[i + 2, 12].Style.Font.Bold = true;
    }
}

Thanks in advance. 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Apr 02, 2021 12:30 AM

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