Epplus excel conditional formatting databar max value

Question

I'm creating an excel file with epplus, and I'm trying to add a databar with the following code:

var bar = pck.Workbook.Worksheets[1].ConditionalFormatting.AddDatabar(new ExcelAddress("I4:I56"), System.Drawing.Color.Blue);
            bar.HighValue.Type = eExcelConditionalFormattingValueObjectType.Num;
            bar.LowValue.Type = eExcelConditionalFormattingValueObjectType.Num;
            bar.HighValue.Value = 600;
            bar.LowValue.Value = 0;

However, cells with the value 600 do not fully fill. They look like this:

When I expect it to look like this, which is how it comes out if you set it to 600 within excel:

I have tried adding the xml manually as suggested in this answer: https://stackoverflow.com/a/33568285/1308743 and the same issue persists.

Answer 1

The answer in the link you provided does work for me, you might just need to update the cell reference in xm:sqref to point to your I4:I56 address instead, which I believe is why it does not work for you.

To explain the visual issue, the dataBar that EPPlus generates in the xml is missing the minLength and maxLength attributes. These attributes define the fixed width percentage that the data bar fills. By default, the min length is 10% and max length is 90%.

If you don't care about removing the gradient, you can directly add the minLength and maxLength without creating the extension list like in the linked answer if you want a simpler solution:

// All the code below goes after your code that creates the data bar
var ws = pck.Workbook.Worksheets[1];

// Create namespace manager using default uri to be able to select nodes.
var ns = new XmlNamespaceManager(ws.WorksheetXml.NameTable);
ns.AddNamespace("d", ws.WorksheetXml.DocumentElement.NamespaceURI);

// Get all the dataBar elements and set the missing min and max length attributes.
var dataBarElements = ws.WorksheetXml.SelectNodes("//d:dataBar", ns);
foreach (XmlElement dataBarElement in dataBarElements)
{
    dataBarElement.SetAttribute("minLength", "0");
    dataBarElement.SetAttribute("maxLength", "100");
}

The above applies it to all data bars. If you want to only apply it to the one databar like for I4:I56, change the selection to:

var dataBarElement = (XmlElement)ws.WorksheetXml.SelectSingleNode("//d:conditionalFormatting[@sqref='I4:I56']//d:dataBar", ns);
dataBarElement.SetAttribute("minLength", "0");
dataBarElement.SetAttribute("maxLength", "100");

If you need to turn off the gradient, you will need to use the answer in your link because the gradient attribute doesn't seem to be supported on dataBar elements not in an extension list.

I also suggest using the Open XML SDK Productivity Tools to view/compare the xml in Excel documents.