PowerShell & Excel 2013

This topic contains 2 replies, has 2 voices, and was last updated by Profile photo of Gordon Grant Gordon Grant 2 years, 1 month ago.

  • Author
    Posts
  • #20041
    Profile photo of Gordon Grant
    Gordon Grant
    Participant

    I have a script that talks to VMware vCenter then exports information into Excel and creates charts based on specific VM info. I'm having difficulty getting the charts to use specific data for the charts can anybody advise?

    Ive attached the excel report output and script (unfinished). The storage info is fine but the cpu chart should reference the data located in columns CV & CW.
    In the script comment (# Create CPU Stats Chart) attempt to create the chart.

    Any information or assistance is appreciated.

    Thanks Mr G

    .. The script

    cls
    Connect-VIServer -Server server.domain -WarningAction:SilentlyContinue
    $VM = Get-VM -Name "some vm"

    # Set date. Todays date minus 30 days for report
    $start = (Get-Date).AddDays(-30)

    #store Storage stats
    $PSGB = [decimal]::round($VM.ProvisionedSpaceGB)
    $USGB = [decimal]::round($VM.UsedSpaceGB)

    #Store CPU Stats
    $CpuAvg = Get-Stat -Entity $VM -Stat cpu.usage.average -Start $start -IntervalMins 7200
    #$CpuMhzAvg = Get-Stat -Entity $VM -Stat cpu.usagemhz.average -Start $start -IntervalMins 7200

    #Store MEM Stats
    $MemAvg = Get-Stat -Entity $VM -Stat mem.usage.average -Start $start -IntervalMins 7200
    $MemBal = Get-Stat -Entity $VM -Stat mem.vmmemctl.average -Start $start -IntervalMins 7200

    #Store Network Stats
    $Net = Get-Stat -Entity $VM -Stat net.usage.average -Start $start -IntervalMins 7200

    # Create New Excel Object
    $xl = New-Object -comobject Excel.Application

    # Show Excel Workbook . Change to $False to hide
    $xl.Visible = $True
    $b = $xl.Workbooks.Add()
    $c = $b.Worksheets.Item(1)
    $c.Name=$VM

    # Formatting
    $c.Cells.Item(1,1).Font.Bold = $true
    $c.Cells.Item(1,1).Font.Size = 9
    $c.Cells.Item(1,2).Font.Bold = $true
    $c.Cells.Item(1,2).Font.Size = 9
    $c.Cells.Item(1,3).Font.Bold = $true
    $c.Cells.Item(1,3).Font.Size = 9
    $c.Cells.Item(1,4).Font.Bold = $true
    $c.Cells.Item(1,4).Font.Size = 9
    $c.Cells.Item(1,5).Font.Bold = $true
    $c.Cells.Item(1,5).Font.Size = 9
    $c.Cells.Item(1,6).Font.Bold = $true
    $c.Cells.Item(1,6).Font.Size = 9
    $c.Cells.Item(1,7).Font.Bold = $true
    $c.Cells.Item(1,7).Font.Size = 9
    $c.Cells.Item(1,8).Font.Bold = $true
    $c.Cells.Item(1,8).Font.Size = 9

    # More Formatting ColumnWidth
    $c.columns.Item("A:A").ColumnWidth = 28
    $c.Columns.Item("B:B").ColumnWidth = 12
    $c.Columns.Item("C:C").ColumnWidth = 38
    $c.Columns.Item("D:D").ColumnWidth = 8
    $c.Columns.Item("E:E").ColumnWidth = 9
    $c.Columns.Item("F:F").ColumnWidth = 16
    $c.Columns.Item("G:G").ColumnWidth = 16
    $c.Columns.Item("H:H").ColumnWidth = 13

    # Center all text 1st Row
    $c.Cells.Item(1, 1).HorizontalAlignment = -4108
    $c.Cells.Item(1, 2).HorizontalAlignment = -4108
    $c.Cells.Item(1, 3).HorizontalAlignment = -4108
    $c.Cells.Item(1, 4).HorizontalAlignment = -4108
    $c.Cells.Item(1, 5).HorizontalAlignment = -4108
    $c.Cells.Item(1, 6).HorizontalAlignment = -4108
    $c.Cells.Item(1, 7).HorizontalAlignment = -4108
    $c.Cells.Item(1, 8).HorizontalAlignment = -4108

    # Center all text 2nd row
    $c.Cells.Item(2, 1).HorizontalAlignment = -4108
    $c.Cells.Item(2, 2).HorizontalAlignment = -4108
    $c.Cells.Item(2, 3).HorizontalAlignment = -4108
    $c.Cells.Item(2, 4).HorizontalAlignment = -4108
    $c.Cells.Item(2, 5).HorizontalAlignment = -4108
    $c.Cells.Item(2, 6).HorizontalAlignment = -4108
    $c.Cells.Item(2, 7).HorizontalAlignment = -4108
    $c.Cells.Item(2, 8).HorizontalAlignment = -4108

    # Fill out Excel Sheet
    $c.Cells.Item(1,1) = "Virtual Machine Name (vCenter Name)"
    $c.Cells.Item(2,1) = $VM.Name
    $c.Cells.Item(1,2) = "VM DNS name"
    $c.Cells.Item(2,2) = $VM.Guest.HostName
    $c.Cells.Item(1,3) = "Guest Operating System"
    $c.Cells.Item(2,3) = $VM.Guest.OSFullName
    $c.Cells.Item(1,4) = "Cpu Count"
    $c.Cells.Item(2,4) = $VM.NumCpu
    $c.Cells.Item(1,5) = "Memory GB"
    $c.Cells.Item(2,5) = $VM.MemoryGB
    $c.Cells.Item(1,6) = "VMware Tools Status"
    $c.Cells.Item(2,6) = $VM.ExtensionData.Guest.ToolsStatus.ToString()
    $c.Cells.Item(1,7) = "Storage Allocated GB"
    $c.Cells.Item(2,7) = $PSGB
    $c.Cells.Item(1,8) = "Storage Used GB"
    $c.Cells.Item(2,8) = $USGB

    # Insert CPU stats into Excel ## These are created in columns CV , CW
    $c.Cells.Item(1, 100) = "Time"
    $c.Cells.Item(1, 101) = "Usage %"
    #$c.Cells.Item(1, 102) = "Usage in MHz"

    # Insert CPU% Average data. Column CW
    $cells = $c.Cells
    $row=1
    $col=100
    foreach ($obj in $CpuAvg) {
    $row++
    $col=101
    $cells.item($Row,$col) = [decimal]::round($obj.Value)
    }

    # Insert CPU Time data. Column CV
    $cells = $c.Cells
    $row=1
    $col=100
    foreach ($obj in $CpuAvg) {
    $row++
    $col=100
    $cells.item($Row,$col)=$obj.Timestamp
    }

    # Chart info
    $xlConditionValues=[Microsoft.Office.Interop.Excel.XLConditionValueTypes]
    $xlTheme=[Microsoft.Office.Interop.Excel.XLThemeColor]
    $xlChart=[Microsoft.Office.Interop.Excel.XLChartType]
    $xlIconSet=[Microsoft.Office.Interop.Excel.XLIconSet]
    $xlDirection=[Microsoft.Office.Interop.Excel.XLDirection]

    # Create VM Storage Chart
    $chart1=$c.Shapes.AddChart().Chart
    $chart1.chartType=$xlChart::xlBarClustered
    $chart1.ChartTitle.Text = "Storage Utilization"
    $c.shapes.item("Chart 1").top=45
    $c.shapes.item("Chart 1").left=10

    # Create CPU Stats Chart
    $chart2=$c.Shapes.AddChart().Chart
    $chart2.chartType=$xlChart::xl3DLine
    $chart2.ChartTitle.Text = "CPU Usage Average"
    # Set CPU Chart data range
    $range2 = $xl.Range("CV:CW")
    $range2.activate

    $chart2.SetSourceData($range2)

    $c.shapes.item("Chart 2").top=45
    $c.shapes.item("Chart 2").left=420

    # Create MEM Stat Chart ##############################################################
    $chart3=$c.Shapes.AddChart().Chart
    $chart3.chartType=$xlChart::xlBarClustered
    $chart3.ChartTitle.Text = "RAM Usage Average & Balloning"

    $c.shapes.item("Chart 3").top=300
    $c.shapes.item("Chart 3").left=10

    # Create Network Average Stat Chart ##################################################
    $chart4=$c.Shapes.AddChart().Chart
    $chart4.chartType=$xlChart::xlLine
    $chart4.ChartTitle.Text = "Network Usage Average"

    $c.shapes.item("Chart 4").top=300
    $c.shapes.item("Chart 4").left=420

    # Save worksheet to users desktop

    #$b.SaveAs "C:\Users\$([Environment]::UserName)\Desktop\CR.xlsx"

    #$xl.Quit()

  • #20178
    Profile photo of Don Jones
    Don Jones
    Keymaster

    You may want to post your question in StackOverflow; I'm seeing a pattern of Excel related questions going unanswered here because everyone is desperately trying to move away from programming Excel. It's awkward, and is a very old COM interface. I've been moving to SSRS, myself. Much nicer, and I can still use PowerShell to populate the data.

  • #20255
    Profile photo of Gordon Grant
    Gordon Grant
    Participant

    Thanks Don,

    Totally agree, trying to program Excel has been a nightmare! I'll look into SSRS.

    Many Thanks

    Gordon Grant

You must be logged in to reply to this topic.