This project has moved. For the latest updates, please go here.

[Sample] Adding Conditional Formatting

Developer
Mar 12, 2012 at 2:52 AM
Edited Mar 12, 2012 at 2:53 AM

Hi,

hope this sample will help you to add Conditional Formatting to a Worksheet.

There are many types of possible Conditional Formatting. I chose the "Color Scale" as an example.

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Xml;
using OfficeOpenXml;

namespace EPPlus
{
  public class Class1
  {
    /// <summary>
    /// Sample of adding Conditional Formatting to a EPPlus Worksheet.
    /// </summary>
    /// <param name="args"></param>
    static void Main(string[] args)
    {
      FileInfo newFile = new FileInfo(@"Conditional Formatting Sample.xlsx");

      if (newFile.Exists)
      {
        newFile.Delete();  // ensures we create a new workbook
        newFile = new FileInfo(@"Conditional Formatting Sample.xlsx");
      }

      using (ExcelPackage package = new ExcelPackage(newFile))
      {
        ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Conditional Formatting");

        // Fill the first 10 lines of the first column with sequencial numbers
        for (int i = 1; i <= 10; i++)
        {
          worksheet.Cells[i, 1].Value = i;
        }

        // Add Conditional Formatting for the cells we just filled
        AddConditionalFormatting(
          worksheet,
          "A1:A10",
          "FFF8696B",
          "FF63BE7B");

        // Save the Excel Workbook
        package.Save();
      }
    }

    /// <summary>
    /// Add Conditional Formatting to an EPPlus Worksheet, using the "colorScale" formatting option.
    /// </summary>
    /// <param name="inWorksheet">EPPlus Worksheet object</param>
    /// <param name="inSqref">Range of cells to format</param>
    /// <param name="inColor1">From Color</param>
    /// <param name="inColor2">To Color</param>
    static public void AddConditionalFormatting(
      ExcelWorksheet inWorksheet,
      string inSqref,
      string inColor1,
      string inColor2)
    {
      // Example of a XML used to format A1:A10 cells, with a Color Scale:
      //
      //  <conditionalFormatting sqref="A1:A10">
      //    <cfRule type="colorScale" priority="1">
      //      <colorScale>
      //        <cfvo type="min"/>
      //        <cfvo type="max"/>
      //        <color rgb="FFF8696B"/>
      //        <color rgb="FF63BE7B"/>
      //      </colorScale>
      //    </cfRule>
      //  </conditionalFormatting>

      // The WorksheetXml
      XmlDocument xmlDoc = inWorksheet.WorksheetXml;

      // Root element of the WorksheetXml
      XmlElement root = xmlDoc.DocumentElement;

      // Generic element
      XmlElement xmlElement;

      // -----------------------------------------
      // <conditionalFormatting sqref="A1:A10">
      // -----------------------------------------
      XmlElement eleConditionalFormatting = xmlDoc.CreateElement("conditionalFormatting", root.NamespaceURI);
      AddAttribute(eleConditionalFormatting, "sqref", inSqref);

      // -----------------------------------------
      // <cfRule type="colorScale" priority="1">
      // -----------------------------------------
      XmlElement eleCfRule = xmlDoc.CreateElement("cfRule", root.NamespaceURI);
      AddAttribute(eleCfRule, "type", "colorScale");
      AddAttribute(eleCfRule, "priority", "1");

      // -----------------------------------------
      // <colorScale>
      // -----------------------------------------
      XmlElement eleColorScale = xmlDoc.CreateElement("colorScale", root.NamespaceURI);

      // -----------------------------------------
      // <cfvo type="min"/>
      // -----------------------------------------
      xmlElement = xmlDoc.CreateElement("cfvo", root.NamespaceURI);
      AddAttribute(xmlElement, "type", "min");
      eleColorScale.AppendChild(xmlElement);

      // -----------------------------------------
      // <cfvo type="max"/>
      // -----------------------------------------
      xmlElement = xmlDoc.CreateElement("cfvo", root.NamespaceURI);
      AddAttribute(xmlElement, "type", "max");
      eleColorScale.AppendChild(xmlElement);

      // -----------------------------------------
      // <color rgb="FFF8696B"/>
      // -----------------------------------------
      xmlElement = xmlDoc.CreateElement("color", root.NamespaceURI);
      AddAttribute(xmlElement, "rgb", inColor1);
      eleColorScale.AppendChild(xmlElement);

      // -----------------------------------------
      // <color rgb="FF63BE7B"/>
      // -----------------------------------------
      xmlElement = xmlDoc.CreateElement("color", root.NamespaceURI);
      AddAttribute(xmlElement, "rgb", inColor2);
      eleColorScale.AppendChild(xmlElement);

      // -----------------------------------------
      // </colorScale>
      // -----------------------------------------
      eleCfRule.AppendChild(eleColorScale);

      // -----------------------------------------
      // </cfRule>
      // -----------------------------------------
      eleConditionalFormatting.AppendChild(eleCfRule);

      // -----------------------------------------
      // </conditionalFormatting>
      // -----------------------------------------
      root.InsertAfter(eleConditionalFormatting, root.LastChild);
    }

    /// <summary>
    /// Add an attribute to a XML Element.
    /// </summary>
    /// <param name="inXmlElement">XML Element to add the attribute to</param>
    /// <param name="inAttributeName">Attribute name</param>
    /// <param name="inAttributeValue">Attribute value</param>
    static public void AddAttribute(
      XmlElement inXmlElement,
      string inAttributeName,
      string inAttributeValue)
    {
      XmlAttribute xmlAttribute = inXmlElement.OwnerDocument.CreateAttribute(inAttributeName);
      xmlAttribute.Value = inAttributeValue;
      inXmlElement.SetAttributeNode(xmlAttribute);
    }
  }
}
Coordinator
Mar 15, 2012 at 8:39 PM

Thanks!

Jan

Jun 1, 2012 at 7:35 AM
Edited Jun 1, 2012 at 7:36 AM

Hi All,

I am just stuck on one issue.

I am having a Pivot Table which is having  5 rows with 2 value columns. (e.g. value1 and value2)

And my need is to color RED all those cells of value1 column which is having corresponding value2 column value less then 50.

e.g.:

Value1 Value2
100 20
100 70


 

 

I know that I need to apply a conditional formatting for this. But no idea how to do.

If some one can help with sample code.

Note: number of value columns will always be 2 but number of rows may differ. 

Pranay...

Aug 9 at 7:21 AM
Hello,

I want to create a custom conditional formatting with an Icon set. I have posted my request here.
I don't know all of the Xml attributes, but you are using "color" attribute : do you know which attribute I need to change to apply my custom CF ?

Thank you !