VBA自动画图

来源：互联网发布：json对象和json字符串编辑：程序博客网时间：2024/05/17 07:08
Private Sub CommandButton1_Click()
    Call doit
End Sub
Public column_range As String
Public counter_num As Integer
Sub getLinenum(range_num)
    
    column_range = column_range + CStr(range_num)
    For Each c In Worksheets("TOP").range(column_range)
        If c.Value <> "" Then
            counter_num = counter_num + 1
        Else
            counter_num = range_num - 1000 + counter_num - 2
            Exit For
            
        End If
        
        If counter_num = 1001 Then
            column_range = "M"
            column_range = column_range + CStr(range_num + 1)
            range_num = range_num + 1000
            column_range = column_range + ":M"
            counter_num = 1
            Call getLinenum(range_num)
        End If
    Next c
    
End Sub
Sub nameParse(sheetname)
    sheetname = Replace(sheetname, ":", " ")
    sheetname = Replace(sheetname, "/", " ")
    sheetname = Replace(sheetname, "/", " ")
    sheetname = Replace(sheetname, "?", " ")
    sheetname = Replace(sheetname, "*", " ")
    sheetname = Replace(sheetname, "[", " ")
    sheetname = Replace(sheetname, "]", " ")
End Sub
Sub fillNewData(sheetname, start_line, end_line, new_range)
    new_range = 1
    row_range = end_line - start_line + 1
    
    merge_start = start_line
    merge_end = merge_start
    
    Do While row_range > 0
        cur_time = Format(Sheets("TOP").Cells(merge_end, 1).Value, "hh:mm:ss")
        next_time = Format(Sheets("TOP").Cells(merge_end + 1, 1).Value, "hh:mm:ss")
        If cur_time = next_time Then
            merge_end = merge_end + 1
        Else
            new_range = new_range + 1
            Sheets(sheetname).range("A" + CStr(new_range)).Formula = "=TOP!A" + CStr(merge_start)
            Sheets(sheetname).range("C" + CStr(new_range)).Formula = "=SUM(TOP!" + "C" + CStr(merge_start) + ":C" + CStr(merge_end) + ")"
            Sheets(sheetname).range("D" + CStr(new_range)).Formula = "=SUM(TOP!" + "D" + CStr(merge_start) + ":D" + CStr(merge_end) + ")"
            Sheets(sheetname).range("E" + CStr(new_range)).Formula = "=SUM(TOP!" + "E" + CStr(merge_start) + ":E" + CStr(merge_end) + ")"
            
            merge_start = merge_end + 1
            merge_end = merge_start
        End If
        row_range = row_range - 1
    Loop
End Sub
Sub CreateSheet(sheetname, start_line, end_line)
    
    'check the sheetname
    If Len(sheetname) > 31 Then
        sheetname = Left(sheetname, 31)
    End If
    Call nameParse(sheetname)
    
    'create new sheet
    Sheets.Add.Name = sheetname
    
    'copy data
    'Sheets("TOP").Select
    'Rows(CStr(start_line) + ":" + CStr(end_line)).Select
    'Selection.Copy
    'Sheets(sheetname).Select
    'Rows("2:2").Select
    'ActiveSheet.Paste
    
    'copy title
    Sheets("TOP").Select
    Rows("1:1").Select
    Selection.Copy
    Sheets(sheetname).Select
    Rows("1:1").Select
    ActiveSheet.Paste
    
    Call fillNewData(sheetname, start_line, end_line, new_range)
    
    'format data
    row_num = new_range
    For Each c In Sheets(sheetname).range("D2:" + "E" + CStr(row_num))
        c.Value = c.Value * 0.01
    Next c
    Sheets(sheetname).range("D2:" + "E" + CStr(row_num)).Select
    Selection.NumberFormatLocal = "0.00%"
    
    'create graph
    graphname = sheetname + " CPU"
    Charts.Add.Name = graphname
    ActiveChart.ChartType = xlAreaStacked
    range_string = "A1:" + "A" + CStr(row_num) + "," + "D1:" + "E" + CStr(row_num)
    ActiveChart.SetSourceData Source:=Sheets(sheetname).range(range_string), _
        PlotBy:=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:=sheetname
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "CPU"
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With
End Sub
Sub doit()
    column_range = "M2:M"
    counter_num = 2
    range_num = 1000
    
    Call getLinenum(range_num)
    MsgBox "There are " + CStr(counter_num) + " records in Top sheet."
    
    column_range = "M3:M"
    column_range = column_range + CStr(counter_num + 2)
    
    cur_command = Worksheets("TOP").range("M2").Value
    start_line = 2
    end_line = start_line
    
    'sort
    sortString = "A1:P" + CStr(counter_num + 1)
    Sheets("TOP").Select
    Worksheets("TOP").range("A1:P11").sort Key1:=range("M2"), Order1:=xlAscending, Key2:=range( _
        "A2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
        :=False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin, DataOption1:= _
        xlSortNormal, DataOption2:=xlSortNormal
    For Each c In Worksheets("TOP").range(column_range)
        If c.Value = cur_command Then
            end_line = end_line + 1
        Else
            Call CreateSheet(cur_command, start_line, end_line)
            cur_command = Worksheets("TOP").range("M" + CStr(end_line + 1)).Value
            start_line = end_line + 1
            end_line = start_line
        End If
    Next c
End Sub
该程序功能为根据nmon的分析结果，将各个进程的分析数据制作成图形。
1，读TOP sheet，获得记录的数目。
2，将TOP sheet 排序， first: command, second: time
3，为当前处理的command 创建新的sheet
4, 回到Top sheet 计算同一时间点上的user CPU占用率和sys CPU占用率的和
5，将该记录写入新创建的sheet, 根据数据画图