Closed XML change entire row background color based on cell value

Question

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

ClosedXML can only apply conditional formats for a cell itself. — Raidri
– Raidri, Commented Mar 23, 2018 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. — Daniel Manta
– Daniel Manta, Commented Mar 23, 2018 at 17:18

Rituparno Bhattacharya · Accepted Answer · 2019-03-08 09:52:20Z

4

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

answered Mar 8, 2019 at 9:52

Rituparno Bhattacharya

3739 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Armali Over a year ago

The question wasn't about VB.NET.

Collectives™ on Stack Overflow

Closed XML change entire row background color based on cell value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest