Games: Advanced 2
This is playing in a spreadsheet. Need to do some calculations and sorting
| 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
| $header = 1 ## yes it does. 0 = no header
$asc = 1 ## sort ascending
$desc = 2 ## sort descending
$xl = New-Object -comobject "Excel.Application"
$xl.visible = $true
$wkbk = $xl.WorkBooks.Open("C:\Scripts\Scripting Games 2009\Competitors pack\LongJump_Adv2.xls")
## $wkbk.workSheets | Format-Table Index, Name -AutoSize
$sheet = $wkbk.WorkSheets.Item(1)
$sheet.Cells.Item(1,10).FormulaLocal = "Ratio"
for ($i=2;$i -le 19; $i++){
## calculate score
$formula = "=max(e$i" + ":" + "g$i)"
$sheet.Cells.Item($i,8).FormulaLocal = $formula
## over or under achieving
$achieve = "=IF(H$i" + ">D" + $i + ',"Exceed",IF(H' + $i + "<D" + $i + ',"Under Perform","Achieve"))'
$sheet.Cells.Item($i,9).FormulaLocal = $achieve
## ratio over best
$ratio = "=(H" + $i + "/D" + $i + ")"
$sheet.Cells.Item($i,10).FormulaLocal = $ratio
}
## find the winner
$range = $sheet.UsedRange
$range1 = $xl.Range("H2")
$range.Sort($range1, $desc) | Out-Null
Write-Host "The winner is $($sheet.Cells.Item(2,1).FormulaLocal) `
of $($sheet.Cells.Item(2,2).FormulaLocal) `
with a jump of $($sheet.Cells.Item(2,8).Text)"
## sort the sheet by achievement
$range1 = $xl.Range("J2")
$range.Sort($range1, $desc) | Out-Null |
Some constants are defined at the beginning and then we open the spreadsheet. Add a header to an extra column we will be using (11) . We can then loop through each row finding the maximum of the scores and putting it into the results column (17) In the same loop we can determine if they are exceeding their season best (21) and calculate a ratio (25)
A sort on the results column means we can pick off the winner (31) and then another sorts leaves the spreadsheet ordered by the comparison to the season best. It can be saved like that if required.
Interesting mix of PowerShell and Excel but the challenges are in the Excel side and how to get that working in PowerShell rather than PowerShell itself. One possibility would be to open the spreadsheet and create a csv file which would make some the work easier but its probably not worth the effort.