Customize excel sheet's cell background color of open xml in asp.net

Question

I want to set a color of some cells using Asp.Net. Suppose,If my Salary amount is greater than 20000 than that specific cells color will be red,where salary > 20000 .other cell is as well as. I am fetching the full data from database and finally export the data in excel file using closedXML dll. my all data are store in excel file but the requirement is full filled.

Its my code to generete excel(In Vb.Net)

Public Sub GenerateExcel(ByVal DTExcel As DataTable, ByVal sFolder As String, ByVal email As String)
    Dim filepathemail As String = "", cntCol As Integer = 0, cntrow As Integer = 0, k As Integer = 0

    If DTExcel.Rows.Count > 0 Then
        ' string Sallary= ds.Tables[0].Rows[0]["Sallary"].ToString();

        Dim Excel As New XLWorkbook()

   If Sallary > 20000 Then
                //Here need to change the color of that cell (How to find cell)
  End if

        Dim worksheet = Excel.Worksheets.Add("Longcode SMS Log Report")
        worksheet.Cell(1, 1).InsertTable(DTExcel, True)

        worksheet.Tables.First().InsertRowsAbove(3)
        worksheet.Cell("A1").SetValue("Longcode SMS Log Report").Style.Font.FontName = "Calibri"
        worksheet.Cell("A1").Style.Font.Bold = True
        worksheet.Cell("A1").Style.Font.FontSize = 14
        worksheet.Range("A1:L1").Row(1).Merge()


        worksheet.Cell("A2").SetValue("Report Date : " & Format(ReportDate, "dd/MM/yyyy ")).Style.Font.FontName = "Calibri"
        worksheet.Cell("A2").Style.Font.Bold = True
        worksheet.Cell("A2").Style.Font.FontSize = 13
        worksheet.Range("A2:L2").Row(1).Merge()

        worksheet.Range("A3:L3").Row(1).Merge()

        Dim dttime As DateTime = DateTime.Now
        dttime = dttime.AddDays(-1)
        Dim hr As String = dttime.Hour
        Dim day As String = dttime.Day
        Dim mon As String = dttime.Month
        Dim yr As String = dttime.Year
        Dim dirPath As String = Application.StartupPath & "\Reports\" & sFolder
        If Not Directory.Exists(dirPath) Then
            Directory.CreateDirectory(dirPath)
        End If
        filepathemail = IO.Path.Combine(dirPath, "Longcode SMS Log Report_" & day & mon & yr & "_" + ".xlsx")
        Dim filepath As String = filepathemail
        Excel.SaveAs(filepath)

Answer 1

What you describe is called Conditional Formatting in Excel and ClosedXml (see the ClosedXml documentation). The code looks something like this:

worksheet.Range("B2:B100").AddConditionalFormat()
    .WhenGreaterThan(20000).Fill.SetBackgroundColor(XLColor.Red);