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

Conditional Formatting

Apr 17, 2012 at 9:27 PM

I'm quite new to EPPlus and working with XML and I needed to do quite a lot of conditional formatting, I had seen one post on how to do it but it seemed rather hard so partly for my own understanding and partly for my  own purposes I created a class to let me do it easily and quickly. Having almost finished I though i would give back so if any one else can find this useful feel free. I will update it when I come across bugs.

Disclaimer, I write VB not C# i'm an amateur, and i haven't fully tested this yet

To use create a new ConditionalFormatDef object, populate it with colours and boundary conditions then  use AddConditionalFormatting sub to apply it to an epplus range.

Any feed back/guidance would be appreciated. 

Tim

 

Imports System.Collections.Generic
Imports System.Text
Imports System.IO
Imports OfficeOpenXml
Imports System.Xml
Imports OfficeOpenXml.Drawing
Imports OfficeOpenXml.Style


Class ConditionalFormatDef
    Private P_cfvoType(2) As XmlElement
    Private P_Colour(2) As Color
    Private P_ConditionalFormatType As ConditionalFormatOptions
    Enum ConditionalFormatOptions
        DataBar = 1
        TwoColourScale = 2
        ThreeColourScale = 3
    End Enum
    Enum OneTwoThree
        One = 1
        Two = 2
        Three = 3
    End Enum
    Enum MinMax
        Min
        Max
    End Enum
    Enum BoundryType
        Percent
        Percentile
        Number
        formula
    End Enum

    Dim Doc As New XmlDocument

    ''' <summary>
    ''' Contructor for ConditionalFormatDef
    ''' Requires the number of colours in the colour scale to set
    ''' </summary>
    ''' <param name="ConditionalFormatType"> The type of the conditional format type</param>
    ''' <remarks></remarks>
    Public Sub New(ConditionalFormatType As ConditionalFormatOptions)
        Me.New()
        'Sets the type of Conditional format being applied
        P_ConditionalFormatType = ConditionalFormatType
    End Sub

    ''' <summary>
    ''' Constructor for recreating an object from an XML element to aid working with existing formating
    ''' </summary>
    ''' <param name="XMLElement"> A conditional formatting XMLElement </param>
    ''' <remarks> unfinished/tested</remarks>
    Public Sub New(XMLElement As XmlElement)
        Me.New()
        Select Case XMLElement.Name
            Case "dataBar"
                P_ConditionalFormatType = ConditionalFormatOptions.DataBar
            Case "colorScale"
                If XMLElement.ChildNodes.Count = 4 Then
                    P_ConditionalFormatType = ConditionalFormatOptions.TwoColourScale
                Else
                    P_ConditionalFormatType = ConditionalFormatOptions.ThreeColourScale
                End If
            Case Else
                Exit Sub
        End Select

        Dim ColoursCount As Integer = 0
        Dim BoundrysCount As Integer = 0

        For Each Node As XmlElement In XMLElement.ChildNodes
            If Node.Name Like "cfvo" Then

            End If
        Next
    End Sub


    ''' <summary>
    ''' Private constructor, sets some defaults in all the variables.
    ''' </summary>
    ''' <remarks>  </remarks>
    Private Sub New()

        'sets some default colours
        P_Colour(0) = Colors.Red
        P_Colour(1) = Colors.Yellow
        P_Colour(2) = Colors.Green

        'Creates CFVO Elemetns
        P_cfvoType(0) = Doc.CreateElement("cfvo")
        P_cfvoType(1) = Doc.CreateElement("cfvo")
        P_cfvoType(2) = Doc.CreateElement("cfvo")

        'Add So Default Attributes to 
        AddAttribute(P_cfvoType(0), "type", "num")
        AddAttribute(P_cfvoType(0), "val", "10")

        AddAttribute(P_cfvoType(0), "type", "num")
        AddAttribute(P_cfvoType(0), "val", "20")

        AddAttribute(P_cfvoType(0), "type", "num")
        AddAttribute(P_cfvoType(0), "val", "30")

    End Sub


    ''' <summary>
    ''' Sets the colours for the colour scale indavidualy
    ''' </summary>
    ''' <param name="inNumber">The colour number being set {1-3}</param>
    ''' <param name="inColour">The colour refference</param>
    ''' <remarks>They default to Red,Green or Red,Yellow,Green if not changed</remarks>
    Public Sub SetColour(inNumber As OneTwoThree, inColour As Color)
        P_Colour(inNumber - 1) = inColour
    End Sub


    ''' <summary>
    ''' Sets a boundry condition
    ''' </summary>
    ''' <param name="inNumber">The boundry condition to be set</param>
    ''' <param name="cell">The cell to use a refference</param>
    ''' <param name="BoundryType">The type of the boundry condition</param>
    ''' <remarks></remarks>
    Public Sub SetBoundrys(inNumber As OneTwoThree, cell As OfficeOpenXml.ExcelCellAddress, BoundryType As BoundryType)
        P_cfvoType(inNumber - 1) = Doc.CreateElement("cfvo")
        Select Case BoundryType
            Case Is = ConditionalFormatDef.BoundryType.Number
                AddAttribute(P_cfvoType(inNumber - 1), "type", "num")
            Case Is = ConditionalFormatDef.BoundryType.Percent
                AddAttribute(P_cfvoType(inNumber - 1), "type", "percent")
            Case Is = ConditionalFormatDef.BoundryType.Percentile
                AddAttribute(P_cfvoType(inNumber - 1), "type", "percentile")
            Case Is = ConditionalFormatDef.BoundryType.formula
                AddAttribute(P_cfvoType(inNumber - 1), "type", "formula")
        End Select

        AddAttribute(P_cfvoType(inNumber - 1), "val", cell.Address)
    End Sub

    ''' <summary>
    ''' Sets a boundry condition
    ''' </summary>
    ''' <param name="inNumber">The boundry condition to be set</param>
    ''' <param name="MinOrMax">The type of the boundry condition</param>
    ''' <remarks></remarks>
    Public Sub SetBoundrys(inNumber As OneTwoThree, MinOrMax As MinMax)
        P_cfvoType(inNumber - 1) = Doc.CreateElement("cfvo")
        Select Case MinOrMax
            Case ConditionalFormatDef.MinMax.Min
                AddAttribute(P_cfvoType(inNumber - 1), "type", "Min")
            Case ConditionalFormatDef.MinMax.Max
                AddAttribute(P_cfvoType(inNumber - 1), "type", "Max")
        End Select
    End Sub

    ''' <summary>
    ''' Sets a boundry condition
    ''' </summary>
    ''' <param name="inNumber">The boundry condition to be set</param>
    ''' <param name="value">The value to set the boundry condition at</param>
    ''' <param name="BoundryType">The type of the boundry condition</param>
    ''' <remarks></remarks>
    Public Sub SetBoundrys(inNumber As OneTwoThree, value As Double, BoundryType As BoundryType)
        P_cfvoType(inNumber - 1) = Doc.CreateElement("cfvo")
        Select Case BoundryType
            Case Is = ConditionalFormatDef.BoundryType.Number
                AddAttribute(P_cfvoType(inNumber - 1), "type", "num")
            Case Is = ConditionalFormatDef.BoundryType.Percent
                AddAttribute(P_cfvoType(inNumber - 1), "type", "percent")
            Case Is = ConditionalFormatDef.BoundryType.Percentile
                AddAttribute(P_cfvoType(inNumber - 1), "type", "percentile")
            Case Is = ConditionalFormatDef.BoundryType.formula
                AddAttribute(P_cfvoType(inNumber - 1), "type", "formula")
        End Select

        AddAttribute(P_cfvoType(inNumber - 1), "val", value)
    End Sub

    ''' <summary>
    ''' Sets a boundry condition
    ''' </summary>
    ''' <param name="inNumber">The boundry condition to be set</param>
    ''' <param name="value">The value to set the boundry condition at</param>
    ''' <param name="BoundryType">The type of the boundry condition</param>
    ''' <remarks></remarks>
    Public Sub SetBoundrys(inNumber As OneTwoThree, value As String, BoundryType As BoundryType)
        P_cfvoType(inNumber - 1) = Doc.CreateElement("cfvo")
        Select Case BoundryType
            Case Is = ConditionalFormatDef.BoundryType.Number
                AddAttribute(P_cfvoType(inNumber - 1), "type", "num")
            Case Is = ConditionalFormatDef.BoundryType.Percent
                AddAttribute(P_cfvoType(inNumber - 1), "type", "percent")
            Case Is = ConditionalFormatDef.BoundryType.Percentile
                AddAttribute(P_cfvoType(inNumber - 1), "type", "percentile")
            Case Is = ConditionalFormatDef.BoundryType.formula
                AddAttribute(P_cfvoType(inNumber - 1), "type", "formula")
        End Select

        AddAttribute(P_cfvoType(inNumber - 1), "val", value)
    End Sub


    ''' <summary>
    ''' Converts the colour object to an XML element
    ''' </summary>
    ''' <param name="Colour">Colour to be converted</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetColourElement(Colour As Color) As XmlElement
        GetColourElement = Doc.CreateElement("color")
        AddAttribute(GetColourElement, "rgb", Colour.ToString)
    End Function


    ''' <summary>
    ''' The function that returns the XMLElement to be intergrated into the document
    ''' </summary>
    ''' <param name="Priority">Sets the priority of the element </param>
    ''' <returns>XML Element containing the conditional formanting</returns>
    ''' <remarks></remarks>
    Public Function GetXMLElement(Priority As Integer) As XmlElement
        Dim ChildElement = Doc.CreateElement("colorScale")

        Select Case P_ConditionalFormatType
            Case ConditionalFormatOptions.dataBar

                ChildElement.AppendChild(P_cfvoType(0))
                ChildElement.AppendChild(P_cfvoType(1))

                ChildElement.AppendChild(GetColourElement(P_Colour(0)))

                GetXMLElement = Doc.CreateElement("dataBar")
                AddAttribute(GetXMLElement, "type", "dataBar")
                AddAttribute(GetXMLElement, "priority", Priority)
                GetXMLElement.AppendChild(ChildElement)

            Case ConditionalFormatOptions.ThreeColourScale
                ChildElement.AppendChild(P_cfvoType(0))
                ChildElement.AppendChild(P_cfvoType(1))
                ChildElement.AppendChild(P_cfvoType(2))

                ChildElement.AppendChild(GetColourElement(P_Colour(0)))
                ChildElement.AppendChild(GetColourElement(P_Colour(1)))
                ChildElement.AppendChild(GetColourElement(P_Colour(2)))

                GetXMLElement = Doc.CreateElement("colorScale")
                AddAttribute(GetXMLElement, "type", "colorScale")
                AddAttribute(GetXMLElement, "priority", Priority)
                GetXMLElement.AppendChild(ChildElement)

            Case ConditionalFormatOptions.TwoColourScale
                ChildElement.AppendChild(P_cfvoType(0))
                ChildElement.AppendChild(P_cfvoType(1))
                ChildElement.AppendChild(GetColourElement(P_Colour(0)))
                ChildElement.AppendChild(GetColourElement(P_Colour(1)))

                GetXMLElement = Doc.CreateElement("colorScale")
                AddAttribute(GetXMLElement, "type", "colorScale")
                AddAttribute(GetXMLElement, "priority", Priority)
                GetXMLElement.AppendChild(ChildElement)

        End Select

    End Function


