WinForms Chart控件Series.Points无法批量设置Excel范围值问题求助

原始代码 #

Private Sub ChartButton1_Click_1(sender As Object, e As EventArgs) Handles ChartButton1.Click
        Dim TnTBooks As Workbooks
        Dim TnTSheets As Sheets
        Dim TnTSheet As Worksheet


        Dim TimeRange As Object

        Dim PriceRange As Object
        TnTBooks = TnT.Workbooks
        TnTSheets = TnT.Worksheets
        TnTSheet = TnTSheets(1)


        TimeRange = TnTSheet.Range("a21").Value

        PriceRange = TnTSheet.Range("b21").Value

        Me.Chart1.Series("Time").Points.AddXY(TimeRange, PriceRange)


    End Sub

上述代码仅能读取工作表中单个数据点（X值为A21，Y值为B21）添加到图表系列。需求是将A21:A2100区域的所有数据作为X值，B21:B2100区域的数据作为Y值，一次性添加2079个数据点到Series。

尝试修改及报错 #

修改了以下两行代码：

TimeRange = TnTSheet.Range("a21:a2100").Value
PriceRange = TnTSheet.Range("b21:b2100").Value

运行后报错：

Error:System.ArguementException: Series data points do not support values of type System.Object[]

报错原因 #

Excel的Range.Value返回的是二维Object数组（即使是单列区域，也会保留行列结构），而Chart的Series.Points.AddXY方法仅支持单个值或一维数组，无法直接识别二维数组，因此抛出类型不支持异常。

可行解决方案 #

方案一：循环遍历单元格逐个添加 #

逻辑直观，适合小批量数据：

Private Sub ChartButton1_Click_1(sender As Object, e As EventArgs) Handles ChartButton1.Click
    Dim TnTSheet As Worksheet = TnT.Worksheets(1)
    Dim timeCells As Range = TnTSheet.Range("A21:A2100")
    Dim priceCells As Range = TnTSheet.Range("B21:B2100")
    
    ' 清空原有数据（可选）
    Me.Chart1.Series("Time").Points.Clear()
    
    ' 遍历每个单元格添加数据点
    For i As Integer = 1 To timeCells.Cells.Count
        Dim xVal As Object = timeCells.Cells(i).Value
        Dim yVal As Object = priceCells.Cells(i).Value
        Me.Chart1.Series("Time").Points.AddXY(xVal, yVal)
    Next
End Sub

方案二：直接绑定Excel Range到Series（高效） #

无需手动遍历，性能更优：

Private Sub ChartButton1_Click_1(sender As Object, e As EventArgs) Handles ChartButton1.Click
    Dim TnTSheet As Worksheet = TnT.Worksheets(1)
    
    ' 清空原有数据
    Me.Chart1.Series("Time").Points.Clear()
    
    ' 直接绑定X、Y轴数据源
    Me.Chart1.Series("Time").XValues = TnTSheet.Range("A21:A2100").Value
    Me.Chart1.Series("Time").YValues = TnTSheet.Range("B21:B2100").Value
End Sub

方案三：将二维数组转为一维数组后添加 #

若直接绑定Range仍有问题，可先转换数组格式：

Private Sub ChartButton1_Click_1(sender As Object, e As EventArgs) Handles ChartButton1.Click
    Dim TnTSheet As Worksheet = TnT.Worksheets(1)
    
    ' 获取Range对应的二维数组
    Dim timeArray As Object(,) = TnTSheet.Range("A21:A2100").Value
    Dim priceArray As Object(,) = TnTSheet.Range("B21:B2100").Value
    
    ' 转换为一维数组
    Dim xValues As New List(Of Object)()
    Dim yValues As New List(Of Object)()
    
    For i As Integer = 1 To timeArray.GetLength(0)
        xValues.Add(timeArray(i, 1))
        yValues.Add(priceArray(i, 1))
    Next
    
    ' 添加到Series
    Me.Chart1.Series("Time").Points.Clear()
    Me.Chart1.Series("Time").XValues = xValues.ToArray()
    Me.Chart1.Series("Time").YValues = yValues.ToArray()
End Sub

内容的提问来源于stack exchange，提问作者christopher martin