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 + "

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.

Technorati Tags: ,
Published Wed, Jun 17 2009 18:27 by RichardSiddaway
Filed under: