none
Excel 2013 chart not displying correctly RRS feed

  • Question

  • I'm producing a chart in excel 2013 that requires a box to be displayed over a range of values instead of starting from 0. It requires forming a stacked column plot and then hiding the first column. The following code should produce a chart that displays a bar over the range 20 to 35.

     private void TestPlot()
            {
                worksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
                Excel.ChartObjects charts = (Excel.ChartObjects)worksheet.ChartObjects(Type.Missing);
                Excel.ChartObject chart1 = charts.Add(1, 1, 300, 300);
                chart1.Chart.ChartType = Excel.XlChartType.xlColumnStacked;
                chart1.Name = "Chart1";
                chart1.Chart.HasLegend = false;
                chart1.Chart.HasTitle = true;
                chart1.Chart.ChartTitle.Text = "Box Plot";
                Excel.SeriesCollection myCollection = chart1.Chart.SeriesCollection();
                Excel.Series first = myCollection.NewSeries();
                first.XValues = 1;
                first.Values = 20;
                first.Format.Fill.Visible = Office.MsoTriState.msoFalse;
                Excel.Series second = myCollection.NewSeries();
                second.XValues = 1;
                second.Values = 15;
            }

    This works fine in excel 2007 and 2010 but I'm now working in 2013 and the chart produced is blank(although all the data is there)  until the 'select data' option is selected from the chart context menu which then shows the chart correctly, as shown in following picture.

    If fill.visible of the first series isn't set to false then the 2 columns display, so seems setting one series effects the other although setting fill.visible to true for the second series doesn't make any difference. I don't know if I missing something obvious but would be grateful if anyone could offer any help on how to get the chart to display correctly.

    I'm using visual studio 2012 with update 1 on windows 8 64 bit with office professional plus 2013

    Many Thanks

    George


    George
    http://www.sharpstatistics.co.uk/blog

    Monday, February 4, 2013 8:51 PM

Answers

  •  

    I had the same issue, and I resolved it using seriesCollection.Add(...) method. Thought I should share it over here for others to reference.

    You can simply replace myCollection.NewSeries() with myCollection.Add(20), omit setting Values later, and get the results you want.

    Reference that helped me:

    http://msdn.microsoft.com/en-us/library/office/ff194065.aspx

    • Proposed as answer by Maxo666 Wednesday, March 20, 2013 6:13 PM
    • Marked as answer by SharpStatistics Sunday, March 24, 2013 2:22 PM
    Wednesday, March 20, 2013 6:07 PM

All replies

  • Hi George,

    Thanks for posting in the MSDN Forum.

    I can reproduce your issue on my side. I will involve some experts into this issue to see whether they can help you out. There might be some time delay, appreciate for your patience.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, February 5, 2013 2:35 AM
    Moderator
  • Hi Tom,

    Thanks for your help. It isn't time sensitive so no problem if it takes a while.

    Thanks

    George


    George
    http://www.sharpstatistics.co.uk/blog

    Tuesday, February 5, 2013 7:29 AM
  • Hi George,

    I have discussed it with my colleagues about this issue. We will contact Microsoft via internal channel, I think Microsoft must pay attention at this issue.

    Thank you for submit it in the Forum.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, February 6, 2013 4:48 AM
    Moderator
  •  

    I had the same issue, and I resolved it using seriesCollection.Add(...) method. Thought I should share it over here for others to reference.

    You can simply replace myCollection.NewSeries() with myCollection.Add(20), omit setting Values later, and get the results you want.

    Reference that helped me:

    http://msdn.microsoft.com/en-us/library/office/ff194065.aspx

    • Proposed as answer by Maxo666 Wednesday, March 20, 2013 6:13 PM
    • Marked as answer by SharpStatistics Sunday, March 24, 2013 2:22 PM
    Wednesday, March 20, 2013 6:07 PM
  • Hi Maxo47,

    Thanks for your help, but if I replace NewSeries() with Add(20) I get the error 'Parameter is incorrect'.


    George
    http://www.sharpstatistics.co.uk

    Wednesday, March 20, 2013 8:34 PM
  • Hey Sharp,

    Well, in my case I pass Excel.Range, so I'm not sure how to get it work in your case. Also, note that the Add method doesn't work in Excel 2003, so that's a bummer, too.

    Try passing valid range (I think format is something like "c1:c5", and keep Series.Values = 20 after, maybe it will work then.
    Thursday, March 21, 2013 3:30 PM
  • I'm passing in data from an array as it is the result of a calculation so I don't think this helps me. I haven't the time at the moment to look at this so, I'll have another go at the weekend, and let you know if I get anywhere.

    Thanks


    George
    http://www.sharpstatistics.co.uk

    Thursday, March 21, 2013 8:00 PM
  • Hi Maxo47,

    You are right using the collection.add() method does get round the problem. In my case as I'm using results from a calculation so it means messing around with ranges which I could do without. Thanks for your help.

    Any word from the Microsoft guys on why my original method doesn't work?


    George
    http://www.sharpstatistics.co.uk

    Sunday, March 24, 2013 2:27 PM
  • Glad that helped you too.

    I haven't found anything about this problem anywhere else, but I learned that these charts can really be painful to work with. I even had a problem caused by order of setting properties, which is really awful.

    Monday, March 25, 2013 11:10 AM
  • Hello,

    Any update about this bug? I don't want to use myCollection.Add as a workaround for this issue. Are there any other workarounds? When is this bug going to be fixed?


    Tatyana Yakushev [PredixionSoftware.com]

    Download Predixion Insight 3.0 - World class predictive platform for big data

    Thursday, June 6, 2013 6:18 PM
  • Any update on this? We are very advanced in development where we targeted Excel 2010 and we found this issue when testing 2013. If is actually a big deal for us.
    Sunday, March 9, 2014 5:18 AM
  • 3 years later and I am seeing this same bug in both Excel 2013 and 2016.  Charts work fine for Excel 2010 but do not show all series in later versions.  You can manually try to Select Data which is not desirable or save and close the workbook.  Opening it again shows all the series.  

    Has this been resolved?  I can't use the Add() method as I am not dealing with data on the sheet.

    Friday, May 12, 2017 8:59 PM