在vb.net中生成散点图 [英] Generate scatter graph in vb.net

问题描述

生成代码如下，

  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）
对于i = 0到DataGridView2.RowCount  -  1 
对于j = 0到DataGridView2.ColumnCount  -  1 
 xlWorkSheet.Cells（ i + 1，j + 1）= _ 
 DataGridView2（j，i）.Value.ToString（）
下一个
下一个
'创建图表
 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。添加（180，80，300，250）
 chartPage = myChart.Chart 
 
带有chartPage 
 chartRange = xlWorkSheet.Range（B2，C16）
 .SetSourceData（chartRange）
'设置你的想要绘制图表，即列智能或行智能
 .PlotBy = Excel.XlRowCol.xlColumns 
'设置条的数据标签
 .ApplyDataLabels（Excel.XlDataLabelsType.xlDataLabelsShowNone）
'设置要显示的图例
 .HasLegend = True 
'设置图例位置
 .Legend.Position = Excel.XlLegendPosition.xlLegendPositionRight 
'选择图表类型
。 ChartType = Excel.XlChartType.xlXYScatter 
'图表标题
 .HasTitle = True 
 .ChartTitle.Text =XY散点图
'设置Axis值和类别的标题
 Dim xlAxisCategory，xlAxisValue As Excel.Axes 
 xlAxisCategory = CType（chartPage.Axes（，
 _Excel.XlAxisGroup.xlPrimary），Excel.Axes）
 xlAxisCategory.Item（Excel.XlAxisType。 xlCategory）.HasT itle = True 
 xlAxisCategory.Item（Excel.XlAxisType.xlCategory）
 .AxisTitle.Characters.Text =标题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
结束
 xlWorkSheet.SaveAs（C：\vbexcel.xlsx）
 xlWorkBook.Close（）
 xlApp.Quit（）
 
 releaseObject（xlApp）
 releaseObject（xlWorkBook）
 releaseObject（xlWorkSheet）
  

，生成后的输出就像下面这样，

但是实际图形似乎是这样的如果我在excel中手动生成图表

我正在使用Visual Studio 13和Framework 3.5

解决方案

您需要手动创建系列对象，并提供 XValues 和值属性相应。这将替换为 SetSourceData 的调用。

这里是一些粗糙的C＃代码。对于不使用VB.NET，我深表歉意。关键步骤是在 SeriesCollection 对象上使用 NewSeries 方法获取一个空的序列来添加数据。 以下是 NewSeries 的MS支持。

  private void CreateChartAddSeries（）
 {
 Microsoft.Office.Interop.Excel.Application xl_app = new Microsoft.Office.Interop.Excel.Application（）; 
 xl_app.Visible = true; 
 
工作簿wkbk = xl_app.Workbooks.Add（）; 
工作表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）; 
 
图表cht = cht_obj.Chart; 
 cht.ChartType = XlChartType.xlXYScatter; 
 
 SeriesCollection sc = cht.SeriesCollection（）; 
 
系列ser = sc.NewSeries（）; 
 ser.Values = wksht.get_Range（B1：B10）; 
 ser.XValues = wksht.get_Range（A1：A10）; 
 
} 
  

结果

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

解决方案

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

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