vb操作excel作图

vb操作excel作图 Private Sub Command1_Click() Dim xlapp As Object 'New Excel.Application Dim xlBook As Object 'New Excel.Workbook Dim xlSheet As Object 'New Excel.Worksheet Set xlapp = CreateObject("Excel.Application") xlapp.Workbooks.Add xlapp.Visible = flase xlapp.Cells(1, 1) = "shiduan" xlapp.Cells(1, 2) = "datetime" xlapp.Cells(1, 3) = "x" xlapp.Cells(1, 4) = "y" xlapp.Cells(1, 5) = "x_t" xlapp.Cells(1, 6) = "y_t" xlapp.ActiveWorkbook.SaveAs App.Path + "\data.xls" xlapp.Workbooks.Close xlapp.Quit Set xlapp = Nothing End Sub Private Sub Command2_Click() Dim count As Integer Dim count_b As Integer Set xlapp = CreateObject("Excel.Application") xlapp.Visible = True On Error Resume Next Set xlBook = xlapp.Workbooks.Open(App.Path + "\data.xls") Set xlSheet = xlBook.Worksheets(1) xlSheet.Application.WindowState = xlMaximized xlSheet.Activate count = 11 xlSheet.Range("C2").Select xlSheet.Cells(2, 3).FormulaR1C1 = "=VALUE(RC[+2])" xlSheet.Range("C2").AutoFill Destination:=xlSheet.Range("C2:C" & 11), Type:=xlFillDefault xlSheet.Range("D2").Select xlSheet.Cells(2, 4).FormulaR1C1 = "=VALUE(RC[+2])" xlSheet.Range("D2").AutoFill Destination:=xlSheet.Range("D2:D" & 11), Type:=xlFillDefault count_b = 2 xlSheet.Range("C2 :D11").Select '选取区域 xlapp.Charts.Add xlapp.ActiveChart.SeriesCollection(2).Delete xlapp.ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R" & count_b & "C3:R" & count & "C3" 'X区域 xlapp.ActiveChart.SeriesCollection(1).Values = "=Sheet1!R" & count_b & "C4:R" & count & "C4" 'Y区域 xlapp.ActiveChart.ChartType = xlXYScatter '设置散点 xlapp.ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("C" & count_b & ":D" & count) '选取区域 xlapp.ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1" With xlapp.ActiveChart.Axes(xlCategory) 'X轴 .MinimumScale = 0 '设置最小坐标 End With With xlapp.ActiveChart.Axes(xlValue) 'Y轴 .MinimumScale = 0 'Int(min_f(a) / 100) * 1000 + 50 '设置最小坐标 .CrossesAt = 0 'Int(min_f(a) / 100) * 1000 + 50 End With xlapp.ActiveChart.SeriesCollection(1).Select With Selection .MarkerStyle = xlMarkerStyleDiamond End With xlapp.ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlPolynomial, Order:=3, Forward:=0, Backward:=0, DisplayEquation:=True, DisplayRSquared:=False).Select ' ' xlapp.ActiveChart.ChartArea.Select With xlapp.ActiveChart '设置标题和坐标名称 .HasTitle = True .ChartTitle.Characters.Text = "负荷气象分布图" '标题 .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "实感指数" 'X 轴 .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "最大负荷(MW)" 'Y轴 End With ' xlapp.ActiveChart.HasLegend = True '图 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 图例 ' xlapp.ActiveChart.Legend.Select xlapp.ActiveChart.Legend.Position = xlTop '图例位置 ' xlapp.ActiveChart.ChartArea.Select ' xlapp.ActiveChart.PlotArea.Select xlapp.ActiveChart.PlotArea.ClearFormats ' xlapp.ActiveChart.Axes(xlValue).MajorGridlines.Select With xlapp.ActiveChart.Axes(xlValue).MajorGridlines.Border '虚线 .ColorIndex = 57 .Weight = xlHairline .LineStyle = xlDot End With '' xlapp.ActiveChart.ChartArea.Select ' xlapp.ActiveChart.SeriesCollection(1).Trendlines(1).Select '趋势线大小,颜色 With xlapp.ActiveChart.SeriesCollection(1).Trendlines(1).Border ' .ColorIndex = curv_color '3 .Weight = xlThick 'xlThick .LineStyle = xlGray75 End With With xlapp.ActiveChart.SeriesCollection(1).Trendlines(1) .Name = "回归负荷" '曲线名称 End With ' xlapp.ActiveChart.ChartArea.Select xlapp.ActiveChart.SeriesCollection(1).Name = "=""实际负荷""" '散点名称 xlapp.ActiveChart.PlotArea.Select '调整区域大小，先选中区域 xlapp.Selection.Left = 15 xlapp.Selection.Top = 55 xlapp.Selection.Height = 170 xlapp.Selection.Width = 343 xlapp.ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select '回归函数位置 xlapp.Selection.Left = 50 xlapp.Selection.Top = 35 ' xlapp.Selection.AutoScaleFont = True xlapp.ActiveChart.Legend.Select '标示调整 xlapp.Selection.Left = 99 xlapp.Selection.Top = 18 xlapp.ActiveChart.ChartTitle.Select '标题 xlapp.Selection.Top = 0 xlapp.ActiveSheet.Shapes("图表 1").ScaleHeight 0.8, msofalse, msoScaleFromTopLeft ' xlapp.ActiveChart.ChartArea.Select ' xlapp.Selection.AutoScaleFont = False xlapp.ActiveChart.Export App.Path + "\" + "SHIL" + ".jpg", "jpg" '导出图片 '' ''' xlapp.ActiveChart.Export App.Path + "\R\" + "SHILI" + ".jpg", "jpg" xlBook.Save xlBook.Close xlapp.Quit Set xlapp = Nothing Set xlBook = Nothing Set xlSheet = Nothing DoEvents End Sub