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...

Answer 1

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.