Add Hyperlink to Excel Cell using PowerShell

Tagged: PowerShell

Problem#

In this blog post, we will show you how to add hyperlink to Excel cell using PowerShell.

As the context, our original cell will look as follows:

And after adding hyperlink to the cell, it will look as follows:

Using Excel Com Object#

To add hyperlink to the cell, we can use Add method from Hyperlinks property. The first argument determines the cell location where hyperlink is added. The second is hyperlink address. The third is hyperlink subaddress. The fourth determines text to be displayed when we hover the cell. The fifth argument determines text to be displayed on the cell.

The first two arguments are mandatory while the rest is optional.

try {
    # Create excel object
    $excel = New-Object -ComObject Excel.Application
    $workbook = $excel.Workbooks.Open("C:\Scripts\Test.xlsx")
    
    # Add hyperlink to the cells
    $sheet = $workbook.ActiveSheet
    $sheet.Hyperlinks.Add(
        $sheet.Cells.Item(2, 3), 
        "mailto:[email protected]", 
        "", 
        "Email", 
        "Click to send email"
    ) | Out-Null
    
    $sheet.Hyperlinks.Add(
        $sheet.Cells.Item(3, 3), 
        "mailto:[email protected]", 
        "", 
        "Email", 
        "Click to send email"
    ) | Out-Null

    # Save excel file
    $workbook.Save()    
}
finally {
    # Close excel file
    $workbook.Close()

    $excel.Quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)    
}

We enclose the code above with try-finally block because we want to make sure it will release the resource after modifying Excel that is performed at finally block.

Using ImportExcel Module#

ImportExcel is an external module that is built based on EPPlus which is a popular library to work with Excel spreadsheets in .NET.

Before using ImportExcel module, we have to install it.

Install-Module -Name ImportExcel

To add hyperlink, we can specify the cell where we want to add the hyperlink. Then, we set Value and Hyperlink property respectively.

The first one denotes the text that will be displayed on the cell while the second one denotes the address that the hyperlink point to.

try {
    # Import the module
    Import-Module ImportExcel

    # Open excel file
    $excel = Open-ExcelPackage -Path "C:\Scripts\Test.xlsx"

    # Get active worksheet
    $currentWorksheetIndex = $excel.Workbook.View.ActiveTab
    $sheet = $excel.Workbook.Worksheets[$currentWorksheetIndex + 1]

    # Add hyperlink to the cells
    $cell = $sheet.Cells[2, 3]
    $cell.Value = "Click to send email"
    $cell.Hyperlink = "mailto:[email protected]"
    
    $cell = $sheet.Cells[3, 3]
    $cell.Value = "Click to send email"
    $cell.Hyperlink = "mailto:[email protected]"
}
finally {
    # Close excel file
    Close-ExcelPackage $excel    
}

Similar to previous solution, we enclose the script with try-finally block to avoid memory leak.

Using PSExcel Module#

PSExcel is another module based on EPPlus library. You can find all the examples in github repository.

Before using this module, we have to install it.

Install-Module -Name PSExcel

This solution is basically the same with previous solution since the module is based on EPPlus as well. The difference is on the order of Value and Hyperlink property.

In PSExcel, we should set Hyperlink property first before setting Value property. The Hyperlink property denotes the address that the hyperlink point to while Value property denotes the text that will be displayed on the cell.

try {
    # Import the module
    Import-Module PSExcel

    # Open excel file
    $excel = New-Excel -Path 'C:\Scripts\Test.xlsx'

    # Get active worksheet
    $currentWorksheetIndex = $excel.Workbook.View.ActiveTab
    $sheet = $excel.Workbook.Worksheets[$currentWorksheetIndex + 1]

    # Add hyperlink to the cells
    $cell = $sheet.Cells[2, 3]
    $cell.Hyperlink = "mailto:[email protected]"
    $cell.Value = "Click to send email"    
    
    $cell = $sheet.Cells[3, 3]    
    $cell.Hyperlink = "mailto:[email protected]"
    $cell.Value = "Click to send email"

    # Save excel file
    $excel | Save-Excel
}
finally {
    # Close excel file
    $excel | Close-Excel   
}

We also enclose the script with try-finally block to avoid memory leak.

Conclusion#

To add hyperlink to Excel cell using PowerShell, we can use excel module/libraries.

We can use Excel Com Object which is based on .NET Framework. We can also use PowerShell external modules like PSExcel and ImportExcel. ImportExcel is an excellent module which is based on EPPlus library, a popular C# libary for woking with Excel from .NET.

PSExcel is the alternative of ImportExcel module. PSExcel is also based on EPPlus but this module is no longer maintained as stated in its GitHub repository.