Generate scatter graph in vb.net

Question

I am exporting the table data to excel and trying to generate a scatter graph for it.

The code for generating is as follows,

Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
Dim i As Integer
Dim j As Integer
xlApp = New Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")
For i = 0 To DataGridView2.RowCount - 1
   For j = 0 To DataGridView2.ColumnCount - 1
       xlWorkSheet.Cells(i + 1, j + 1) = _
       DataGridView2(j, i).Value.ToString()
   Next
Next
'create chart
Dim chartPage As Excel.Chart
Dim xlCharts As Excel.ChartObjects
Dim myChart As Excel.ChartObject
Dim chartRange As Excel.Range
xlCharts = xlWorkSheet.ChartObjects
myChart = xlCharts.Add(180, 80, 300, 250)
chartPage = myChart.Chart

With chartPage
   chartRange = xlWorkSheet.Range("B2", "C16")
   .SetSourceData(chartRange)
   'set how you want to draw chart i.e column wise or row wise
   .PlotBy = Excel.XlRowCol.xlColumns
   'set data labels for bars
   .ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowNone)
   'set legend to be displayed or not
   .HasLegend = True
   'set legend location
   .Legend.Position = Excel.XlLegendPosition.xlLegendPositionRight
   'select chart type
   .ChartType = Excel.XlChartType.xlXYScatter
   'chart title
   .HasTitle = True
   .ChartTitle.Text = "XY Scatter Chart"
   'set titles for Axis values and categories
   Dim xlAxisCategory, xlAxisValue As Excel.Axes
   xlAxisCategory = CType(chartPage.Axes(,                           
   _Excel.XlAxisGroup.xlPrimary), Excel.Axes)
   xlAxisCategory.Item(Excel.XlAxisType.xlCategory).HasTitle = True
   xlAxisCategory.Item(Excel.XlAxisType.xlCategory)
      .AxisTitle.Characters.Text = "Title 1"
   xlAxisValue = CType(chartPage.Axes(, _Excel.XlAxisGroup.xlPrimary),  
      Excel.Axes)
   xlAxisValue.Item(Excel.XlAxisType.xlValue).HasTitle = True
   xlAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Characters.Text 
      = "Velocity"
End With
xlWorkSheet.SaveAs("C:\vbexcel.xlsx")
xlWorkBook.Close()
xlApp.Quit()

releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)

and the output after generating it I get like below,

but the actual graph appears to be like this If I generate graph manually in the excel

I am using Visual Studio 13 and Framework 3.5

Answer 1

You need to manually create the Series object and supply it with XValues and Values properties accordingly. This replaces the call to SetSourceData.

Here is some rough C# code that works. I apologize for not using VB.NET. The key step is to use the NewSeries method on the SeriesCollection object to get an empty series to add data to. Here is the MS support for NewSeries.

private void CreateChartAddSeries()
{
    Microsoft.Office.Interop.Excel.Application xl_app = new Microsoft.Office.Interop.Excel.Application();
    xl_app.Visible = true;

    Workbook wkbk = xl_app.Workbooks.Add();
    Worksheet wksht = wkbk.Worksheets[1];

    wksht.get_Range("A1:A10").Formula = "=RAND()";
    wksht.get_Range("B1:B10").Formula = "=RAND()";

    ChartObjects cht_objs = wksht.ChartObjects();
    ChartObject cht_obj = cht_objs.Add(100, 0, 300, 300);

    Chart cht = cht_obj.Chart;
    cht.ChartType = XlChartType.xlXYScatter;

    SeriesCollection sc = cht.SeriesCollection();

    Series ser = sc.NewSeries();
    ser.Values = wksht.get_Range("B1:B10");
    ser.XValues = wksht.get_Range("A1:A10");

}

Results