End Class

Module ApplyConditionalFormat

    Public Sub AddAttribute(inXmlElement As XmlElement, inAttributeName As String, inAttributeValue As String)
        Dim xmlAttribute As XmlAttribute = inXmlElement.OwnerDocument.CreateAttribute(inAttributeName)
        xmlAttribute.Value = inAttributeValue
        inXmlElement.SetAttributeNode(xmlAttribute)
    End Sub

 
    ''' <summary>
    ''' Sub that adds the conditional formating XMLElement to the excel doc
    ''' </summary>
    ''' <param name="inSqref">Range to apply the conditional formatting to</param>
    ''' <param name="ConditionalFormatdef">Conditional format object</param>
    ''' <remarks></remarks>
    Public Sub AddConditionalFormatting(inSqref As OfficeOpenXml.ExcelRange, ConditionalFormatdef As ConditionalFormatDef)

        ' The WorksheetXml
        Dim xmlDoc As XmlDocument = inSqref.Worksheet.WorksheetXml

        ' Root element of the WorksheetXml
        Dim root As XmlElement = xmlDoc.DocumentElement

        Dim eleConditionalFormatting As XmlElement = xmlDoc.CreateElement("conditionalFormatting", root.NamespaceURI)
        AddAttribute(eleConditionalFormatting, "sqref", inSqref.Address)

        ' </cfRule>
        ' -----------------------------------------
        eleConditionalFormatting.AppendChild(ConditionalFormatdef.GetXMLElement(1))

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

    End Sub
End Module

Coordinator
May 2, 2012 at 6:03 PM

Hi Tim,

Sorry for my late replay, I have been sick for 1 1/2 week, so I haven't done much lately.

Thanks for your code. Eyal in the project is working on an implement of conditional formatting to the next version (probably not all features).

Thanks Jan