how to add alternative row formatting in EPP Plus C# .XLSX download

Question

I am upgrading .xls download in .aspx page to .xlsx download using c# EppPlus. How can I add Alternative row background color like each other row has gray background ?

I am using the below code

public void DumpExcel(DataTable tbl)
{
    using (ExcelPackage pck = new ExcelPackage())
    {
        //Create the worksheet
        ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Sheet1");


        ws.Cells["A1"].LoadFromDataTable(tbl, true);



        using (ExcelRange rng = ws.Cells["A1:AA1"])
        {
            rng.Style.Font.Bold = false;
            rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid
            rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(0, 51, 153));  //Set color to dark blue
            rng.Style.Font.Color.SetColor(Color.White);
            rng.Style.Font.Size = 10;
        }



        // Add Word wrap
        for (int i = 1; i <= tbl.Columns.Count; i++)
        {
            ws.Column(i).AutoFit();
            ws.Column(i).Width = 20;
            ws.Column(i).Style.WrapText = true;
            ws.Column(i).Style.VerticalAlignment = ExcelVerticalAlignment.Top;
            ws.Column(i).Style.Font.Size = 9;
        }


        //Write it back to the client
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AddHeader("content-disposition", "attachment;  filename=UserEntitleDLX.xlsx");
        Response.BinaryWrite(pck.GetAsByteArray());
    }
}

Answer 1

Here's a variation of the given answers I use (as extension). My requirements required a subset of columns be colored, not the whole row, and with more than just two colors.

public static void ApplyBackgroundColorsPerRow(
    this ExcelWorksheet worksheet, 
    int startRow, int startColumn, int endRow, int endColumn, 
    List<System.Drawing.Color> colors)
{
    if (startRow <= endRow)
    {
        int numberOfColors = colors.Count;
        for (int row = startRow; row <= endRow; row++)
        {
            using (ExcelRange range = worksheet.Cells[row, startColumn, row, endColumn])
            {
                range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                range.Style.Fill.BackgroundColor.SetColor(colors[(row - startRow) % numberOfColors]);
            }
        }
    }
}

Alternating white / gray (light gray is my recommendation over grey) would look like this:

worksheet.ApplyBackgroundColorsPerRow(
    startRow: 1,
    startColumn: 1,
    endRow: 20,
    endColumn: 5,
    colors: new List<System.Drawing.Color>()
    {
        System.Drawing.Color.White,
        System.Drawing.Color.LightGray
    }
);

Answer 2

I think it should also be mentioned that there is an overload for LoadFromDataTable where you can pass a TableStyle like so

ws.Cells["A1"].LoadFromDataTable(tbl, true, TableStyles.Dark1);

If you want the tbl's area to be formatted from scratch then you could do something like this

for (var row = 1; row <= tbl.Rows.Count; row++)
{
    for (var column = 1; column <= tbl.Columns; column++)
    {
        ws.Cells[row, column].Style.Font.Bold = false;
        ws.Cells[row, column].Style.Fill.PatternType = ExcelFillStyle.Solid;
        ws.Cells[row, column].Style.Font.Size = 10;

        ws.Cells[row, column].Style.Fill.BackgroundColor.SetColor(column%2 == 0
           ? Color.Blue
           : Color.Gray);
     }
}

Answer 3

Below piece of code did the job for me

  for (int row = ws.Dimension.Start.Row; row <= ws.Dimension.End.Row; row++)
                {
                    int pos = row % 2;
                    ExcelRow rowRange = ws.Row(row);
                    ExcelFill RowFill = rowRange.Style.Fill;
                    RowFill.PatternType = ExcelFillStyle.Solid;
                    switch (pos)
                    {
                        case 0:
                            RowFill.BackgroundColor.SetColor(System.Drawing.Color.White);

                            break;
                        case 1:
                            RowFill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
                            break;

                    }
                }