Alternate rows coloring in Excel using Epplus

Question

I have multiple rows which I colored with White and dark grey. an example for alternate rows:

The code I used:

            //end.Row is the last row of the excel file
            for (var row = 1; row <= end.Row; row++)
            {
                int pos = row % 2;

                ExcelRow rowRange = BaseSheet.Row(row);
                ExcelFill RowFill = rowRange.Style.Fill;
                RowFill.PatternType = ExcelFillStyle.Solid;

                    if (pos == 0)
                    {
                        RowFill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(233, 233, 233));
                    }
                    else
                    {
                        RowFill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(255, 255, 255));
                    }
            }

and it works as intended. But in my case here I have some identical names not counting the numbers at the end like (nameB_1 , nameB_2). I was able to find and group them with:

            List<string> BaseLines = new List<string>();

            for (int row = 1; row <= end.Row; row++)
            {
                //get the filename column
                string fileName = BaseSheet.Cells[row, 1].Value.ToString();

                BaseLines.Add(fileName.Substring(0, fileName.Length - 2));
            }
                        
             var duplicateIndexes = BaseLines
            .Select((t, i) => new { Index = i, Text = t })
            .GroupBy(g => g.Text)
            .Where(g => g.Count() > 1)
            .SelectMany(g => g, (g, x) => x.Index);

It returns them as follows: {Index = 3, Text = "nameB"} {Index = 4, Text = "nameB"}

I want to do alternate rows coloring but making an exception in the case there is a two duplicate rows(the identical row will always be after the original one) so that the second row will be the same color like in this image:

I tried this:

 for (var row = 1; row < end.Row; row++)
            {
                int pos = row % 2;
                ExcelRow rowRange = BaseSheet.Row(row+1);
                ExcelFill RowFill = rowRange.Style.Fill;
                RowFill.PatternType = ExcelFillStyle.Solid;
                

                //get the filename column
                string fileName = BaseSheet.Cells[row, 1].Value.ToString();

                //Get the filename without the last two characters to find continued lines
                string NewfileName = fileName.Substring(0, fileName.Length - 2);
                
                //To find the first instance to take the color from
                var FirstDuplicate = duplicateIndexes .Where((x, i) => i % 2 == 0);

                bool isduplicate= false;
                foreach (var item in FirstDuplicate)
                {

                    if (item == row)
                    {
                        isduplicate= true;

                    }
                }

                if (isduplicate== false)
                {
                    if (pos == 0)
                    {
                        RowFill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(233, 233, 233));
                    }
                    else
                    {
                        RowFill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(255, 255, 255));
                    }
                }
                else
                {
                    //Get the color of the first instance
                    var Hexcolor = BaseSheet.Cells[row, 1].Style.Fill.BackgroundColor.Rgb;
                    Color RGBColor = System.Drawing.ColorTranslator.FromHtml("#" + Hexcolor);
                    
                    //Set the color to the current duplicate
                    RowFill.BackgroundColor.SetColor(RGBColor);
                }


            }

The problem with this approach that the next line that is not duplicated still has it's old color

So in summary: I want to do alternate rows coloring with the exception to duplicate lines to have the same color but still make the rest of the lines maintain the order of coloring.

Answer 1

I found an answer from a similar question in VBA here :https://stackoverflow.com/a/42809651/8494981

I have modified it for my current situation by replacing the whole for loop code with this method:

        public void ColorAlternateRows(ExcelWorksheet sheet)
        {
        int row;
        int lastrow;

        Color color1 = System.Drawing.Color.FromArgb(233, 233, 233);
        Color color2 = System.Drawing.Color.FromArgb(255, 255, 255);

        // "Current" colour for highlighting
        Color currentColor;
        currentColor = color1;


        lastrow = sheet.Dimension.End.Row;

        for (row = 1; row < lastrow; row++)
        {
            //Define the pattern type.
            //The range here is all rows within the excel sheet from A to G column
            sheet.Cells["A" + row + ":G" + row].Style.Fill.PatternType = ExcelFillStyle.Solid;

            //Set the color of the row
            sheet.Cells["A" + row + ":G" + row].Style.Fill.BackgroundColor.SetColor(currentColor);

            //get the filename in column A for the first line and second line
            string firstLine = sheet.Cells[row, 1].Value.ToString();
            string secondLine = sheet.Cells[row+1, 1].Value.ToString();

            //get the original filename without the additional suffix numbering
            string firstLineWithoutSuffix = firstLine.Substring(0, firstLine.Length - 2);
            string SecondLineWithoutSuffix = secondLine.Substring(0, secondLine.Length - 2);

            // If column A value in next row is different, change colour
            if (SecondLineWithoutSuffix != firstLineWithoutSuffix)
            {
                    // Set to whichever colour it is not
                    if (currentColor == color1)
                        currentColor = color2;
                    else
                        currentColor = color1;
            }
        }
    }