1

I am trying to write Excel file from datatable with VB.NET. I am able to achieve few excel operations also. Now I want to colour a row based on cell value of a particular column (Col-4 for example). Here is my Excel file -

Here is my Excel file

I want to achieve like this -

Looking some guidance to achieve this.

Rituparno Bhattacharya
  • 319
  • 8
  • 23

1 Answers1

3

I suggest you add conditional formatting. For example:

var workbook = new XLWorkbook();
var ws = workbook.AddWorksheet("Sheet1");

ws.FirstCell().SetValue(1)
  .CellBelow().SetValue(1)
  .CellBelow().SetValue(2)
  .CellBelow().SetValue(3)
  .CellBelow().SetValue(4);

ws.RangeUsed().AddConditionalFormat().WhenBetween(2, 3)
  .Fill.SetBackgroundColor(XLColor.Red);

Reference: https://github.com/ClosedXML/ClosedXML/wiki/Conditional-Formatting

Francois Botha
  • 4,520
  • 1
  • 34
  • 46
  • Thanks. I satisfied the requirement by adding following code - Dim nonEmptyDataRows2 = ws1.RowsUsed For Each dataRow1 In nonEmptyDataRows2 'for row number check Dim cell1 As String = dataRow1.Cell(29).Value If cell1 = "0" Then dataRow1.Style.Fill.BackgroundColor = XLColor.Yellow ElseIf cell1 = "1" Then dataRow1.Style.Fill.BackgroundColor = XLColor.Green End If Next – Rituparno Bhattacharya Mar 12 '19 at 14:32
  • @RituparnoBhattacharya is that C#? – indago Jun 10 '21 at 06:14