editing excel chart data range by c#

I'm trying to create an excel scatter chart using Microsoft.Office.Interop.Excel. I have 2 data ranges

 Range chartRange1 = xlWorkSheet.Range["A1", "B5"];
 Range chartRange2 = xlWorkSheet.Range["A6", "B10"];

for 2 sets of points. "A" column specifies x coordinates and "B" column specifies y coordinates. When I create chart this way:

        chartPage.ChartType = Excel.XlChartType.xlXYScatter;

        var seriesCol = chartPage.SeriesCollecti开发者_运维技巧on(misValue);

        seriesCol.add(chartRange1, misValue, misValue, misValue, misValue);
        seriesCol.add(chartRange2, misValue, misValue, misValue, misValue);

I get 4 sets of points with x coordinate from 1 to 5 and y coordinate specified by ranges A1 - A5, B1 - B5, A6 - A10, B6 - B10.

How can I get the chart contains 2 sets of points with x coordinates defined at "A" column and y coordinates defined at "B"?

Thanks!

You'll have to translate this from VBA into your own syntax.

The Add method of the SeriesCollection object is as follows:

Function Add(Source, [Rowcol As XlRowCol = xlColumns], 
[SeriesLabels], [CategoryLabels], [Replace]) As Series

You only want to add one series, not two as you've done. So you need to define a range based on the union of your two ranges. In VBA:

Set ChartRange = Union(ChartRange1, ChartRange2)

Then you can probably use (in your syntax)

seriesCol.add(chartRange, misValue, misValue, True, misValue);

where I've used True for the value of CategoryLabels to try to force Excel to use the first column as X values.

Sometimes in VBA with a fresh chart, it doesn't work exactly right. In this case, you need to add the series using the Y values, then apply the X values. The first line is your syntax, the second is pure VBA.

seriesCol.add(chartRange2, misValue, misValue, misValue, misValue);

seriesCol(1).XValues = ChartRange1