本人想做个统计功能 用CHART控件 以曲线图的形式体现 目的是统计每个时间段的生产量我用的是SQL SERVER数据库 ODBC数据源 库里信息每条为一个产品的信息 我需要在程序里先统计出每天的生产量填充到Y轴 X轴为生产日期 努力了很长时间没有做出来 代码如下望老大们指教 高高分回赠
Option Explicit
Dim rs As New Recordset
Private Values()
Dim ss As New Recordset
Private NumPoints As IntegerPrivate Sub LoadData()Dim dbname As String
Dim i As Integer If rs.State = adStateOpen Then rs.Close
rs.Open "select * from q", "dsn=breed", adOpenKeyset, adLockOptimistic
rs.MoveLast
NumPoints = rs.RecordCount
ReDim Values(1 To NumPoints, 1 To 2) ' Load the data.
rs.MoveFirst
For i = 1 To NumPoints
Values(i, 1) = Format$(rs!aaa, "yyyy")
If ss.State = adStateOpen Then ss.Close
ss.Open "select * from q where aaa = '" & rs!aaa & "'", "dsn=breed", adOpenKeyset, adLockOptimistic
Values(i, 2) = Mid(Values(i, 1), 1, 3)
rs.MoveNext
Next i rs.Close
' db.Close
End Sub
Private Sub Form_Load()
Const MAKING_DATA = False LoadData
' Send the data to the chart.
Chart1.chartType = VtChChartType2dXY
Chart1.RowCount = NumPoints
Chart1.ColumnCount = 2
Chart1.ChartData = Values
End Sub
Option Explicit
Dim rs As New Recordset
Private Values()
Dim ss As New Recordset
Private NumPoints As IntegerPrivate Sub LoadData()Dim dbname As String
Dim i As Integer If rs.State = adStateOpen Then rs.Close
rs.Open "select * from q", "dsn=breed", adOpenKeyset, adLockOptimistic
rs.MoveLast
NumPoints = rs.RecordCount
ReDim Values(1 To NumPoints, 1 To 2) ' Load the data.
rs.MoveFirst
For i = 1 To NumPoints
Values(i, 1) = Format$(rs!aaa, "yyyy")
If ss.State = adStateOpen Then ss.Close
ss.Open "select * from q where aaa = '" & rs!aaa & "'", "dsn=breed", adOpenKeyset, adLockOptimistic
Values(i, 2) = Mid(Values(i, 1), 1, 3)
rs.MoveNext
Next i rs.Close
' db.Close
End Sub
Private Sub Form_Load()
Const MAKING_DATA = False LoadData
' Send the data to the chart.
Chart1.chartType = VtChChartType2dXY
Chart1.RowCount = NumPoints
Chart1.ColumnCount = 2
Chart1.ChartData = Values
End Sub
解决方案 »
- VB中Dictionary如何使用
- 诚心请教计算VB数学程序的技巧!
- 把从字段名以"|"号间隔的test.txt导入到SQLSERVER表中,提示错误,谢谢大家!!
- 求解,高手进啊
- 我的网页http://p_s_w.nease.net/myz/myz.html 开通了,欢迎访问!
- 请问哪位大虾知道这是什么原理
- 关于查询的问题
- ◆≈≈ 请教一个算法,有点刁 ≈≈◆
- 有关ACCESS数据库
- zilong:如何在一台机器上模拟多个IP
- 继续请问我访问Access 2000的数据库时,Visual Data Manager为什么说格式不对,非要转化到Access 97才行?
- commondialog问题
Dim cur_col As Integer
Dim MaxCount As Long
Dim TotalCount as Long
dim Graphsql as new adodb.recordsetIf GraphSql.State = adStateOpen Then GraphSql.Close
GraphSql.Open "select aaa as Product_date, sum(数量)as Product_Count from q group by aaa order by aaa", "dsn=breed", adOpenKeyset, adLockOptimistic GraphSql.MoveFirst
TotalCount = 0
With jcchart
' .Plot.Axis(VtChAxisIdX).CategoryScale.DivisionsPerLabel = 10 .ChartType = VtChChartType2dBar
.ColumnCount = GraphSql.RecordCount
.ColumnLabelCount = 20
.RowCount = 1
.RowLabel = "日产量列表"
For cur_col = 1 To GraphSql.RecordCount
.Column = cur_col
.Row = 1
.ColumnLabel = GraphSql!Product_Date
.Data = GraphSql!Product_Count
TotalCount = TotalCount + GraphSql!Product_Count
If MaxCount < GraphSql!Product_Count Then
MaxCount = GraphSql!Product_Count
End If GraphSql.MoveNext
Next cur_col
If MaxCount < 10 Then
MaxCount = 10
ElseIf MaxCount < 100 Then
MaxCount = 100
ElseIf MaxCount < 1000 Then
MaxCount = 1000
ElseIf MaxCount < 10000 Then
MaxCount = 10000
ElseIf MaxCount < 100000 Then
MaxCount = 100000
End If .Plot.Axis(VtChAxisIdY).ValueScale.Auto = False
.Plot.Axis(VtChAxisIdY).ValueScale.Maximum = MaxCount
.Plot.Axis(VtChAxisIdY).ValueScale.Minimum = 0 'y轴最小刻度
.Plot.Axis(VtChAxisIdY).ValueScale.MajorDivision = 20 ' y轴刻度10等分
.Plot.Axis(VtChAxisIdY).ValueScale.MinorDivision = 1 '每刻度一个刻度线
.Plot.Axis(VtChAxisIdY).CategoryScale.DivisionsPerLabel = 10 '每时刻一个标注 '将图表作为图例的背景。
.ShowLegend = True
' .SelectPart VtChPartTypePlot, index1, index2, _
index3, index4
' .EditCopy
' .SelectPart VtChPartTypeLegend, index1, _
index2, index3, index4
' .EditPaste
End With
Dim i As Integer
For i = 1 To jcchart.Plot.SeriesCollection.Count
jcchart.Plot.SeriesCollection(i).DataPoints.Item(-1).DataPointLabel.LocationType = VtChLabelLocationTypeAbovePoint
Next i
End Sub
Dim cur_col As Integer
Dim MaxCount As Long
Dim TotalCount as Long
dim Graphsql as new adodb.recordsetIf GraphSql.State = adStateOpen Then GraphSql.Close
GraphSql.Open "select aaa as Product_date, sum(数量)as Product_Count from q group by aaa order by aaa", "dsn=breed", adOpenKeyset, adLockOptimistic
end if
GraphSql.MoveFirst
TotalCount = 0
With jcchart
' .Plot.Axis(VtChAxisIdX).CategoryScale.DivisionsPerLabel = 10 .ChartType = VtChChartType2dBar
.ColumnCount = GraphSql.RecordCount
.ColumnLabelCount = 20
.RowCount = 1
.RowLabel = "日产量列表"
For cur_col = 1 To GraphSql.RecordCount
.Column = cur_col
.Row = 1
.ColumnLabel = GraphSql!Product_Date
.Data = GraphSql!Product_Count
TotalCount = TotalCount + GraphSql!Product_Count
If MaxCount < GraphSql!Product_Count Then
MaxCount = GraphSql!Product_Count
End If GraphSql.MoveNext
Next cur_col
If MaxCount < 10 Then
MaxCount = 10
ElseIf MaxCount < 100 Then
MaxCount = 100
ElseIf MaxCount < 1000 Then
MaxCount = 1000
ElseIf MaxCount < 10000 Then
MaxCount = 10000
ElseIf MaxCount < 100000 Then
MaxCount = 100000
End If .Plot.Axis(VtChAxisIdY).ValueScale.Auto = False
.Plot.Axis(VtChAxisIdY).ValueScale.Maximum = MaxCount
.Plot.Axis(VtChAxisIdY).ValueScale.Minimum = 0 'y轴最小刻度
.Plot.Axis(VtChAxisIdY).ValueScale.MajorDivision = 20 ' y轴刻度10等分
.Plot.Axis(VtChAxisIdY).ValueScale.MinorDivision = 1 '每刻度一个刻度线
.Plot.Axis(VtChAxisIdY).CategoryScale.DivisionsPerLabel = 10 '每时刻一个标注 '将图表作为图例的背景。
.ShowLegend = True
' .SelectPart VtChPartTypePlot, index1, index2, _
index3, index4
' .EditCopy
' .SelectPart VtChPartTypeLegend, index1, _
index2, index3, index4
' .EditPaste
End With
Dim i As Integer
For i = 1 To jcchart.Plot.SeriesCollection.Count
jcchart.Plot.SeriesCollection(i).DataPoints.Item(-1).DataPointLabel.LocationType = VtChLabelLocationTypeAbovePoint
Next i
End Sub
Dim objDBRS As New adodb.Recordset
Dim stSqlstring As String
stSqlstring = "SELECT CAST(YEAR(DATE) AS CHAR(4))+'.'+" & _
"CASE " & _
"LEN(CAST(MONTH(DATE) AS CHAR(2))) " & _
" WHEN 1 THEN '0'+CAST(MONTH(DATE) AS CHAR(2)) " & _
" Else CAST(MONTH(DATE) AS CHAR(2)) END, " & _
" SUM(SI_AMOUNT - CREDIT_AMOUNT)/1000 As 销售 From SI_AMOUNT " & _
" WHERE CAST(YEAR(DATE) AS CHAR(4))+'.'+ " & _
" CASE LEN(CAST(MONTH(DATE) AS CHAR(2))) WHEN 1 THEN '0'+CAST(MONTH(DATE) AS CHAR(2)) " & _
" Else CAST(MONTH(DATE) AS CHAR(2)) END BETWEEN '" & BeginDate & "' AND '" & EndDate & "'" & _
" AND SALES=N'" & Sales & "'" & _
" GROUP BY CAST(YEAR(DATE) AS CHAR(4))+'.'+ " & _
" CASE LEN(CAST(MONTH(DATE) AS CHAR(2))) WHEN 1 THEN '0'+CAST(MONTH(DATE) AS CHAR(2)) " & _
" Else CAST(MONTH(DATE) AS CHAR(2)) End"
objDBRS.Open stSqlstring, objConn, adOpenKeyset, adLockOptimistic
Set .DataSource = objDBRS
.TitleText = Sales & "业绩走势图"
.ShowLegend = True
If Window.Visible = False Then Window.Show 1
End With