EPPlus Barchart bars not showing colors for negative value in Excel 2013, but works fine in Excel 2007

Question

I am using a BarClustered chart using EPPlus for Excel Package in C#. I am able to generate the bar chart as required. Only problem I am facing is that when I have a negative value, the bar does not show any color. It would be as if a transparent bar with only the border.

I am facing this issue with Excel 2013. However this works fine in Excel 2007.

ExcelWorksheet wsDataSource = xlPackage.Workbook.Worksheets.Add("DataSource");
wsDataSource.Hidden = eWorkSheetHidden.VeryHidden;
var namedStyle = xlPackage.Workbook.Styles.CreateNamedStyle("HyperLink");   
namedStyle.Style.Font.UnderLine = true;
namedStyle.Style.Font.Color.SetColor(Color.Blue);

//Here I iterate through an array and populate the wsDataSource values as below starting from 3rd row:
Feb     2000    5000
March   -2000   2770
April   4000    4643

var chart = worksheet.Drawings.AddChart("Chart", OfficeOpenXml.Drawing.Chart.eChartType.BarClustered);
//row is the offset int variable
chart.SetPosition(row + 2, 0, 0, 10);
chart.SetSize(750, 30);
chart.Title.Text = "Data Graph";
chart.Legend.Position = eLegendPosition.Top;
var barChart = chart as ExcelBarChart;
barChart.DataLabel.ShowValue = true;

var mySeries = chart.Series.Add(wsDataSource.Cells[3, 2, intDataRow - 1, 2], wsDataSource.Cells[3, 1, intDataRow - 1, 1]);
mySeries.Header = "Current Year";

//isPreviousYearDataAvailable is a boolean which indicates if previous year data for the user is available.
if (isPreviousYearDataAvailable)
{
    var mySeries2 = chart.Series.Add(wsDataSource.Cells[3, 3, intDataRow, 3], wsDataSource.Cells[3, 1, intDataRow - 1, 1]);
    mySeries2.Header = "Previous Year"
}

Below is the image I get for negative values when opening using Excel 2013.

Answer 1

It appears that EPPlus doesn't have support for the "invertIfNegative" tag for data series. You might have to contact the authors for help, or add in the feature yourself. The Open XML specs state that:

This element specifies the parent element shall invert its colors if the value is negative.

Also:

A value of on, 1, or true specifies that the property is applied. This is the default value for this attribute, and is implied when the parent element is present, but this attribute is omitted.

Since EPPlus doesn't render this tag, the default value is used, which is "true". The "parent element" in this case is the data series XML element. So this means colors will be inverted if the cell value is negative. Hence the transparent color you see.

I have found that different versions of Excel obey the Open XML specs slightly differently. Excel 2013 appears to obey more strictly to the Open XML specs, which is why you get a transparent color. Excel 2007 probably ignored the absent "invertIfNegative" tag (meaning if absent, you don't want to have anything to do with inverting colors and so on, and so Excel will just render the color). In this sense, Excel 2007 is more forgiving of mistakes, which may or may not be a good thing.

Answer 2

Adding <c:invertIfNegative val="0"/> to the XML manually seemed to work for me:

System.Xml.XmlNode invertIfNegativeNode = chart.ChartXml.CreateElement(
    "c", "invertIfNegative", "http://schemas.openxmlformats.org/drawingml/2006/chart");
System.Xml.XmlAttribute invertIfNegativeAttribute = chart.ChartXml.CreateAttribute("val");
invertIfNegativeAttribute.Value = "0";
invertIfNegativeNode.Attributes.Append(invertIfNegativeAttribute);
chart.ChartXml.DocumentElement["c:chart"]["c:plotArea"]["c:barChart"]["ser"].AppendChild(invertIfNegativeNode);

Answer 3

Adding to this old post, I hit the same problem in Excel 2016 but was unable to resolve it with @Saxon Druce answer directly. When I examine chart1.xml behind the Excel, only ser contains invertIfNegative and setting it's val attribute to 0 does not apply to any bar (still remain inverted / transparent). When I open the file in Excel and uncheck Series Option/Invert if negative option and reopen chart1.xml, I found each and every bar dPt is inject with invertIfNegative node.

So I modified @Saxon Druce answer a bit, create and append invertIfNegative node to each bar's dPt (2nd arrow) instead of appending to parent node ser (1st arrow). Then the ExcelBarChart turns out ok with all bars colored (not inverted).

I banged my head for hours so hopefully this help someone with Excel 2016.

var nsuri = chartXml.DocumentElement.NamespaceURI;
var dPt = chartXml.CreateNode(XmlNodeType.Element, "dPt", nsuri);
var invertIfNegative = chartXml.CreateNode(XmlNodeType.Element, "invertIfNegative", nsuri);
var att = chartXml.CreateAttribute("val", nsuri);
att.Value = "0";
invertIfNegative.Attributes.Append(att);
dPt.AppendChild(invertIfNegative);

// Other xml changes
var idx = chartXml.CreateNode(XmlNodeType.Element, "idx", nsuri);
att = chartXml.CreateAttribute("val", nsuri);
att.Value = i.ToString();
idx.Attributes.Append(att);
dPt.AppendChild(idx);