VB中调用Excel生成图表

软件世界

wzwtz

编写数据库程序的最后步骤一般都是通过查询检索生成各种报表、图形等，在VB中通过调用Excel的图表制作功能可以生成各种复杂的图表，使编程过程得以简化。举例如下：新建工程，在Form1窗口添加Command1按钮，编写程序在Excel中添加数据并生成饼图。
Private Sub Command1_Click()
Dim x1 As Excel.Application '声明数据类型
Set x1 = CreateObject("Excel.Application")
'创建实例
x1.Workbooks.Add
'添加工作簿
x1.Visible = True
x1.Range("A1").Value = 1 'A1格赋值
x1.Range("B1").Value = 2 'B1格赋值
x1.Range("C1").Value = 3 'C1格赋值
x1.Range("D1").Value = 4 'D1格赋值
x1.Range("A1", "D1").Borders.LineStyle = xlContinuous '单元格边框
x1.ActiveSheet.Rows.HorizontalAlignment = xlVAlignCenter
x1.ActiveSheet.Rows.VerticalAlignment = xlVAlignCenter '上下、左右居中
Set ct = x1.Worksheets("sheet1").ChartObjects.Add(10, 40, 220, 120) '插入图形
ct.Chart.ChartType = xl3DPie '图形类型为饼图
ct.Chart.SetSourceData Source:=Sheets("Sheet1").Range("A1:D1"), PlotBy:=xlRows '图形数据来源
With ct.Chart
.HasTitle = True
.ChartTitle.Characters.Text = "饼图" '图表标题为饼图
End With
ct.Chart.ApplyDataLabels 2, True '标志旁附图例项标志
Set x1 = Nothing
End Sub
有人可能会觉得程序里对象、属性太多，自己记不住。有一个可以偷懒的方法。打开Excel，点击工具→宏→录制宏，按照步骤添加数据，设置格式，生成饼图，然后停止录制。这时点击工具→宏，再点击编辑，就会出现刚才所做事情的代码，将代码复制到VB中稍做改动就可以了。
ActiveCell.FormulaR1C1 = "1"
Range("B1").Select
ActiveCell.FormulaR1C1 = "2"
Range("C1").Select
ActiveCell.FormulaR1C1 = "3"
Range("D1").Select
ActiveCell.FormulaR1C1 = "4"
Range("A1：D1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("A3").Select
Charts.Add
ActiveChart.ChartType = xl3DPie
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:D1"), PlotBy:= _
xlRows
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "饼图"
End With
ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowValue, LegendKey：=True, _
HasLeaderLines:=True
可以看出，Excel自动生成的代码很不简洁，自己还需要手工对它进行修改。用同样的方法还可以生成折线图、柱形图等的代码。