如何在C#中打开Excel并通过条件格式按结果设置单元格颜色

Hey there! To add conditional cell coloring for your PASS/FALSE test results in Excel, you'll need to use a library that supports Excel styling—EPPlus is a popular, easy-to-use choice for .NET projects. Here's a step-by-step guide to modify your existing code:

Step 1: Install EPPlus NuGet Package #

First, make sure you have EPPlus installed in your project. You can do this via the NuGet Package Manager or run this command in the Package Manager Console:

Install-Package EPPlus

Step 2: Modify Your Export Code #

Assuming your test results are stored in a collection (like a List<TestResult> where each item has a Result property), here's how to update your code to apply cell styling:

First, define a simple test result class if you don't already have one:

public class TestResult
{
    public string TestName { get; set; }
    public string Result { get; set; } // "PASS" or "FALSE"
}

Then, integrate styling into your export logic:

using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.IO;

namespace PortfolioVariation
{
    class Program
    {
        static void Main(string[] args)
        {
            // Replace this with your actual test results data
            var testResults = new List<TestResult>
            {
                new TestResult { TestName = "Login Function", Result = "PASS" },
                new TestResult { TestName = "Payment Processing", Result = "FALSE" },
                new TestResult { TestName = "User Profile Update", Result = "PASS" }
            };

            // Create a new Excel package
            using (var package = new ExcelPackage())
            {
                var worksheet = package.Workbook.Worksheets.Add("Test Results");

                // Define reusable styles for PASS and FALSE
                var passStyle = worksheet.Cells.Style.Clone();
                passStyle.Fill.PatternType = ExcelFillStyle.Solid;
                passStyle.Fill.BackgroundColor.SetColor(System.Drawing.Color.Green);
                passStyle.Font.Color.SetColor(System.Drawing.Color.White); // Optional: White text for better contrast

                var falseStyle = worksheet.Cells.Style.Clone();
                falseStyle.Fill.PatternType = ExcelFillStyle.Solid;
                falseStyle.Fill.BackgroundColor.SetColor(System.Drawing.Color.Red);
                falseStyle.Font.Color.SetColor(System.Drawing.Color.White);

                // Write header row with bold styling
                worksheet.Cells[1, 1].Value = "Test Name";
                worksheet.Cells[1, 2].Value = "Result";
                worksheet.Cells[1, 1, 1, 2].Style.Font.Bold = true;

                // Write test results and apply conditional styling
                int row = 2;
                foreach (var result in testResults)
                {
                    worksheet.Cells[row, 1].Value = result.TestName;
                    worksheet.Cells[row, 2].Value = result.Result;

                    // Apply style based on the result value (case-insensitive)
                    if (result.Result.Equals("PASS", StringComparison.OrdinalIgnoreCase))
                    {
                        worksheet.Cells[row, 2].Style.ApplyFromStyle(passStyle);
                    }
                    else if (result.Result.Equals("FALSE", StringComparison.OrdinalIgnoreCase))
                    {
                        worksheet.Cells[row, 2].Style.ApplyFromStyle(falseStyle);
                    }

                    row++;
                }

                // Auto-fit columns for cleaner formatting
                worksheet.Cells.AutoFitColumns();

                // Save the final Excel file
                var filePath = Path.Combine(Environment.CurrentDirectory, "TestResults.xlsx");
                package.SaveAs(new FileInfo(filePath));
            }
        }
    }
}

Key Details to Note: #

• Case Insensitivity: The code uses StringComparison.OrdinalIgnoreCase so it works even if your result values are lowercase (like "pass" or "false").
• Style Cloning: Cloning the default cell style ensures we only override the properties we care about (background color, font color) without messing up other formatting.
• Auto-Fit Columns: This adjusts column widths to fit your content, making the Excel file easier to read.

Alternative: Using ClosedXML #

If you prefer ClosedXML over EPPlus, the approach is similar. Here's a quick snippet for styling:

using ClosedXML.Excel;
using System.Drawing;

// Inside your export method:
var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets.Add("Test Results");

var passStyle = worksheet.Style;
passStyle.Fill.BackgroundColor = XLColor.Green;
passStyle.Font.FontColor = XLColor.White;

var falseStyle = worksheet.Style;
falseStyle.Fill.BackgroundColor = XLColor.Red;
falseStyle.Font.FontColor = XLColor.White;

// After writing the result cell:
if (result.Result.Equals("PASS", StringComparison.OrdinalIgnoreCase))
{
    worksheet.Cell(row, 2).Style = passStyle;
}
else if (result.Result.Equals("FALSE", StringComparison.OrdinalIgnoreCase))
{
    worksheet.Cell(row, 2).Style = falseStyle;
}

Just install the ClosedXML NuGet package first:

Install-Package ClosedXML

That should do the trick! Your exported Excel file will now have green cells for PASS results and red cells for FALSE results.

内容的提问来源于stack exchange，提问作者Tantrix1