How to stop other conditional formatting from disappearing when hackmodding databars into solid fills?

Question

EPPlus has no support for extLst thing which is needed to make databars conditional formatting with solid fill. They are gradient by themselves without modifications.

I coded this to modify worksheet's xml directly (this gets databars from worksheet XML and then adds required extLst nodes):

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>";
    }
}

And this really makes databars solid fill, the problem is any other conditional formatting like GreaterThan loses it's style when true.

For example I add databar and GreaterThan 123 (green) conditional formattings. Excel still see coditional formatting rule GreaterThan 123, but the style is not set when it's true (green is not set). While databars is displayed correctly at same time.

I don't know where to look... Someone help!

score 1 · Accepted Answer · answered Oct 17 '19 at 21:50

Thats the problem with hacks - they are so fragile! :)

I was able to get it work with another hack - setting the style differential formatting (dxf) reference which seems to be dropped when epplus saves. What might be happening is epplus only thinks there is one dxf on save so it doesnt set the value since excel will assume it is the first dxf style (index 0) but that is a bit of a guess.

Anyway, if you set the dxfid via XML manually it will find it. But order counts here, you have to apply the databar hack last otherwise it will hit the wrong reference:

[TestMethod]
public void FixDatabarsAtWorksheetTest()
{
    //https://stackoverflow.com/questions/58417819/how-to-stop-other-conditional-formatting-from-disappearing-when-hackmodding-data
    //Throw in some data
    var datatable = new DataTable("tblData");
    datatable.Columns.AddRange(new[]
    {
        new DataColumn("Col1", typeof(int)), new DataColumn("Col2", typeof(int)), new DataColumn("Col3", typeof(object))
    });

    for (var i = 0; i < 10; i++)
    {
        var row = datatable.NewRow();
        row[0] = i;
        row[1] = i * 10;
        row[2] = Path.GetRandomFileName();
        datatable.Rows.Add(row);
    }

    //Create a test file
    var fi = new FileInfo(@"c:\temp\FixDatabarsAtWorksheetTest.xlsx");
    if (fi.Exists)
        fi.Delete();

    using (var pck = new ExcelPackage(fi))
    {
        var workbook = pck.Workbook;
        var doc = workbook.Worksheets.Add("Sheet1");
        doc.Cells.LoadFromDataTable(datatable, true);

        //Set the greater than
        var gtConditional = doc
            .ConditionalFormatting
            .AddGreaterThan(doc.Cells["A2:A11"]);

        gtConditional.Formula = "2";
        gtConditional.Style.Fill.BackgroundColor.Color = Color.GreenYellow;

        //Fix the gt
        var xdoc = doc.WorksheetXml;
        var nsm = new XmlNamespaceManager(xdoc.NameTable);
        nsm.AddNamespace("default", xdoc.DocumentElement.NamespaceURI);
        var gtNode = xdoc.SelectSingleNode("/default:worksheet/default:conditionalFormatting[@sqref=\"A2:A11\"]", nsm);

        //Create the new attribute for table
        var att = xdoc.CreateAttribute("dxfId");
        att.Value = "0";
        gtNode
            .FirstChild
            .Attributes.Append(att);

        //Set the bar condition LAST
        var barConditional = doc
            .ConditionalFormatting
            .AddDatabar(doc.Cells["B2:B11"], Color.FromArgb(99, 195, 132));

        barConditional.HighValue.Type = eExcelConditionalFormattingValueObjectType.Num;
        barConditional.LowValue.Type = eExcelConditionalFormattingValueObjectType.Num;

        barConditional.HighValue.Value = 82;
        barConditional.LowValue.Value = 0;

        FixDatabarsAtWorksheet(doc);

        pck.Save();
    }
}

I get this:

Not sure how feasible this is for you depending on how many conditional formats you have but its worth a shot.

Thank you. Even through this is fragile as well. In case of multi conditional formatting (that is my case exactly). You said what's the problem is, so I will continue to look for solution... Actually, I already found another solution - to unpack xlsx after saving by `EPPlus` and apply `FixDatabarsAtWorksheet` to already written files (in that case `dxfId` is already there), but ZipArchive produce corrupted xlsx files... I described it here: https://stackoverflow.com/questions/58433045/how-to-prevent-system-io-compression-ziparchive-from-corrupting-xlsx-file — Kosmo零, Oct 18 '19 at 09:32
See my comment in your question, you should be able to accomplish it that way as well, just a little more expensive with the `IO`. It is exactly what was done here: https://stackoverflow.com/questions/56392364/replacing-invalid-xml-characters-from-an-excel-file-and-writing-it-back-to-disk/56403083#56403083 — Ernie S, Oct 18 '19 at 12:44
Looks like you know a lot about Excel. I met another problem with this code. I just found that sometimes my code leave databars as gradients. I tracked the differences till `id` level. When I use `id` made by excel - databars becomes solid fill. When I use own generated `ids`... sometimes databars solid and sometimes no. Do you know where I can read rules about how to generate this `id`? Example: `929AC576-F2BB-44B5-9105-569891D267CA` - solid databars, `3YT2WE7Y-ZB97-RBE7-FPTM-R40996BC0046` - gradient databars. — Kosmo零, Oct 18 '19 at 16:27
I cant say I know the magic formula to create them. Any time I have ran into this I have always hacked it in by using one generated by Excel after doing a File->Save. Here is the API documentation about that class, maybe it will give you some clue: https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.conditionalformattingruleextension?view=openxml-2.8.1 Maybe posting a question/issue on the EPPlus GitHub page might get some result. I would think the authors would be able to tell you more about it but they do not seem to be very responsive these days. — Ernie S, Oct 18 '19 at 21:03
Thank you. I found the exact rule how to generate this id (ST_GUID) in ISO document `[ISO/IEC-29500-1] section 22.9.2.4`. It is said that letters should be in A-F range, but my function generate them in A-Z range. Other things were correct. — Kosmo零, Oct 21 '19 at 06:13

How to stop other conditional formatting from disappearing when hackmodding databars into solid fills?

1 Answers1

Linked