VB中调用Excel2003生成Chart图表程序代码.doc

VB中调用Excel2003生成Chart图表程序代码
   在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自动生成的代码，自己还需要手工对它进行修改。用同样的方法还可以生成折线图、柱形图等的代码。