Conditional Formatting in Excel with LINQ and EPPlus

Introduction

There is an alternative approach to conditional formatting:

1. When formulas are too complex or impossible to apply using conditional formatting
2. When you are creating gigantic Excel spreadsheets with over 20,000 – 30,000 records and need to conditionally format many cells without sacrificing Excel performance

Background

The logic of this approach is very simple:

1. Store in one of the list properties Excel row numbers (in the sample below, it’s iRow)
2. Name all of the columns of exported data respective to class properties (to make easy reference to exported columns)
3. Retrieve all the Excel cell addresses that you’d like to format using LINQ into a single string

Using the Code

Here is simplified version of the project I had been working on recently, where I had to format cost and price columns to show respective currency within the same column based on Currency property (if Currency is USD, then I had to apply $ sign, if EUR then €, etc…). Also, I had to highlight gm column with negative values with red background and yellow font, from 0 to 15% with yellow background and red font.

Here is my class EndContract and ViewModel vmEndContracts:

public class EndContract
{
    [Key]
    public string iRow { get; set; }
    public string Customer { get; set; }
    public string Item { get; set; }
    public string CustomerItem { get; set; }
    public string ItemType { get; set; }
    public DateTime ContractDate { get; set; }
    public Nullable<decimal> cost { get; set; }
    public Nullable<decimal> price { get; set; }
    public Nullable<decimal> gm { get; set; }
    public string Currency { get; set; }
    public int seqCnt { get; set; }
    public Nullable<decimal> TotalShipped { get; set; }
}

public class vmEndContracts
{
    public List<EndContract> EndContract_List { get; set; }
}

I used list EndContract_List to create webpage for ending customer contract and also wanted to create Excel download of the same data similarly formatted (see picture below):

In the view Excel button calls IActionResult ExportToExcel() in the Controller.

<form action="@Url.Action("ExportToExcel","EndingContracts")" 
enctype="multipart/form-data" method="post" target="_blank">
    <button type="submit" class="btn">
        <img src="@Url.Content("~/images/Excel.ico")" width="48" height="48" />
    </button>
</form>

EndingCountractsController Controller

using System.Linq;
using Microsoft.Extensions.Caching.Memory;
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.Style;
Controller constructor:
    private IMemoryCache _cache;
    private IHostingEnvironment _env;
    private vmEndContracts vm = new vmEndContracts();
 
    public EndingContractsController(IMemoryCache memoryCache, IHostingEnvironment env)
    {           
        _cache = memoryCache; _env = env;
    }

I’m saving list with Ending Contracts in IMemoryCache to make screen data available for exporting to Excel.

    var cacheOptions = new MemoryCacheEntryOptions().SetSlidingExpiration(TimeSpan.FromMinutes(30));
    _cache.Set("ListEndingContracts", vm.EndContract_List, cacheOptions);
Exporting to Excel:
public IActionResult ExportToExcel()
{
    string webRoot = _env.WebRootPath; // wwwroot directory
    string path = Path.Combine(webRoot, @"xlDownloads/ContractsEnding.xlsx");
    FileInfo newFile = new FileInfo(path);
    if (newFile.Exists) { newFile.Delete(); newFile = new FileInfo(path); }
 
    if (_cache.Get<List<EndContract>>("ListEndingContracts") != null) 
    { vm.EndContract_List = _cache.Get<List<EndContract>>("ListEndingContracts"); }
    if (vm.EndContract_List != null && vm.EndContract_List.Count > 0) {
 
        // Enumerate records - iRow would be loaded with Excel Row numbers
        int iRow = 3; foreach (EndContract ec in vm.EndContract_List) { ec.iRow = iRow++.ToString(); }
 
        using (ExcelPackage package = new ExcelPackage(newFile)) {
            ExcelWorksheet ws = package.Workbook.Worksheets.Add("Contracts_Ending");
            ExcelWorkbook wb = package.Workbook; ExcelRange cRng;
 
            int Recs = vm.EndContract_List.Count;
            PropertyInfo[] properties = null;                
            Type type = typeof(EndContract); properties = type.GetProperties();
 
            //Add Heading
            ws.Cells[1, 3].Value = "Ending Contracts";
            cRng = ws.Cells["C1"]; cRng.Style.Font.Name = "Verdana"; cRng.Style.Font.Size = 16;
 
            int cntCols = properties.Length;
            ws.Cells[2, 1, 2, cntCols].Style.Font.Bold = true;
 
            // Name all the columns with respective RangeNames by 
            // adding "rng" to each class property name
            int iCol = 0;
            foreach (PropertyInfo property in properties) {
                iCol++; ws.Cells[2, iCol].Value = property.Name;
                string strRngName = "rng" + property.Name;
                wb.Names.Add(strRngName, ws.Cells[3, iCol, 2 + Recs, iCol]);
            }
 
            FormatGM(wb, ws);                               // Conditionally Format GM% column
            FormatCurrColumns(wb, ws, "rngcost", "Cost");   // Conditionally Format Currencies 
                                                            // for Cost Column
            FormatCurrColumns(wb, ws, "rngprice", "Price"); // Conditionally Format Price 
                                                            // for Cost Column
 
            ws.Cells[3, 1].LoadFromCollection(vm.EndContract_List);
            ws.Cells[2, 1, 2 + Recs, cntCols].AutoFitColumns(0, 40);
 
            ws.View.FreezePanes(3, 1);
 
            ws.DeleteColumn(1); // Remove Column with Excel Row Numbers
            package.Save();
            // Download the Exdcel file
            byte[] fileBytes = System.IO.File.ReadAllBytes(path); 
            string fileName = "ContractsEnding.xlsx";
            HttpContext.Response.ContentType = 
                   "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            return File(fileBytes, HttpContext.Response.ContentType, fileName);
        }
    }
    return PartialView("~/Views/Shared/_Close.cshtml"); // Open an empty view with JavaScript 
                                                        // which closes itself 
}
 
