How to add pictures to Excel with PowerShell?

This is part of the How to Excel with PowerShell series. Links to all the tips can be found in this post.
If you would like to learn more about the module with an interactive notebook, check this post out.

Spreadsheets’ main purpose is data: storing, manipulating and analyzing them. We can add some colours or charts to make the data more friendly, but sometimes we may want to add something else – like a logo or picture and all that can be achieved with PowerShell.

Preparation

Firstly, let’s prepare our environment to make sure the file does not exist, otherwise, we may see conflicts.

# set location for the files - I am using the temporary folder in my user profile
Set-Location $env:TEMP

# that is the location of our files
#Invoke-Item $env:TEMP

# cleanup any previous files - this helps in case the example has been already ran
$excelFiles = "ImportExcelHowTo012.xlsx"
Remove-Item $excelFiles -ErrorAction SilentlyContinue

In the next step, we are going to create a simple worksheet with few rows. Nothing fancy, just so it is not totally blank. For this purpose, I have used names of some fictional countries.

# create some dummy data
$country = ConvertFrom-Csv -InputObject @"
Country
Bartovia
Nextdoria
Pottsylvania
Santa Prisca
"@

$country | Export-Excel -Path $excelFiles -WorksheetName HowToPicture

Adding pictures to the worksheets with PowerShell

As an example, I will use my profile photo, but this can by anything you want in your spreadsheet.

# get the picture to be added to the worksheet
$file = (Get-Item 'Mikey.jpg')
$picture = [System.Drawing.Image]::FromFile((Get-Item $file))

Next, we are going to add the picture using AddPicture(Name, ImageFile) method. There is no dedicated function in the module yet, so we would need to work it out directly on the worksheet object.

As you may see I am using the $picture object to set the size of the picture before I close the package and open the workbook.

# open the package
$excelPackage = Open-ExcelPackage -Path $excelFiles

# select the worksheet
$excel = $excelPackage.Workbook.Worksheets['HowToPicture']

# add picture to the worksheet
$picture = $excel.Drawings.AddPicture('picture',$picture)

# change the default size
$picture.SetSize(100,100)

# save and open the workbook
Close-ExcelPackage $excelPackage -Show

Adding picture with PowerShell

As a result, we can see my photo in the Excel worksheet, but we had some data in the first column! Now, we need to move the picture. To do so, we need SetPosition(Row, RowOffsetPixels, Column, ColumnOffsetPixels) method (0-based index).

# open the package
$excelPackage = Open-ExcelPackage -Path $excelFiles -KillExcel

# select the worksheet
$excel = $excelPackage.Workbook.Worksheets['HowToPicture']

# refer the picture by its name
$picture = $excel.Drawings['picture']

# set the position
$picture.SetPosition(3,0,1,0)

# save and open the workbook
Close-ExcelPackage $excelPackage -Show

Adding shapes to Excel with PowerShell

Another thing we can add to Excel spreadsheets is shapes. A list of all available shapes can be found here.

# open the package
$excelPackage = Open-ExcelPackage -Path $excelFiles -KillExcel

# select the worksheet
$excel = $excelPackage.Workbook.Worksheets['HowToPicture']

# add shapes - Rectangle
$shape = $excel.Drawings.AddShape('Rect01', 'Rect')
$shape.SetPosition(4,0,2,0)
$shape.SetSize(50,50)

# add shapes - Cube
$shape = $excel.Drawings.AddShape('Cube01', 'Cube')
$shape.SetPosition(8,0,3,0)
$shape.SetSize(50,50)

# here comes the sun
$shape = $excel.Drawings.AddShape('Sun01', 'Sun')
$shape.SetPosition(12,0,4,0)
$shape.SetSize(50,50)

# save and open the workbook
Close-ExcelPackage $excelPackage -Show

Adding shapes to Excel worksheets with PowerShell

Summary

Managing Excel data with PowerShell is possible, but we can also add extras to make the worksheet even more interesting. And with a few lines of code, there is no need to add these pictures/shapes manually again.

Thank you,
Mikey