我正在编写 vb 脚本来生成图表。在 X 轴上有日期,在 Y 轴上有温度。
在 X 轴上,我想以“dd-mm”格式显示时间。我的数据如下所示:
2014-06-17 01:00
2014-06-17 02:00
2014-06-17 03:00
2014-06-17 04:00
2014-06-17 05:00
2014-06-17 06:00
2014-06-17 07:00
2014-06-17 08:00
2014-06-17 09:00
这是我到目前为止用 vb 脚本编写的内容:
With chtChart.Chart
.HasTitle = True
.ChartTitle.Text = sheetName & vbCr & "2014"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
.Axes(xlCategory, xlPrimary).CategoryType = xlTimeScale
.Axes(xlCategory, xlPrimary).MinimumScaleIsAuto = True
.Axes(xlCategory, xlPrimary).MaximumScaleIsAuto = True
.Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "dd-mm"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Temperature [C]"
End With
不幸的是,当我生成图表时,整个日期值(例如 2014-06-07 01:00)将应用于 X 轴。
关于如何解决我所拥有的问题有什么想法/想法吗?
Update:
创建图表的完整代码:
Function AddChartSheet(sheetName As String, title As String) As Boolean
Dim ws As Worksheet
Dim chtChart As ChartObject
Dim measDataSheetName As String
'Create a new chart.
measDataSheetName = sheetName & "_measurements.csv"
Dim Lastrow As Integer
Dim seriesNames() As String
ActiveWorkbook.Sheets.Add.name = sheetName & " chart"
Set ws = ActiveWorkbook.Sheets(sheetName & " chart")
Set chtChart = ActiveSheet.ChartObjects.Add(Left:=25, Top:=25, _
Width:=700, Height:=500)
With chtChart
.name = sheetName
End With
Lastrow = ActiveWorkbook.Sheets(measDataSheetName).Cells(ActiveWorkbook.Sheets(measDataSheetName).Rows.Count, "P").End(xlUp).Ro
With chtChart.Chart
.HasTitle = True
.ChartTitle.Text = sheetName & vbCr & "2014"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
.Axes(xlCategory, xlPrimary).CategoryType = xlTimeScale
.Axes(xlCategory, xlPrimary).MinimumScaleIsAuto = True
.Axes(xlCategory, xlPrimary).MaximumScaleIsAuto = True
.Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "dd-mm"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Temperature [C]"
End With
With chtChart.Chart.SeriesCollection.NewSeries
.name = "Supply"
.ChartType = xlXYScatterSmoothNoMarkers
.XValues = Worksheets(measDataSheetName).Range("P2:P" & Lastrow) '. SelectRange("C3", Range("C3").End(xlDown))
.Values = Worksheets(measDataSheetName).Range("T2:T" & Lastrow)
End With
With chtChart.Chart.SeriesCollection.NewSeries
.name = "Return"
.ChartType = xlXYScatterSmoothNoMarkers
.XValues = Worksheets(measDataSheetName).Range("P2:P" & Lastrow) '. SelectRange("C3", Range("C3").End(xlDown))
.Values = Worksheets(measDataSheetName).Range("U2:U" & Lastrow)
End With
AddChartSheet = True
End Function