在C#.net中使用EPPLus根据文本设置单元格的条件背景色 [英] Set conditional background color of cell based on text using EPPLus in C#.net

问题描述

我正在使用Epplus导出到Excel.

我想根据第1列的值设置第1列和第2列的背景颜色.如果列2单元格中的任何单元格包含1，则col1和col2的背景颜色为绿色.如果包含2，则背景颜色必须为浅黄色.就像下面的图片一样.

现在，我只能设置第二列的背景颜色.如果我设置了范围，那么它将根据"Last"条件设置背景色，并将整个列的颜色设置为黄色.请帮帮我.

解决方案

我找到了自己的解决方案.以下是excel输出.

 int Tocolumn = ws.Dimension.End.Column;

  foreach (ExcelRangeBase cell in ws.Cells[2, 1, ToRow, 2])
    {
        if (string.IsNullOrEmpty(cell.Text)) continue;
        var text = cell.Text;

        if (text.Equals("0"))
        {
            Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#7fcbfe");
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.BackgroundColor.SetColor(colFromHex);
        }
        else if (text.Equals("1"))
        {
            Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#90ee90");
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.BackgroundColor.SetColor(colFromHex);
        }
        else if (text.Equals("2"))
        {
            Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#ffee75");
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.BackgroundColor.SetColor(colFromHex);
        }
        else if (text.Equals("3"))
        {
            Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#fdb957");
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.BackgroundColor.SetColor(colFromHex);
        }
        else if (text.Equals("4"))
        {
            Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#FF9985");
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.BackgroundColor.SetColor(colFromHex);
        }
        else if (text.Equals("5"))
        {
            Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#33CCCC");
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.BackgroundColor.SetColor(colFromHex);
        }
        else if (text.Equals("6"))
        {
            Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#66CCFF");
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.BackgroundColor.SetColor(colFromHex);
        }
        else if (text.Equals("7"))
        {
            Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#FFFF99");
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.BackgroundColor.SetColor(colFromHex);
        }
    }

I am using Epplus For Export to Excel.

i want to set the background color of column 1 and 2 based on column 1 value. if any cell in column 2 cells contain 1 then the background color of col1 and col2 is of Green. if it contains 2 then the background Color must be light yellow. Like below Image.

Now I am able to set only 2nd column background color. if i set the range then it set the background color based on the Last condition and color the entire column as yellow. Please help me out.

解决方案

I have found my own solution. below is excel output.

 int Tocolumn = ws.Dimension.End.Column;

  foreach (ExcelRangeBase cell in ws.Cells[2, 1, ToRow, 2])
    {
        if (string.IsNullOrEmpty(cell.Text)) continue;
        var text = cell.Text;

        if (text.Equals("0"))
        {
            Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#7fcbfe");
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.BackgroundColor.SetColor(colFromHex);
        }
        else if (text.Equals("1"))
        {
            Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#90ee90");
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.BackgroundColor.SetColor(colFromHex);
        }
        else if (text.Equals("2"))
        {
            Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#ffee75");
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.BackgroundColor.SetColor(colFromHex);
        }
        else if (text.Equals("3"))
        {
            Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#fdb957");
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.BackgroundColor.SetColor(colFromHex);
        }
        else if (text.Equals("4"))
        {
            Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#FF9985");
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.BackgroundColor.SetColor(colFromHex);
        }
        else if (text.Equals("5"))
        {
            Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#33CCCC");
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.BackgroundColor.SetColor(colFromHex);
        }
        else if (text.Equals("6"))
        {
            Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#66CCFF");
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.BackgroundColor.SetColor(colFromHex);
        }
        else if (text.Equals("7"))
        {
            Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#FFFF99");
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.BackgroundColor.SetColor(colFromHex);
        }
    }

这篇关于在C#.net中使用EPPLus根据文本设置单元格的条件背景色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！