private void FormatCurrColumns(ExcelWorkbook wb, ExcelWorksheet ws, string rngName, string sHdr)
{
    string rngUS = ""; string rngEU = ""; string rngUK = ""; string rngCZ = "";
    string sFormatUS = @"_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)";
    string sFormatEU = @"_([$€-2] * #,##0.00_);_([$€-2] * (#,##0.00);_([$€-2] * ""-""??_);_(@_)";
    string sFormatUK = @"_-[$£-809]* #,##0.00_-;-[$£-809]* #,##0.00_-;_-[$£-809]* ""-""??_-;_-@_-";
    string sFormatCZ = @"_-* #,##0.00 [$Kc-405]_-;-* #,##0.00 [$Kc-405]_-;_-* ""-""?? [$Kc-405]_-;_-@_-";
 
    // Format Currency Columns
    string sCell = wb.Names[rngName].Address.Split(":", StringSplitOptions.RemoveEmptyEntries)[0];
    string sCol = sCell.Substring(0, sCell.Length - 1); 
    int bRow = Convert.ToInt16(sCell.Substring(sCell.Length - 1, 1));
    string hdrCell = sCol + (bRow - 1).ToString(); ws.Cells[hdrCell].Value = sHdr;
    ws.Cells[hdrCell].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
 
    // Get all the Cell Addresses with $US currency and save it in rngUS
    rngUS = string.Join(",", vm.EndContract_List.Where
    (p => p.Currency == "USD").Select(a => sCol + a.iRow));
    if (rngUS.Length > 0) { ws.Cells[rngUS].Style.Numberformat.Format = sFormatUS; }
 
    // Get all the Cell Addresses with Euro currency and save it in rngEU
    rngEU = string.Join(",", vm.EndContract_List.Where
    (p => p.Currency == "EUR").Select(a => sCol + a.iRow));
    if (rngEU.Length > 0) { ws.Cells[rngEU].Style.Numberformat.Format = sFormatEU; }
 
    // Get all the Cell Addresses with British Pound currency and save it in rngGB
    rngUK = string.Join(",", vm.EndContract_List.Where
    (p => p.Currency == "GBP").Select(a => sCol + a.iRow));
    if (rngUK.Length > 0) { ws.Cells[rngUK].Style.Numberformat.Format = sFormatUK; }
 
    // Get all the Cell Addresses with Czech currency and save it in rngCZ
    rngCZ = string.Join(",", vm.EndContract_List.Where
    (p => p.Currency == "CZK").Select(a => sCol + a.iRow));
    if (rngCZ.Length > 0) { ws.Cells[rngCZ].Style.Numberformat.Format = sFormatCZ; }
}
 
private void FormatGM(ExcelWorkbook wb, ExcelWorksheet ws)
{
    string sCell = wb.Names["rnggm"].Address.Split(":", StringSplitOptions.RemoveEmptyEntries)[0];
    string sCol = sCell.Substring(0, sCell.Length - 1); 
    int bRow = Convert.ToInt16(sCell.Substring(sCell.Length - 1, 1));
    string sHdr = sCol + (bRow-1).ToString();   ws.Cells[sHdr].Value = "GM%";
    ws.Cells[sHdr].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
 
    wb.Names["rnggm"].Style.Numberformat.Format = "#,##0.00%";
    wb.Names["rnggm"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
    wb.Names["rnggm"].Style.Indent = 1;
 
    // Get all the Cell Addresses with Negative GM% and save it in string gmRed
    string gmRed = string.Join
         (",", vm.EndContract_List.Where(p => p.gm < 0).Select(a => sCol + a.iRow));
    ws.Cells[gmRed].Style.Fill.PatternType = ExcelFillStyle.Solid;
    ws.Cells[gmRed].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Red);
    ws.Cells[gmRed].Style.Font.Color.SetColor(System.Drawing.Color.Yellow);
    ws.Cells[gmRed].Style.Font.Bold = true;
 
    // Get all the Cell Addresses with Negative GM% between 0 to 15% and save it in string gm15
    string gm15 = string.Join(",", vm.EndContract_List.Where
    (p => p.gm >= 0 && p.gm < (decimal)0.15).Select(a => sCol + a.iRow));
    ws.Cells[gm15].Style.Fill.PatternType = ExcelFillStyle.Solid;
    ws.Cells[gm15].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Yellow);
    ws.Cells[gm15].Style.Font.Color.SetColor(System.Drawing.Color.Red);
    ws.Cells[gm15].Style.Font.Bold = true;
}