如何在EPP Plus 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);

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

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

下面的代码为我完成了这项工作

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

Answer 3

这是我使用的给定答案的变体(作为扩展)。我的要求是列的子集是有颜色的，而不是整行，而且不止两种颜色。

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

白色/灰色交替(浅灰色是我推荐的灰色)看起来像这样:

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