0

In the most simplest,shortest,easiest understandable words:

I have a program that automatically creates and output results of a tests into an excel file. Wherever it says PASS in the results column I want the cell color to change to green or else if its FALSE I want to change it to Red.

How would I do this?

Current Code for Exporting Results: (Comments Asked for it)

    using System;
    using System.Collections.Generic;
    using System.IO;
    using System.Linq;
    using System.Text;

    namespace PortfolioVariations
  {
public class FileOutputter
{
    private const char DELIM = '\t';
    private VariationRunConfig Config { get; set; }

    public FileOutputter(VariationRunConfig p_Config)
    {
        Config = p_Config;
    }

    private string CreateOutputRow(RunResult p_From)
    {
        const string passed = "PASS",
            failed = "FAIL",
            error = "ERROR";

        if (p_From.HasWebsiteResult)
        {
            string passedStatus = p_From.GetHasPassed() ? passed : failed;
            return $"{p_From.TestCaseNumber}{DELIM}{passedStatus}{DELIM}{p_From.TestCaseExpectedAmount:C}{DELIM}{p_From.GetPriceText()}{DELIM}{p_From.TestCaseStatus}{DELIM}{p_From.Result}{DELIM}{p_From.QuoteRef}{DELIM}{p_From.ExpectedEndorsements}{DELIM}{p_From.ActualEndorsements}";
        }
        else
        {
            return $"{p_From.TestCaseNumber}{DELIM}{failed}{DELIM}{p_From.TestCaseExpectedAmount:C}{DELIM}{error}{DELIM}{p_From.TestCaseStatus}{DELIM}{error}{DELIM}{error}";
        }
    }

    public string DoFileOutput(IEnumerable<RunResult> p_Results, TimeSpan p_TimeTaken)
    {
        if (!Config.EnableOutput)
        {
            return string.Empty;
        }

        string outputFileName = Path.Combine(Config.OutputFolder, string.Format(Config.OutputFileNameFormat, DateTime.Now)),
            outputResult = new[] { $"{p_TimeTaken.Hours}:{p_TimeTaken.Minutes}:{p_TimeTaken.Seconds}",

                $"Case{DELIM}Result{DELIM}Expected Premium{DELIM}Actual Premium{DELIM}Expected status{DELIM}Actual Status{DELIM}Quote Reference{DELIM}Expected Endorsements{DELIM}Actual Endorsements"}
        .Concat(p_Results.Select(CreateOutputRow))
        .Aggregate(new StringBuilder(), (sb, s) => sb.AppendLine(s)).ToString();

        using (StreamWriter of = new StreamWriter(outputFileName, false, Encoding.Unicode))
        {
            of.Write(outputResult);
            of.Flush();
        }

        return outputFileName;
    }
}
}
Tantrix1
  • 89
  • 1
  • 1
  • 8
  • There are multiple ways to accomplish this, but not all of them would be applicable to your use case. Please provide more details on how the file is being generated and be sure to include code. – Dan Wilson Mar 19 '18 at 16:32
  • Updated Answer with Code, im sure this would only complicate things. But i guess it could be easier whilst actually outputting to excel and make changes there and there. There it is anyway – Tantrix1 Mar 19 '18 at 16:37
  • Outputting plain TSV (tab-separated values) will not give you cell coloring. You'll need to output the data using a library that can write OfficeOpenXml format (xlsx), such as https://github.com/JanKallman/EPPlus. See https://stackoverflow.com/questions/16998447/set-custom-backgroundcolor-of-a-excel-sheet-cell-using-epplus-c-sharp for an example. – Dan Wilson Mar 19 '18 at 16:47
  • Cheers Ill have a look at that. – Tantrix1 Mar 19 '18 at 16:50

0 Answers0