Read a file and create a graph of the results. The quickest way to graph the data is to take the height and display a number of “*” depending on the value.
| 001
002
003
004
005
006
007
008
| cls
Get-content "High Jump Stats_Adv4.txt" | foreach {
$data = $_ -split ","
Write-Host "Jumper: $($data[1]) $($data[0]) last $($data.count - 3) jumps"
for ($i=2;$i -le $($data.count - 1); $i++) {
"*" * $data[$i]
}
} |
-split is a PowerShell 2 operator - use $_.Split(“,”) in v1
BTW – there are 43 jumps per person not the 30 as stated in the event information.
This answers the letter of the event but probably not the spirit. PowerGadgets would be an interesting option for this. PowerGadgets installs straight into Windows 7. I had tested it with PowerShell v2 in the past but nice that it installs on Windows 7.
If we want to create individual graphs using PowerGadgets
| 001
002
003
004
005
| cls
Get-content "High Jump Stats_Adv4.txt" | foreach {
$data = $_ -split ",",3
$data[2] -split "," | out-chart -gallery lines -Title "$($data[1]) $($data[0])"
} |
All we do is pipe numeric data (notice the double split – the first gives the names plus the numbers as one string. We can then split the numbers separately as required) into out-chart using the first two elements from the first split (jumpers name) as the title.
Finally lets look at charting in Excel – we have to use the COM object to work with Excel (wouldn’t be nice if we got proper PowerShell support in next version of Office – please - )
| 001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
031
032
033
034
035
036
037
038
039
040
041
042
043
044
045
046
047
048
049
050
051
052
053
| cls
$sfile = "C:\Scripts\Scripting Games 2009\Competitors pack\ad4.xlsx"
if(Test-Path $sfile){Remove-Item $sfile}
## create a spreadsheet
$xl = New-Object -comobject "Excel.Application"
$xl.visible = $true
$xlbooks =$xl.workbooks
## remember non-US culture
## add work book
$newci = [System.Globalization.CultureInfo]"en-US"
$wkbk = $xlbooks.PSBase.GetType().InvokeMember("Add", `
[Reflection.BindingFlags]::InvokeMethod, $null, $xlbooks, $null, $newci)
$sheet = $wkbk.WorkSheets.Item(1)
$sheet.Name = "High Jump"
## read the file
$row = $col = 1
Get-content "High Jump Stats_Adv4.txt" | foreach {
$data = $_ -split ",",3
$numbers = $data[2] -split ","
## name
$sheet.Cells.Item($row, $col) = $data[1]
$sheet.Cells.Item($row, $col+1) = $data[0]
## jumps
for ($i=0;$i -le $numbers.length-1;$i++){
$sheet.Cells.Item($row, $col+2+$i) = $numbers[$i]
}
$row++
}
## get the cell range
$xl.Charts.Add() | out-null
$xl.ActiveChart.chartType = 4 ## line chart
$xl.ActiveChart.HasTitle = $true
$xl.ActiveChart.ChartTitle.Text = "High Jump Results"
## (x axis =1, y axis = 2)
$xl.ActiveChart.Axes(1, 1).HasTitle = $true
$xl.ActiveChart.Axes(1, 1).AxisTitle.Text = "Attempt"
$xl.ActiveChart.Axes(2, 1).HasTitle = $true
$xl.ActiveChart.Axes(2, 1).AxisTitle.Text = "Height"
[void]$wkbk.PSBase.GetType().InvokeMember("SaveAs", `
[Reflection.BindingFlags]::InvokeMethod, $null, $wkbk, $sfile, $newci)
[void]$wkbk.PSBase.GetType().InvokeMember("Close", `
[Reflection.BindingFlags]::InvokeMethod, $null, $wkbk, 0, $newci)
$xl.Quit() |
We create a spreadsheet and add a work book . Have to use this long winded method as I am using a non-US culture on my machine (Excel bug).
We can add the work sheet, add the data and finally add the chart. Chart looks ugly but it is a chart. Finally save the spreadsheet using the same horrible construction.
didn’t like this event - tedious