eDisplayBlanksAs.Gap is not working!?!

Nov 19, 2012 at 4:49 AM

I use the following code to try to set up to "Show empty cells as: Gaps". But it seems not working because I can still see it is set to "Zero" in the "Hidden and Empty Cell Settings" window.

var chart1 = ews01.Drawings.AddChart("Chart - 1", eChartType.LineMarkers);
chart1.SetPosition(5, 5);
chart1.SetSize(1150, 340);
chart1.DisplayBlanksAs = eDisplayBlanksAs.Gap;

but if I change Gap to Span --> chart1.DisplayBlanksAs = eDisplayBlanksAs.Span;

it works! it is set to "Connect data points with line" in the "Hidden and Empty Cell Settings" window.

Does any one get any idea? Thanks

May 31, 2013 at 8:03 PM
Edited May 31, 2013 at 8:04 PM
Did you get any resolution to "eDisplayBlanksAs.Gap is not working!?!"? I have the same issue.
Apr 29, 2014 at 4:14 PM
Jun 24, 2014 at 8:58 PM
Seems like its a bug.

In the chart.xml file inside your excel zip library, if you set with Gap, it actually doesn't include the node.
My work around is as follows, works great.

You need to set it as span, so the node actually gets created.
chart.DisplayBlanksAs = eDisplayBlanksAs.Span;

Now you update it by calling my formatChartGap function, which will replace the xml
xml before:
<c:dispBlanksAs val="span"/>
xml after:
<c:dispBlanksAs val="gap"/>

        nsmgr = new XmlNamespaceManager(doc.NameTable);
        nsmgr.AddNamespace("r", @"http://schemas.openxmlformats.org/officeDocument/2006/relationships");
        nsmgr.AddNamespace("a", @"http://schemas.openxmlformats.org/drawingml/2006/main");
        nsmgr.AddNamespace("c", @"http://schemas.openxmlformats.org/drawingml/2006/chart");

    public void formatChartGap(XmlDocument xdoc)
        var root = xdoc.DocumentElement;
        XmlNode xn;

         xn = root.SelectSingleNode("//c:chart/c:dispBlanksAs", nsmgr);

         xn.Attributes["val"].Value = "gap";

Jun 24, 2014 at 9:04 PM
this is how I initialized my chart obj.. in case this helps

var chart = (ExcelLineChart)sheetchart.Drawings.AddChart("Z Chart", eChartType.LineMarkers);

so chart.ChartXml is essentially a XmlDocument object that you can do anything with. So any time epplus doesn't do something, you just do in an excel file what you want.. change the extension to .zip open the zip and compare the xml before and after (from another copy) and then you can use the XmlDocument method to basically do anything. And fubar stuff as well....