VB中调用Excel生成图表

编写数据库程序的最后步骤一般都是通过查询检索生成各种报表、图形等，在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自动生成的代码很不简洁，自己还需要手工对它进行修改。用同样的方法还可以生成折线图、柱形图等的代码。