如何在.NET Core项目中使用Excel为数据表格的单元格着色?

huangapple go评论74阅读模式
英文:

How can I color the cell of datagrid with excel in .net core project

问题

在我的.NET Core项目中,我有一个数据表格。我想根据内容对某些列进行着色。我创建了数据表格然后解析了数据。

DataTable dt = new();     
dt.Columns.Add("Status");
dt.Columns.Add("Base");
dt.Columns.Add("First Date");
dt.Columns.Add("Second Date");
dt.Columns.Add("Failure Mode");

dataAll.ForEach(row =>
{
    dt.Rows.Add(
        row.status,
        row.partBase,
        row.pcaDate,
        row.icaDate,
        row.failureMode
    );
});

我想要更改状态列的颜色。例如,如果状态等于R,我想将该单元格颜色设置为红色。如何着色数据表格单元格。以下是如何导出到Excel的方式:

using XLWorkbook wb = new();
// 创建数据表格
DataTable dt = MapDataToExcel(response.Result.ToList());

// 添加为工作表
wb.Worksheets.Add(dt, "Rapor");          

// 将其保存为文件,然后将其流式传输给用户
using MemoryStream stream = new MemoryStream();
wb.SaveAs(stream);

return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Rapor.xlsx");

请注意,如何着色数据表格单元格并不在您提供的代码中。要实现此功能,您需要使用Excel库或工具,例如EPPlus,来打开Excel文件并根据条件着色单元格。这部分的代码不包含在您的示例中。

英文:

I have a datatable in my .net core project. I want to color certain column according to the content. I created the datatable and then parsed the data.

DataTable dt = new();     
dt.Columns.Add("Status");
dt.Columns.Add("Base");
dt.Columns.Add("First Date");
dt.Columns.Add("Second Date");
dt.Columns.Add("Failure Mode");

dataAll.ForEach(row =>
{
    dt.Rows.Add(
        row.status,
        row.partBase,
        row.pcaDate,
        row.icaDate,
        row.failureMode
    );
});

I want to change the color of the status column. For example to color this cell red if the status is equal to R. How can I color the Datatable cell.This is how I export with Excel;

        using XLWorkbook wb = new();
        // create data table
        DataTable dt = MapDataToExcel(response.Result.ToList());

        // add as worksheet
        wb.Worksheets.Add(dt, "Rapor");          
       
        // save it as a file then stream it to the user
        using MemoryStream stream = new MemoryStream();
        wb.SaveAs(stream);

        return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Rapor.xlsx");

答案1

得分: 1

我已经假设状态字段是一个布尔值。您可以将此方法包含在您的代码片段中,并根据您自己的定义更改条件。

private void DataGridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{
    bool status = Convert.ToBoolean(e.Row.Cells["Status"].Value);
    if (status)
    {
        e.CellStyle.BackColor = Color.Green;
    }
    else
    {
        e.CellStyle.BackColor = Color.Red;
    }
}
英文:

I have assumed that the status field is a boolen value. You can include this method in your code fragment and change the condition according to your own definition.

private void DataGridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{
    bool status = Convert.ToBoolean(e.Row.Cells["Status"].Value);
    if (status)
    {
       e.CellStyle.BackColor = Color.Green;
    }
    else
    {
       e.CellStyle.BackColor = Color.Red;
    }
}

答案2

得分: 1

for (int i = 2; i < dataCount; i++)
{
    foreach (var item in wb.Worksheets)
    {   
        var objPage2 = item.Cell(i, 1).GetString();
        if (objPage2 == "R") { item.Cell(i, 1).Style.Fill.BackgroundColor = XLColor.Red; }
        if (objPage2 == "G") { item.Cell(i, 1).Style.Fill.BackgroundColor = XLColor.Green; }
        if (objPage2 == "Y") { item.Cell(i, 1).Style.Fill.BackgroundColor = XLColor.Yellow; }
    }
}

根据特定列的值检查并根据其值进行着色。

英文:
for (int i = 2; i &lt; dataCount; i++)
            {
               

                foreach (var item in wb.Worksheets)
                {   
                    var objPage2 = item.Cell(i, 1).GetString();
                    if (objPage2 == &quot;R&quot;) { item.Cell(i, 1).Style.Fill.BackgroundColor = XLColor.Red; }
                    if (objPage2 == &quot;G&quot;) { item.Cell(i, 1).Style.Fill.BackgroundColor = XLColor.Green; }
                    if (objPage2 == &quot;Y&quot;) { item.Cell(i, 1).Style.Fill.BackgroundColor = XLColor.Yellow; }

                }
            }

Check the value of the specific column and color them depending on value

huangapple
  • 本文由 发表于 2023年7月20日 19:21:49
  • 转载请务必保留本文链接:https://go.coder-hub.com/76729340.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定