c# and excel: chart ranges

[everphilski]

long time listener, first time caller...

I'm trying to generate Excel charts using c#. I'm using Excel.Range to set a range (series of values) to plot.

However, I'd like to plot two series that are not next to each other. For example, B1:B10 and G1:G10 versus A1:A10 on the x-axis. If I try to do this using Excel.Range.get_range(), it returns all series between B and G. Is there another method to just use the series I specify?

Thanks,

Phil

[kevin.cochrane]

Hi Phil,

As far as I understand it, and I'm a noob at this but I have some working programs to do similar things, if you are leaving the () blank at the end of the get_range then you will call the entire sheet and load that into the range. You can specify by saying instead: Excel.Range range = oSheet.get_Range("C1", "C10");

And if you wanted a second range you would repeat but change the range variable eg. range2.

For the purposes of your code I would do something like this:

Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)Chart.SeriesCollection(Missing.Value);
Excel.Series series1 = seriesCollection.NewSeries();                                                            
Excel.Series series2 = seriesCollection.NewSeries();
series1.Values = oSheet.get_Range("C2", "C10");                                          
series2.Values = oSheet.get_Range("D2", "D10" );

I've never done more than one series on a graph but those are the basic I think you will need.

I hope this helps.

Kevin

long time listener, first time caller...

I'm trying to generate Excel charts using c#. I'm using Excel.Range to set a range (series of values) to plot.

However, I'd like to plot two series that are not next to each other. For example, B1:B10 and G1:G10 versus A1:A10 on the x-axis. If I try to do this using Excel.Range.get_range(), it returns all series between B and G. Is there another method to just use the series I specify?

Thanks,

Phil

[lokendra jain]

Hi kevin.cochrane,
I am getting error "HRESULT 0x80040154 " when creating charts using c#.net,
I am passing the range to chartrange , when i am passing small range("B33:D33,B37:D37,B43:D43,B47:D47") it is working fine.
But when passing a large range it generating error("B33:D33,B37:D37,B43:D43,B47:D47,B53:D53,B55:D55,B57:D57,B59:D59,B61:D61,B63:D63,B65:D65,B69:D69" ) .
My Code as follows:-

private void ClusteredChartChannelGroup(DataTable tblGroupAvg, int startindex, double topIndex, double cellHeight, double avgCellHeight)
        {
            double top = cellHeight + avgCellHeight + topIndex + 18;
            double left = 20;
            double width = 850;
            double height = 600;

            DataTable dtChannel = new DataTable();
            dtChannel = tblGroupAvg.DefaultView.ToTable(true, "Category");
            DataTable dtGroupAvgerage = new DataTable("GroupAverage");
            dtGroupAvgerage.Columns.Add("Channel", typeof(string));
            dtGroupAvgerage.Columns.Add("IAverage", typeof(decimal));
            dtGroupAvgerage.Columns.Add("KAverage", typeof(decimal));
            int minrange = startindex - 1;
            int maxrange = startindex - 1;
            string data = "B"+minrange+":D"+maxrange;

            for (int cIndex = 0; cIndex < dtChannel.Rows.Count; cIndex++)
            {
                DataView dvAvg = new DataView(tblGroupAvg);
                dvAvg.RowFilter = "Category='" + dtChannel.Rows[cIndex]["Category"].ToString() + "'";
                maxrange = minrange + dvAvg.Count + 1;
                DataRow dr = dtGroupAvgerage.NewRow();
                dr["Channel"] = dtChannel.Rows[cIndex]["Category"].ToString();
                dr["IAverage"] = Convert.ToDecimal(dvAvg.ToTable().Compute("avg(IAverage)", ""));
                dr["KAverage"] = Convert.ToDecimal(dvAvg.ToTable().Compute("avg(KAverage)", ""));
                dtGroupAvgerage.Rows.Add(dr);
                dtGroupAvgerage.AcceptChanges();
                data += "," + "B" + (maxrange) + ":D" + (maxrange);
                minrange = maxrange;
            }
            Excel.Range chartRange;
            Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
            Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(left, top, width, height);
            Excel.Chart chartPage = myChart.Chart;
            chartRange = xlWorkSheet.get_Range(data,misValue);
            //chartRange = xlWorkSheet.get_Range(data, misValue);
            chartPage.SetSourceData(chartRange, misValue);
            chartPage.HasLegend = true;
            chartPage.HasTitle = true;
            chartPage.ChartTitle.Text = "Group average by channel";
            chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
        }

Please let me know cause of the error and resolution to this
error .

Thanks
Lokendra Jain