C# EPPlus databar conditional formatting with solid fill color

Question

I am generating excel reports that involves several columns that are percentage data. Since the reports are for presentation purposes I want to make them look nice by formatting the percentage data with databars with solid fill. Somehow this proves to be extremely difficult as there is no direct setting in EPPlus for solid fill for databar but nevertheless I have arrived at the answer that is in this post:

Inconsistent appearance between manual and coded versions of solid databar and databar minimum value

However no matter how hard I try to edit the code for my application I only have one column that end up with solid fill with the rest being gradient. Even though I changed the node in the question to a nodelist such as below:

        var cfNodes = xdoc.SelectNodes("/default:worksheet/default:conditionalFormatting/default:cfRule", nsm);
        foreach(XmlNode cfNode in cfNodes)
        {
            cfNode.AppendChild(extLstCf);
        }

and also for the worksheet elements:

        var wsNodes = xdoc.SelectNodes("/default:worksheet", nsm);
        foreach(XmlElement wsNode in wsNodes)
        {
            wsNode.AppendChild(extLstWs);
        }

I also tried playing around with the xml changing the <sqref> parameter but that still doesn't cover all my databar columns. I think there has to be something that I can change in the xml to accomplish what I want but I don't know what to look for...

score 2 · Accepted Answer · answered Oct 05 '18 at 14:13

Ok guys It took me a few days but I finally figured it out. There might be a simpler way to do this but so far this is how I got it working for me:

A worksheet xml extension list node need to be appended at the worksheet level node which includes the number data bar elements that your worksheet contains, and each of them needs to have gradient = 0 for solid fill I. For example my worksheet contains two data bars so mine looks like this:

        var extLstWs = xdoc.CreateNode(XmlNodeType.Element, "extLst", xdoc.DocumentElement.NamespaceURI);
        extLstWs.InnerXml = @"<ext uri=""{78C0D931-6437-407d-A8EE-F0AAD7539E65}"" 
                                        xmlns:x14=""http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"">
                                    <x14:conditionalFormattings>
                                    <x14:conditionalFormatting xmlns:xm=""http://schemas.microsoft.com/office/excel/2006/main"">
                                    <x14:cfRule type=""dataBar"" id=""{3F3F0E19-800E-4C9F-9CAF-1E3CE014ED86}"">
                                        <x14:dataBar minLength=""0"" maxLength=""100"" gradient=""0"">
                                        <x14:cfvo type=""num"">
                                            <xm:f>0</xm:f>
                                        </x14:cfvo>
                                        <x14:cfvo type=""num"">
                                            <xm:f>100</xm:f>
                                        </x14:cfvo>
                                        <x14:negativeFillColor rgb=""FFFF0000""/><x14:axisColor rgb=""FF000000""/>
                                        </x14:dataBar>
                                    </x14:cfRule>
                                    <xm:sqref>A1:A20</xm:sqref>
                                    </x14:conditionalFormatting>
                                    <x14:conditionalFormatting xmlns:xm=""http://schemas.microsoft.com/office/excel/2006/main"">
                                        <x14:cfRule type=""dataBar"" id=""{3F3F0E19-800E-4C9F-9CAF-1E3CE014ED86}"">
                                        <x14:dataBar minLength=""0"" maxLength=""100"" gradient=""0"">
                                            <x14:cfvo type=""num"">
                                            <xm:f>0</xm:f>
                                            </x14:cfvo><x14:cfvo type=""num"">
                                            <xm:f>200</xm:f>
                                            </x14:cfvo><x14:negativeFillColor rgb=""FFFF0000""/>
                                            <x14:axisColor rgb=""FF000000""/>
                                        </x14:dataBar>
                                        </x14:cfRule>
                                        <xm:sqref>B1:B20</xm:sqref>
                                    </x14:conditionalFormatting>
                                    </x14:conditionalFormattings>
                                </ext>";
        var wsNode = xdoc.SelectSingleNode("/default:worksheet", nsm);
        wsNode.AppendChild(extLstWs);

Notice how I got two subnodes of <x14:conditionalFormattings> in there, one for each databar.

Secondly another extension list for conditional formatting rule nodes need to be appended under the <cfRule> node, also one for each databar. I was able to use a foreach loop to find all the databars in my worksheet and append the same xml to each of them like below:

        var cfNodes = xdoc.SelectNodes("/default:worksheet/default:conditionalFormatting/default:cfRule", nsm);
        foreach (XmlNode cfnode in cfNodes)
        {
            var extLstCfNormal = xdoc.CreateNode(XmlNodeType.Element, "extLst", xdoc.DocumentElement.NamespaceURI);
            extLstCfNormal.InnerXml = @"<ext uri=""{B025F937-C7B1-47D3-B67F-A62EFF666E3E}"" 
                            xmlns:x14=""http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"">
                            <x14:id>{3F3F0E19-800E-4C9F-9CAF-1E3CE014ED86}</x14:id></ext>";

            cfnode.AppendChild(extLstCfNormal);
        }

After doing the above I was finally able to show all my databars with solid fill.

Kosmo零 · Answer 2 · 2019-10-16T17:51:53.490

This is my version how to make databars solid fill for those who fail to apply Aowei Xu's solution (like me...).

public static Random Rnd = new Random();

