none
Excel chart throws error with single column of data RRS feed

  • Question

  • Hi,

       I am using C# and VSTO to write a bunch of data to Excel. Most of it works fine, but I am getting an error when I try to add a chart, but only when the chart contains only a single column of data with chart type XLChartType.xlLine.

    Below is the code which adds the chart. It works fine for all conditions I have tried (not too many) except when sourceData contains a single column of data & chartType is xlLine. Under those conditions the line
                     chartSeriesCollection.Add(sourceData, XlRowCol.xlColumns, true, true);
    throws an error:
               An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in mscorlib.dll
               Additional information: Exception from HRESULT: 0x800A03EC

    Any clue what's happening?

    Thanks!

    Ethan

           public static ChartObject MakeChart(
            Range sourceData,
            Range errorBarData,
            ExcelCell? cellInWhichToPositionChart,
            XlChartType chartType,
            string chartTitle,
                string xAxisLabelText,
                string yAxisLabelText,
                bool showLegend)
            {
                Worksheet sheet = sourceData.Worksheet;
                double left;
                double width = 600;
                double top;
                double height = 200;
                if (cellInWhichToPositionChart.HasValue)
                {
                    Range cellAsRange = cellInWhichToPositionChart.Value.AsRange(sheet);
                    left = (double)cellAsRange.Left;
                    top = (double)cellAsRange.Top;
                }
                else
                {
                    left = (double)sourceData.Left + sourceData.Width;
                    top = (double)sourceData.Top + sourceData.Height;
                    if (errorBarData != null)
                    {
                        left = Math.Max(left, (double)errorBarData.Left + errorBarData.Width);
                        left = Math.Max(top, (double)errorBarData.Top + errorBarData.Height);
                    }
                }
                ChartObject ch = ((ChartObjects)sheet.ChartObjects()).Add(left, top, width, height);
                ch.Chart.ChartType = chartType;
                ch.Chart.HasTitle = true;
                ch.Chart.ChartTitle.Caption = chartTitle;
                SeriesCollection chartSeriesCollection = (SeriesCollection)ch.Chart.SeriesCollection();
                chartSeriesCollection.Add(sourceData, XlRowCol.xlColumns, true, true);
                if (errorBarData != null)
                {
                    for (int seriesIndex = 1; seriesIndex <= ((SeriesCollection)ch.Chart.SeriesCollection()).Count; seriesIndex++)
                    {
                        ExcelCell firstCellInColumn = new ExcelCell(errorBarData.Row, errorBarData.Column + seriesIndex - 1);
                        ExcelCell lastCellInColumn = firstCellInColumn.Shift(errorBarData.Rows.Count, 0);
                        Range seriesErrorBarRange = sheet.get_Range(firstCellInColumn.cellAddress, lastCellInColumn.cellAddress);
                        ((Series)ch.Chart.SeriesCollection(seriesIndex)).ErrorBar(XlErrorBarDirection.xlY, XlErrorBarInclude.xlErrorBarIncludeBoth, XlErrorBarType.xlErrorBarTypeCustom, seriesErrorBarRange, seriesErrorBarRange);
                    }
                }
                if (!showLegend)
                {
                    ch.Chart.Legend.Clear();
                }
                if (yAxisLabelText != null)//Note: I do want to allow people to set empty string labels
                {
                    Axis yAxis = (Axis)ch.Chart.Axes(XlAxisType.xlValue);
                    yAxis.HasTitle = true;
                    yAxis.AxisTitle.Text = yAxisLabelText;
                } 
                if (xAxisLabelText != null)//Note: I do want to allow people to set empty string labels
                {
                    Axis xAxis = (Axis)ch.Chart.Axes(XlAxisType.xlCategory);
                    xAxis.HasTitle = true;
                    xAxis.AxisTitle.Text = xAxisLabelText;
                }
                return  ch;
            }


    Ethan Strauss

    Monday, May 16, 2016 5:08 PM

Answers

  • Hi Ethan Strauss,

    it looks like line chart doesn't support that.

    CategoryLabels :

    True if the first row or column contains the name of the category labels. False if the first row or column contains the first data point of the series.

    If this argument is omitted, Microsoft Excel attempts to determine the location of the category label from the contents of the first row or column.

    here you have omit this argument. this work around can work for you.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, May 18, 2016 7:19 AM
    Moderator

All replies

  • Hi Ethan Strauss,

    First I would recommend to you that try to create a line chart with simple code instead of using such a long code.

    and check whether it gives you an error or not.

    if it created successfully after then try to modify the code above on the bases of that.

    here you have provided the function but did not provided the objects used inside the functions.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, May 17, 2016 8:04 AM
    Moderator
  • Thanks Deepak.

      The problem was that Excel apparently does not want to add CategoryLabels where there is only one category.

    changing this line

    chartSeriesCollection.Add(sourceData, XlRowCol.xlColumns, true, true);

    to this

    chartSeriesCollection.Add(sourceData, XlRowCol.xlColumns, true);

    Took care of the issue.

    Thanks!


    Ethan Strauss

    Tuesday, May 17, 2016 4:37 PM
  • Hi Ethan Strauss,

    it looks like line chart doesn't support that.

    CategoryLabels :

    True if the first row or column contains the name of the category labels. False if the first row or column contains the first data point of the series.

    If this argument is omitted, Microsoft Excel attempts to determine the location of the category label from the contents of the first row or column.

    here you have omit this argument. this work around can work for you.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, May 18, 2016 7:19 AM
    Moderator