none
Cell color changing In xlsx file using C# RRS feed

  • Question

  • I used the code below, but it background fill paint only the first line, the lines below do not fill.
    I have a file xlsx which contains data:

    ID     VALUE
    ID104  1
    ID411  1
    ID711  1
    ID572  0

    I want to color the cells in the ID column, if VALUE equall 1.
    The number of rows in the file more than 1000.
    I use ClosedXML.Excel

    I used the code below, but it background fill paint only the first line, the lines below do not fill.
    // Fill background 1 row               

                    for (int j = 1; j <= dr.FieldCount; j++) 
                    {
                        // j - Column number
                        if (j == 27)
                        {
    						for (int irow = 1; irow <= i-1; irow++)
    						{
    							if (irow == 1) 
    							{
    								ws.Cell(irow + 1, 1).Style.Fill.BackgroundColor = XLColor.Red;
                                }
    						}
                        }
                    }

    I used the code below but the cell background fill does not apply
    // background fill does not work

                    for (int j = 1; j <= dr.FieldCount; j++) 
                    {
                        // j - Column number
                        if (j == 27)
                        {
    						for (int irow = 1; irow <= i-1; irow++)
    						{
    							if (ws.Cell(irow, 1).Value.ToString() == "1") 
    							{
    								ws.Cell(irow + 1, 1).Style.Fill.BackgroundColor = XLColor.Red;
                                }
    						}
                        }
                    }

    // Legend
    SqlDataReader dr = null;
    IXLWorksheet ws = wb.Worksheets.Add(NameSheet);
    i = 2210 // number of rows in xlsx file
    j = 27 // number of columns
    irow // number rows


    How to fix my code so that cells in the ID column are painted in if the value in the VALUE column is equal to 1?

    Tuesday, October 30, 2018 2:46 PM

Answers

  • Hi Aleks Roth,

    Thank you for posting here.

    For your question, if you want to change the corresponding ID on the left when the value Is equal to 1 in the second column, you could try the code below.

    I suggest you to change the code below.

    change

     if (ws.Cell(irow, 1).Value.ToString() == "1")

    to 

    if (ws.Cell(irow, 2).Value.ToString () == "1")

    Here is the code for your reference.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.IO;
    using System.Data.SqlClient;
    using ClosedXML.Excel;
    using System.Drawing;
    
    namespace COLOR
    {
        class Program
        {
            static void Main(string[] args)
            {
                    XLWorkbook wb = new XLWorkbook(@"D:\test.xlsx");
                    IXLWorksheet ws = wb.Worksheets.First();
                   int i = 5;
                for (int j = 1; j <= 5; j++)
                {
                    for (int irow = 1; irow <= i - 1; irow++)
                    {
                        if (ws.Cell(irow, 2).Value.ToString() == "1")//You  need  to modify.
                        {
                            ws.Cell(irow, irow+1).Style.Fill.BackgroundColor = XLColor.Red;
                            Console.WriteLine(ws.Cell(irow, 2).Value.ToString());//you can  check  the value  is equal to 1
                        }
                    }
                   
                }
                Console.ReadKey();
            }
        }
    }
    

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 31, 2018 12:16 PM
    Moderator

All replies

  • Hi Aleks Roth,

    Thank you for posting here.

    For your question, if you want to change the corresponding ID on the left when the value Is equal to 1 in the second column, you could try the code below.

    I suggest you to change the code below.

    change

     if (ws.Cell(irow, 1).Value.ToString() == "1")

    to 

    if (ws.Cell(irow, 2).Value.ToString () == "1")

    Here is the code for your reference.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.IO;
    using System.Data.SqlClient;
    using ClosedXML.Excel;
    using System.Drawing;
    
    namespace COLOR
    {
        class Program
        {
            static void Main(string[] args)
            {
                    XLWorkbook wb = new XLWorkbook(@"D:\test.xlsx");
                    IXLWorksheet ws = wb.Worksheets.First();
                   int i = 5;
                for (int j = 1; j <= 5; j++)
                {
                    for (int irow = 1; irow <= i - 1; irow++)
                    {
                        if (ws.Cell(irow, 2).Value.ToString() == "1")//You  need  to modify.
                        {
                            ws.Cell(irow, irow+1).Style.Fill.BackgroundColor = XLColor.Red;
                            Console.WriteLine(ws.Cell(irow, 2).Value.ToString());//you can  check  the value  is equal to 1
                        }
                    }
                   
                }
                Console.ReadKey();
            }
        }
    }
    

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 31, 2018 12:16 PM
    Moderator
  • Thank You!

    If you specify more than 1 then the error, how to write it correctly?

    if (ws.Cell(irow, 2).Value.ToString() >= "1")


    • Edited by Aleks Roth Wednesday, October 31, 2018 1:52 PM 1
    Wednesday, October 31, 2018 1:51 PM
  • Hi Aleks Roth,

    Thank you for feedback.

    If you want to set the number in the second column to be greater than 1.

    Here is the code for your reference.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.IO;
    using System.Data.SqlClient;
    using ClosedXML.Excel;
    using System.Drawing;
    namespace COLOR
    {
        class Program
        {
            static void Main(string[] args)
            {
                    XLWorkbook wb = new XLWorkbook(@"D:\test.xlsx");
                    IXLWorksheet ws = wb.Worksheets.First();
                    int i = 5;
                    for (int j = 1; j <= 5; j++)
                    {
                    for (int irow = 2; irow <= i - 1; irow++)    // Since the first line must be value, set irow to 2.
                    {
                    if (Convert.ToInt32(ws.Cell(irow, 2).Value)>=1)// Convert ws.cell(irow,2) to a numeric type, compare to 1.
                    {
                            ws.Cell(irow + 1, 1).Style.Fill.BackgroundColor = XLColor.Red;
                            //Console.WriteLine(ws.Cell(irow, 2).Value.ToString());//You can check if the value is greater than 1
                    }
                    }
            }
                Console.ReadKey();
            }
        }
    }
    

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 5, 2018 12:41 PM
    Moderator