Excel VBA高级图表绘制: 曲面图

三维曲面是常见的数值型图表。用Excel绘制曲面图，需要提供规则网格数据。使用Excel图形引擎可以绘制曲面图，可以对曲面图进行着色、设置透明性和添加光照。[大谦Excel，dqexcel点com]

曲面+三维填充等值线

Excel可以直接绘制曲面图，它要求绘图数据按照指定的格式给出。如图4-34所示，在工作表中输入数据或打开数据文件。首先将行方向和列方向的数据按照从小到大的顺序排序，然后等间隔分割成很多小的区间，将间隔值显示在第1行和第1列。图4-34中根据行数据和列数据等间隔取了100个数，以它们为横坐标和纵坐标构造一个网格，在每个节点输入数据，该数据可能是横坐标和纵坐标的函数值。

\[\]

图4-34 绘制曲面图和等值线图的数据

下面的代码利用给定数据绘制曲面图叠加颜色填充的三维等值线图。完整代码见：Samples->ch07 数值型图表->25 曲面图->py.py。

code.vba

Sub CreateCustomSphere()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets.Add
    Dim numX As Integer, numY As Integer
    Dim XValues() As Variant, YValues() As Variant, ZValues() As Variant
    Dim i As Integer, j As Integer
    numX = 100
    numY = 100
    ReDim XValues(1 To 1, 1 To numX)
    ReDim YValues(1 To numY, 1 To 1)
    ReDim ZValues(1 To numX, 1 To numY)
    For i = 1 To numX
        XValues(1, i) = i
        For j = 1 To numY
            YValues(j, 1) = j
            ZValues(i, j) = i * i + j * j - i * j + 2 * i + 2 * j - 1
        Next j
    Next i
    For j = 1 To numX
        ws.Cells(1, j + 1).Value = XValues(1, j)
    Next j
    For i = 1 To numY
        ws.Cells(i + 1, 1).Value = YValues(i, 1)
    Next i
    For i = 1 To numX
        For j = 1 To numY
            ws.Cells(i + 1, j + 1).Value = ZValues(i, j)
        Next j
    Next i
    Dim chartObj As ChartObject
    Set chartObj = ws.ChartObjects.Add(Left:=100, Top:=50, Width:=500, Height:=400)
    With chartObj.Chart
        .SetSourceData Source:=ws.Range(ws.Cells(2, 2), ws.Cells(numX + 1, numY + 1))
        .ChartType = xlSurface
        .HasTitle = True
        .ChartTitle.Text = "Custom Sphere"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Text = "X Axis"
        .Axes(xlSeriesAxis, xlPrimary).HasTitle = True
        .Axes(xlSeriesAxis, xlPrimary).AxisTitle.Text = "Y Axis"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Text = "Z Axis"
    End With
End Sub