VB中调用Excel生成图表(精品文档)

VB中调用Excel生成图表

添加时间：2004-9-3 查看：298次

编写数据库程序的最后步骤一般都是通过查询检索生成各种报表、图形等，在VB中通过调用Exc el的图表制作功能可以生成各种复杂的图表，使编程过程得以简化。举例如下：新建工程，在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:=xl Rows '图形数据来源

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自动生成的代码很不简洁，自己还需要手工对它进行修改。用同样的方法还可以生成折线图、柱形图等的代码。