Examples/HyperLinks/Races.ps1


#First 10 races is a CSV file containing the top 10 finishers for the first 10 Formula one races of 2018. Read this file and group the results by race
#We will create links to each race in the first 10 rows of the spreadSheet
#The next row will be column labels
#After that will come a block for each race.
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}

#Read the data, and decide how much space to leave for the hyperlinks
$scriptPath = Split-Path -Path $MyInvocation.MyCommand.path -Parent
$dataPath   = Join-Path  -Path $scriptPath -ChildPath "First10Races.csv"
$results    = Import-Csv -Path $dataPath | Group-Object -Property RACE
$topRow     = $lastDataRow = 1 + $results.Count

#Export the first row of the first group (race) with headers.
$path       = "$env:TEMP\Results.xlsx"
Remove-Item -Path $path -ErrorAction SilentlyContinue
$excel      = $results[0].Group[0] | Export-Excel -Path $path -StartRow $TopRow  -BoldTopRow -PassThru

#export each group (race) below the last one, without headers, and create a range for each using the group (Race) name
foreach ($r in $results) {
    $excel        = $R.Group | Export-Excel -ExcelPackage $excel -NoHeader -StartRow ($lastDataRow +1) -RangeName $R.Name -PassThru -AutoSize
    $lastDataRow += $R.Group.Count
}

#Create a hyperlink for each property with display text of "RaceNameGP" which links to the range created when the rows were exported a
$results | ForEach-Object {(New-Object -TypeName OfficeOpenXml.ExcelHyperLink -ArgumentList "Sheet1!$($_.Name)" , "$($_.name) GP")} |
            Export-Excel -ExcelPackage $excel -AutoSize  -Show