5

I'm trying to color an entire row based on a cell selection(value).I want to change the color of the row just for the used range in my worksheet but not for unused cells.

Here's the code I have right now:

class Program
{
    static void Main(string[] args)
    {
        CreateWorkbook();
    }

    static void CreateWorkbook()
    {
        var workbook = new XLWorkbook();
        var ws1 = workbook.AddWorksheet("Main");
        var ws2 = workbook.AddWorksheet("ListOfOptions");

        var listOfStrings = new List<String>();
        listOfStrings.Add(" ");
        listOfStrings.Add("Edit");
        listOfStrings.Add("Delete");

        ws2.Cell(2,2).InsertData(listOfStrings);
        workbook.Worksheet(1).Column(3).SetDataValidation().List(ws2.Range("B2:B4"), true);

        var list = new List<Person>();
        list.Add(new Person() { Name = "John", Age = 30, Action = " " });
        list.Add(new Person() { Name = "Mary", Age = 15, Action = " " });
        list.Add(new Person() { Name = "Luis", Age = 21, Action = " " });
        list.Add(new Person() { Name = "Henry", Age = 45, Action = " " });

        ws1.Cell(1, 1).InsertData(list.AsEnumerable());

        ws1.RangeUsed().AddConditionalFormat().WhenContains("Edit")
            .Fill.SetBackgroundColor(XLColor.Yellow);
        ws1.RangeUsed().AddConditionalFormat().WhenContains("Delete")
            .Fill.SetBackgroundColor(XLColor.Red);

        ws2.Hide(); 
        workbook.SaveAs("DemoWorkbook.xlsx");
    }

    class Person
    {
        public string Name { get; set; }
        public int Age { get; set; }
        public string Action { get; set; }
    }
}

The result is this: See excel screenshot

Andrei
  • 270
  • 1
  • 5
  • 12
  • ClosedXML can only apply conditional formats for a cell itself. – Raidri Mar 23 '18 at 16:39
  • As mentioned by Raidri, the problem is your style being applied to cells containing Edit or Delete, not to their rows. Even if you do `ws1.Rows().ForEach(r => r.AsRange().AddConditionalFormat(). WhenContains("Edit").Fill.SetBackgroundColor( XLColor.Yellow));` is still applying color only to all cells that contain those words in the used range. – derloopkat Mar 23 '18 at 17:18

1 Answers1

1

With VB.NET - 

            Dim nonEmptyDataRows = ws.RowsUsed
            For Each dataRow In nonEmptyDataRows
                Dim cell As String = dataRow.Cell(29).Value
                If cell = "0" Then
                    dataRow.Style.Fill.BackgroundColor = XLColor.Yellow
                ElseIf cell = "1" Then
                    dataRow.Style.Fill.BackgroundColor = XLColor.Green
                End If
            Next
Rituparno Bhattacharya
  • 319
  • 8
  • 23