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.
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
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
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.