form1.frm

vb控件vb控件vb控件vb控件vb控件vb控件vb控件

VERSION 5.00
Object = "{65E121D4-0C60-11D2-A9FC-0000F8754DA1}#2.0#0"; "mschrt20.ocx"
Object = "{0ECD9B60-23AA-11D0-B351-00A0C9055D8E}#6.0#0"; "MSHFLXGD.OCX"
Begin VB.Form Form1 
   BorderStyle     =   4  'Fixed ToolWindow
   ClientHeight    =   11205
   ClientLeft      =   45
   ClientTop       =   225
   ClientWidth     =   15270
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MinButton       =   0   'False
   ScaleHeight     =   11205
   ScaleWidth      =   15270
   ShowInTaskbar   =   0   'False
   StartUpPosition =   3  '窗口缺省
   WindowState     =   2  'Maximized
   Begin MSHierarchicalFlexGridLib.MSHFlexGrid MSHFlexGrid1 
      Height          =   2445
      Index           =   2
      Left            =   12810
      TabIndex        =   2
      Top             =   210
      Width           =   2160
      _ExtentX        =   3810
      _ExtentY        =   4313
      _Version        =   393216
      FixedCols       =   0
      _NumberOfBands  =   1
      _Band(0).Cols   =   2
   End
   Begin MSChart20Lib.MSChart MSC 
      Height          =   3030
      Left            =   120
      OleObjectBlob   =   "Form1.frx":0000
      TabIndex        =   0
      Top             =   105
      Width           =   12495
   End
   Begin MSChart20Lib.MSChart MSC3 
      Height          =   3090
      Left            =   4485
      OleObjectBlob   =   "Form1.frx":24A6
      TabIndex        =   1
      Top             =   3885
      Width           =   7710
   End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim CN As New ADODB.Connection, CRS As New ADODB.Recordset

Private Sub Form_Load()
    CN.Open "provider=microsoft.jet.oledb.4.0;data source=e:\temp\backup\hotel.mdb"
    CRS.Open "select 菜类,count(*)as 品种数量 from 菜单 group by 菜类", CN, adOpenStatic, adLockReadOnly, adCmdText
    AddToMschart CRS, MSC, "各菜类品种数", , True
    CN.Close
    CN.Open "provider=sqloledb.1;data source=MAYASOFT\SQLNEW;initial catalog=company;user id=sa"
    CRS.Open "select sum(case when ([时期]='1') or ([时期]=1)  then [数量] else 0 end) AS [一月]," _
    & "sum(case when ([时期]='2') or ([时期]=2)  then [数量] else 0 end) AS [二月]," _
    & "sum(case when ([时期]='3') or ([时期]=3)  then [数量] else 0 end) AS [三月]," _
    & "sum(case when ([时期]='4') or ([时期]=4)  then [数量] else 0 end) AS [四月]," _
    & "sum(case when ([时期]='5') or ([时期]=5)  then [数量] else 0 end) AS [五月]," _
    & "sum(case when ([时期]='6') or ([时期]=6)  then [数量] else 0 end) AS [六月]," _
    & "sum(case when ([时期]='7') or ([时期]=7)  then [数量] else 0 end) AS [七月]," _
    & "sum(case when ([时期]='8') or ([时期]=8)  then [数量] else 0 end) AS [八月]," _
    & "sum(case when ([时期]='9') or ([时期]=9)  then [数量] else 0 end) AS [九月]," _
    & "sum(case when ([时期]='10') or ([时期]=10) then [数量] else 0 end) AS [十月]," _
    & "sum(case when ([时期]='11') or ([时期]=11) then [数量] else 0 end) AS [十一月]," _
    & "sum(case when ([时期]='12') or ([时期]=12) then [数量] else 0 end) AS [十二月] " _
    & " from (select 时期,isnull(sum(数量), 0) as 数量, isnull(sum(折后金额), 0) as 金额" _
    & " from 买 group by 时期) as da", CN, adOpenStatic, adLockReadOnly, adCmdText
    AddToMschart CRS, MSC3, "2001年12月上旬收支情况(标准模式)"
    Set MSHFlexGrid1(2).DataSource = CRS
    CN.Close
    
    
End Sub

Private Sub AddToMschart(Rst As ADODB.Recordset, MSChart As MSChart20Lib.MSChart, Optional Title As String = "", Optional ChartType As VtChChartType = VtChChartType2dBar, Optional ForceMulitColumn As Boolean = False)
    Dim RecordCount As Long, i As Long, j As Long, Fid As ADODB.Field, n As Integer, fldnum As Integer
    RecordCount = Rst.RecordCount '得到记录数为设定图表行数做准备
    fldnum = Rst.Fields.Count '得到记录集字段数为设定图表列数做准备
    If fldnum > 2 Then ForceMulitColumn = False '如果记录集列表数大于2列则不能使用强制单行多列模式
    If ForceMulitColumn Then '如果强制使用单行多列模式(强制单行多列模式只能用于只有两个字段的记录集,且第二字段为数据段)
        ReDim Value(1 To fldnum - 1, 1 To RecordCount + 1)
    Else
        ReDim Value(1 To RecordCount, 1 To fldnum) '定义标准模式二维数组的各维大小
    End If
    With MSChart
        .Legend.Location.LocationType = VtChLocationTypeTop '在右上角设置图例
        .ChartType = ChartType
        .Plot.View3d.Rotation = 180 '设置旋转度(三维模式)
        For Each Fid In Rst.Fields '向数组中添加数据
            n = n + 1
            Rst.MoveFirst
            For i = 1 To RecordCount
                If ForceMulitColumn Then '为强制单行多列模式下的数组添加数据
                     If n > fldnum - 1 Then Exit For
                     If i = 1 Then Value(n, i) = Rst.Fields(fldnum - 1).Name '得到行名
                     Value(n, i + 1) = Rst.Fields(fldnum - 1).Value '得到列数据
                Else
                    Value(i, n) = Fid.Value '为标准模式添加数据
                End If
                Rst.MoveNext
            Next
         Next
        .ChartData = Value '填充MsChar数据
        .Title = Title '设置标题
        n = 0
        If ForceMulitColumn Then
           For i = 1 To RecordCount '为强制单行多列模式添加列标签
                .DataGrid.ColumnLabel(i, 1) = Rst.Fields(fldnum - 2).Value
           Next
        Else
           For Each Fid In Rst.Fields '为标准模式添加列标签
               n = n + 1
               If n > 1 Then .DataGrid.ColumnLabel(n - 1, 1) = Fid.Name
           Next
        End If
    End With
End Sub