VB.NET生成Excel月度设备占用率柱状图空白问题求助

Got it, let's sort out why your machine occupation rate chart is showing up blank and get the X/Y axis set up correctly (using row 2 as X-axis labels and row 3 as Y-axis data). Here's the breakdown of the issue and the fixed code:

What Was Going Wrong #

1. Incorrect Range Selection: Your original range syntax Range("B2:M2", "B3:M3") wasn't reliably selecting the contiguous two-row range Excel needs to parse the data properly.
2. Missing Axis Configuration: By default, Excel treats the first row of your source data as series names instead of X-axis labels. Without explicitly defining the category labels, the chart couldn't render the data correctly.

Fixed VB.NET Code #

Imports Microsoft.Office.Interop.Excel
Imports System.Drawing ' For Color.FromArgb

Private Sub GenerateMachineOccupationChart()
    ' Ensure folhaexcel5 is a valid, initialized Worksheet object
    If folhaexcel5 Is Nothing Then Exit Sub

    ' 1. Select the contiguous range with X labels (row 2) and data (row 3)
    Dim dataRange As Range = folhaexcel5.Range("B2:M3")

    ' 2. Add a column chart and get the Chart object
    Dim chartShape As Shape = folhaexcel5.Shapes.AddChart(XlChartType.xlColumns)
    Dim machineChart As Chart = chartShape.Chart

    ' 3. Set source data, specifying data is organized in rows
    machineChart.SetSourceData(Source:=dataRange, PlotBy:=XlRowCol.xlRows)

    ' 4. Explicitly map row 2 (B2:M2) as X-axis category labels
    machineChart.Axes(XlAxisType.xlCategory).CategoryNames = folhaexcel5.Range("B2:M2")

    ' 5. Configure chart title and value axis settings
    machineChart.HasTitle = True
    machineChart.ChartTitle.Characters.Text = "Occupation Rate of Machines"

    machineChart.Axes(XlAxisType.xlValue).MaximumScale = 100
    machineChart.Axes(XlAxisType.xlValue).HasTitle = True
    machineChart.Axes(XlAxisType.xlValue).AxisTitle.Characters.Text = "Occupation Rate (%)"

    ' 6. Remove legend and position the chart accurately
    machineChart.HasLegend = False
    Dim chartObject As ChartObject = folhaexcel5.ChartObjects(chartShape.Name)
    chartObject.Top = folhaexcel5.Range("P3").Top
    chartObject.Left = folhaexcel5.Range("P3").Left
    chartObject.Height = 300
    chartObject.Width = 1000

    ' 7. Format series colors
    machineChart.SeriesCollection(1).Interior.Color = Color.FromArgb(0, 124, 208)
    machineChart.SeriesCollection(1).Border.Color = Color.FromArgb(0, 55, 92)
End Sub

Key Changes Explained #

• Contiguous Range: Range("B2:M3") selects both rows in a single block, making it easier for Excel to interpret the data structure.
• PlotBy:=xlRows: Tells Excel to read the data row-by-row (instead of column-by-column), which aligns with your desired X/Y axis setup.
• Explicit CategoryNames: This is the critical fix—we directly assign the row 2 values as the X-axis labels, so Excel knows exactly what to display on the horizontal axis.
• Reliable Chart Object Reference: Using chartShape.Name to get the ChartObject avoids issues with index-based selection (which can break if other charts exist on the sheet).

Quick Checks #

• Verify that folhaexcel5 is properly initialized to the correct worksheet in your Excel workbook.
• Ensure you've added the Microsoft.Office.Interop.Excel reference to your VB.NET project (right-click project > Add > Reference > COM > Microsoft Excel xx.x Object Library).

内容的提问来源于stack exchange，提问作者Paula Lopes