首页 / 问答 / [EPPlus]C#/.NET应用程序编程开发中使用EPPlus时如何根据单格式的值动态设置单元格式的样式(背景、字体颜色/大小等)?

[EPPlus]C#/.NET应用程序编程开发中使用EPPlus时如何根据单格式的值动态设置单元格式的样式(背景、字体颜色/大小等)?

.NET C# EPPlus .NET Core 559 次浏览
0

在C#/.NET应用程序编程开发中,当使用EPPlus作为Excel导出组件时,如何根据单格式的值动态设置单元格式的样式(背景、字体颜色/大小等)呢?

比如当单元格式的值(日期)早于2周前时格式化为黄色,当单元格式的日期早于90天以前时格式化为红色,使用EPPlus应该如何实现这样的格式化样式输出呢?

回复 [×]
提交评论
请输入评论内容

2 个回答

  • 0

    可以使用公式函数TODAY()与当前时间进行计算,然后循环设置每个单元格式的背景颜色,如下:

    public void ConditionalFormattingDate()
    {
        var file = new FileInfo(@"c:\temp\Conditional_Formatting_Date.xlsx");
        if (file.Exists)
            file.Delete();
    
        var dataTable = new DataTable("tblData");
        dataTable.Columns.AddRange(new[] {
            new DataColumn("Col1", typeof(DateTime)),
            new DataColumn("Col3", typeof(string))
        });
    
        var rnd = new Random();
        for (var i = 0; i < 100; i++)
        {
            var row = dataTable.NewRow();
            row[0] = DateTime.Now.AddDays(-rnd.Next(1, 100));
            row[1] = $"=TODAY() - A{i +1}";
            dataTable.Rows.Add(row);
        }
    
        using (var package = new ExcelPackage(file))
        {
            var ws = package.Workbook.Worksheets.Add("table");
            var range = ws.Cells[1, 1].LoadFromDataTable(dataTable, false);
            ws.Column(1).Style.Numberformat.Format = "mm-dd-yy";
            ws.Column(1).AutoFit();
    
            //Add the calc check
            var count = 0;
            foreach (DataRow row in dataTable.Rows)
                ws.Cells[++count, 2].Formula = row[1].ToString();
    
            var rangeA = range.Offset(0, 0, count, 1);
    
            var condition90 = ws.ConditionalFormatting.AddExpression(rangeA);
            condition90.Style.Font.Color.Color = Color.White;
            condition90.Style.Fill.PatternType = ExcelFillStyle.Solid;
            condition90.Style.Fill.BackgroundColor.Color = Color.Red;
            condition90.Formula = "TODAY() - A1> 90";
            condition90.StopIfTrue = true;
    
            var condition14 = ws.ConditionalFormatting.AddExpression(rangeA);
            condition14.Style.Font.Color.Color = Color.Black;
            condition14.Style.Fill.PatternType = ExcelFillStyle.Solid;
            condition14.Style.Fill.BackgroundColor.Color = Color.Yellow;
            condition14.Formula = "TODAY() - A1> 14";
    
            package.Save();
        }
    }
    

    输出结果类似如下图:

    Rector的个人主页

    Rector

    01-13 回答

    • 0

      循环遍历并找到需要格式化样式的单元格式,然后调用设置单元格式的背景颜色属性Style.Fill.BackgroundColor.SetColor,代码如下:

      public void CellColorBackgroundTest()
      {
          var dtdata = new DataTable("tblData");
          dtdata.Columns.Add(new DataColumn("Col1", typeof(string)));
          dtdata.Columns.Add(new DataColumn("Col2", typeof(int)));
          dtdata.Columns.Add(new DataColumn("Col3", typeof(int)));
      
          for (var i = 0; i < 20; i++)
          {
              var row = dtdata.NewRow();
              row["Col1"] = "Available";
              row["Col2"] = i * 10;
              row["Col3"] = i * 100;
              dtdata.Rows.Add(row);
          }
          dtdata.Rows[10]["Col1"] = "Annual leave";
      
          var existingFile = new FileInfo(@"c:\temp\temp.xlsx");
          if (existingFile.Exists)
              existingFile.Delete();
      
          using (var pck = new ExcelPackage(existingFile))
          {
              var ws = pck.Workbook.Worksheets.Add("Availability list");
      
              ws.Cells["A1"].LoadFromDataTable(dtdata, true);
      
              ws.Cells["A1:G1"].Style.Font.Bold = true;
              ws.Cells["A1:G1"].Style.Font.UnderLine = true;
      
              for (var i = 0; i < dtdata.Rows.Count; i++)
              {
                  if (dtdata.Rows[i]["Col1"].ToString() == "Annual leave")
                  {
                      ws.Cells[i + 1, 1].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                      ws.Cells[i + 1, 1].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightYellow);
                  }
              }
      
              pck.Save();
          }
      
      Rector的个人主页

      Rector

      01-13 回答

      我来回答