public static string GenerateXlsId()
{
    //{29BD882A-B741-482B-9067-72CC5D939236}

    string id = string.Empty;

    for (int i = 0; i < 32; i++)
        if (Rnd.NextDouble() < 0.5)
            id += Rnd.Next(0, 10);
        else
            id += (char)Rnd.Next(65, 91);

    id = id.Insert(8, "-");
    id = id.Insert(13, "-");
    id = id.Insert(18, "-");
    id = id.Insert(23, "-");

    return id;
}

public static void FixDatabarsAtWorksheet(OfficeOpenXml.ExcelWorksheet eworksheet)
{
    System.Xml.XmlNodeList databars = eworksheet.WorksheetXml.GetElementsByTagName("dataBar");

    if (databars.Count > 0)
    {
        string conditional_formattings_str = string.Empty;

        for (int i = 0; i < databars.Count; i++)
        {
            string temp_databar_id = GenerateXlsId();

            databars[i].ParentNode.InnerXml += @"<extLst>
        <ext uri=""{B025F937-C7B1-47D3-B67F-A62EFF666E3E}"" xmlns:x14=""http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"">
            <x14:id>{" + temp_databar_id + @"}</x14:id>
        </ext>
    </extLst>";
            //--

            string temp_sqref = databars[i].ParentNode.ParentNode.Attributes["sqref"].Value;
            string left_type = string.Empty;
            string left_val = string.Empty;
            string right_type = string.Empty;
            string right_val = string.Empty;
            string color = string.Empty;
            Color databar_fill_color = Color.Empty;
            Color databar_border_color = Color.Empty;

            for (int j = 0; j < databars[i].ChildNodes.Count; j++)
                if (databars[i].ChildNodes[j].LocalName == "cfvo" && databars[i].ChildNodes[j].Attributes["type"] != null)
                {
                    if (string.IsNullOrEmpty(left_type))
                        left_type = databars[i].ChildNodes[j].Attributes["type"].Value;
                    else if (string.IsNullOrEmpty(right_type))
                        right_type = databars[i].ChildNodes[j].Attributes["type"].Value;

                    if (databars[i].ChildNodes[j].Attributes["val"] != null)
                        if (string.IsNullOrEmpty(left_val))
                            left_val = databars[i].ChildNodes[j].Attributes["val"].Value;
                        else if (string.IsNullOrEmpty(right_val))
                            right_val = databars[i].ChildNodes[j].Attributes["val"].Value;
                }
                else if (databars[i].ChildNodes[j].LocalName == "color")
                {
                    color = databars[i].ChildNodes[j].Attributes["rgb"].Value;
                    int argb = Int32.Parse(color, System.Globalization.NumberStyles.HexNumber);
                    databar_fill_color = Color.FromArgb(argb);

                    databar_border_color = Color.FromArgb(255,
                        databar_fill_color.R - 50 < 0 ? databar_fill_color.R + 50 : databar_fill_color.R - 50,
                        databar_fill_color.G - 50 < 0 ? databar_fill_color.R + 50 : databar_fill_color.G - 50,
                        databar_fill_color.B - 50 < 0 ? databar_fill_color.R + 50 : databar_fill_color.B - 50);
                }

            string temp_conditional_formatting_template = @"<x14:conditionalFormatting xmlns:xm=""http://schemas.microsoft.com/office/excel/2006/main"">
        <x14:cfRule type=""dataBar"" id=""{" + temp_databar_id + @"}"">
            <x14:dataBar minLength=""" + (string.IsNullOrEmpty(left_val) ? "0" : left_val) + "\" maxLength=\"" + (string.IsNullOrEmpty(right_val) ? "100" : right_val) + "\" gradient=\"0\" " + (databar_border_color.IsEmpty ? string.Empty : "border = \"1\"") + ">";

            temp_conditional_formatting_template += Environment.NewLine + "<x14:cfvo type=\"" + (left_type.ToLower() == "min" ? "autoMin" : left_type) + "\" />";
            temp_conditional_formatting_template += Environment.NewLine + "<x14:cfvo type=\"" + (right_type.ToLower() == "max" ? "autoMax" : right_type) + "\" />";

            if (!databar_border_color.IsEmpty)
                temp_conditional_formatting_template += Environment.NewLine + "<x14:borderColor rgb=\"" + BitConverter.ToString(new byte[] { databar_border_color.A, databar_border_color.R, databar_border_color.G, databar_border_color.B }).Replace("-", "") + "\" />";

            temp_conditional_formatting_template += Environment.NewLine + @"</x14:dataBar>
        </x14:cfRule>
        <xm:sqref>" + temp_sqref + @"</xm:sqref>
    </x14:conditionalFormatting>";

            conditional_formattings_str += temp_conditional_formatting_template;
        }

        databars[0].ParentNode.ParentNode.ParentNode.InnerXml += @"<extLst>
<ext uri=""{78C0D931-6437-407d-A8EE-F0AAD7539E65}"" xmlns:x14=""http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"">
<x14:conditionalFormattings>" + conditional_formattings_str + @" 
</x14:conditionalFormattings>
</ext>
</extLst>";
    }
}

Such pain for such small thing... Gwa-a-a-a-a-ar-r-r-r!

P.S. MS, I hate you for XmlElements skipping prefix value when inserting them into parent nodes!

P.S.2. This makes any other conditional formatting to vanish... don't know why... OMG!

C# EPPlus databar conditional formatting with solid fill color

2 Answers2