NMON第3分析工具

Sub Run_AnalySheets()

'*****设置参数***************
Dim FileList As Variant
Dim SheetList(1 To 10) As Variant
Dim FileName As String
Dim NumFiles As Integer
Dim FileNamePart() As String
Dim FileNamePartServer As String
Dim FileNamePartYear As String
Dim FileNamePartMonth As String
Dim FileNamePartDay As String
Dim FileNamePartTime As String
Dim NumSettingRows As Integer
Dim Sum As Double
Dim MaxVal As Double
Dim TmpVal As Double
Dim N As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim Num As Integer
Dim CPUSheetName As String
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim CalRow As Integer
Dim Wrow As Integer
Dim Wcol As Integer
Dim NumDataRows As Integer
Dim WRcol As Integer
Dim SFlag As Boolean
Dim MFlag As Boolean

'********Get Set SheetName*****************************
Sheets("Setting1").Select
NumSettingRows = ActiveSheet.UsedRange.Rows.Count - 1
Range("A1").Select
For i = 1 To NumSettingRows
    SheetList(i) = ActiveCell.Offset(i, 0).Value
Next

'*****建立选择文件列表*******
NumFiles = 0
If FileName = "" Or Dir(FileName) = "" Then
   FileList = Application.GetOpenFilename("NMON Files(*.xls),*.xls", 1, "Select NMON Excel file(s) to be processed", , True)
   If VarType(FileList) <> vbBoolean Then NumFiles = UBound(FileList)
End If
'MsgBox NumFiles

'*****依次打开文件***********
For i = 1 To NumFiles

    '********Split select file name********************
    FileName = FileList(i)
    'CheckCPUFigureFlag = False
    FileNamePart = Split(FileName, ".")
    FileNamePartServer = LCase(FileNamePart(1))
    FileNamePartYear = Left(FileNamePart(2), 4)
    FileNamePartMonth = Mid(FileNamePart(2), 5, 2)
    FileNamePartDay = Right(FileNamePart(2), 2)
    FileNamePartTime = Left(FileNamePart(3), 2)
       
    '**************Set Write Col Number*****************
    Select Case FileNamePart(1)
        Case "misapp1"
            Wcol = 0
        Case "misapp2"
            Wcol = 1
        Case "misdb1"
            Wcol = 2
        Case "misdb2"
            Wcol = 3
    End Select
   
    Select Case FileNamePart(3)
        Case "180100"
            Wrow = 5
        Case "080100"
            Wrow = 4
    End Select
   
    '*****************Write Subject************************
    Sheets(2).Select
    For k = 1 To 7
        Sheets(2).Cells(Wcol * 7 + k + 1, 1) = FileNamePart(2)
        Sheets(2).Cells(Wcol * 7 + k + 1, 2) = FileNamePart(1)
    Next
    'Me.Save
   
    CPUSheetName = ""
    Set xlApp = New Excel.Application
    Set xlBook = Nothing
    Set xlSheet = Nothing

    xlApp.Workbooks.Open FileName:=FileName
    Set xlBook = xlApp.Workbooks(1)      '一个工作簿
   
    '**********依次计算表中数据*********************************
    For j = 1 To NumSettingRows
        CPUSheetName = SheetList(j)
        If CPUSheetName = "" Then
            Exit For
        End If
        Set xlSheet = xlBook.Worksheets(CPUSheetName)      '第一个表
        xlSheet.Activate
        NumDataRows = xlSheet.UsedRange.Rows.Count
   
        Sum = 0
        Num = 0
        CalRow = 1
        MaxVal = 0
        TmpVal = 0
        SFlag = True
        MFlag = True
       
        Select Case CPUSheetName
            Case "CPU_ALL"
                CalRow = 2
                WRcol = 2
            Case "MEM"
                CalRow = 4
                WRcol = 4
                MFlag = False
            Case "PAGE"
                CalRow = 4
                WRcol = 8
            Case "IOADAPT"
                CalRow = 2
                WRcol = 6
                SFlag = False
        End Select
       
        For N = 2 To NumDataRows - 1
            If SFlag Then
            '****************计算平均数**************************
                If Len(Trim(xlSheet.Cells(N, CalRow))) <> 0 Then
                    TmpVal = xlSheet.Cells(N, CalRow)
                    Sum = Sum + TmpVal
                    Num = Num + 1
                End If
            '****************计算最大值***************************
                If TmpVal > MaxVal Then
                    MaxVal = TmpVal
                End If
            Else
            '***************计算IO********************************
                If Len(Trim(xlSheet.Cells(N, CalRow))) <> 0 Then
                    Sum = Sum + xlSheet.Cells(N, CalRow)
                    Sum1 = Sum1 + xlSheet.Cells(N, CalRow + 1)
                End If
            End If
        Next
        '**********************写入结果********************************
        If SFlag Then
            If MFlag Then
                ThisWorkbook.Worksheets("Sheet2").Cells(Wcol * 7 + WRcol, Wrow) = Sum / Num * 0.01
                ThisWorkbook.Worksheets("Sheet2").Cells(Wcol * 7 + WRcol + 1, Wrow) = MaxVal * 0.01
            Else
                ThisWorkbook.Worksheets("Sheet2").Cells(Wcol * 7 + WRcol, Wrow) = Sum * 0.01 / (Num * 32768)
                ThisWorkbook.Worksheets("Sheet2").Cells(Wcol * 7 + WRcol + 1, Wrow) = MaxVal * 0.01 / 32768
            End If
        Else
            ThisWorkbook.Worksheets("Sheet2").Cells(Wcol * 7 + WRcol, Wrow) = Sum
            ThisWorkbook.Worksheets("Sheet2").Cells(Wcol * 7 + WRcol + 1, Wrow) = Sum1
        End If
    Next
    xlBook.Close
Next

MsgBox "OK"
End Sub