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

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 2

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;

                    }
                }