生成散点图在vb.net

问题描述

我导出表数据到Excel，并尝试生成的分布图吧。

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

在$ C $下生成如下：

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,

但实际的图形看起来是这样的。如果我在Excel中手动生成曲线图

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

我在使用Visual Studio 13和框架3.5

I am using Visual Studio 13 and Framework 3.5

推荐答案

您需要手动创建系列对象，并使用 XValues​​ 和值特性相应。这将替换调用 SetSourceData 。

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

下面是一些粗糙的C＃code，它的工作原理。我不使用VB.NET道歉。关键的一步是使用的SeriesCollection 对象的 NewSeries 的方法来得到一个空的系列将数据添加到。 <一href="https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.seriescollection.newseries.aspx?cs-save-lang=1&cs-lang=vb#$c$c-snippet-1"相对=nofollow>这里是 NewSeries 。

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");

}

结果

这篇关于生成散点图在vb.net的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！