c# - 如何在 EPPlus C# .XLSX 下载中添加交替行格式

Question

我正在使用 c# EppPlus 将 .aspx 页面中的 .xls 下载升级为 .xlsx 下载。如何添加替代行背景颜色，就像其他行有灰色背景一样？

我正在使用下面的代码

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

我认为还应该提到的是，LoadFromDataTable 有一个重载，您可以在其中像这样传递 TableStyle

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

如果你想从头开始格式化 tbl 的区域，那么你可以这样做

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);
     }
}