Add System.Drawing.Image support

[joshooaj]

Thanks for such a great tool! It's really nice to be able to just chuck data at a cmdlet and see a spreadsheet pop out the other side 😎

I work for a company that works a lot with video and often times our customers would appreciate exporting snapshots of video to Excel to document camera positioning and image quality so I started to explore how to do that with your module and EPPlus today. I thought I'd make a feature request here as well as show how I managed to get it working.

I added another type check for [System.Drawing.Image] and used the worksheet.Drawings.AddPicture method to embed a picture into the sheet. I don't know if this is the best way to do it and I'm annoyed that I can't seem to constrain an image to a specific cell - either that's now how Excel works with images or I haven't worked out how to do it yet.

I also noticed that the column/row sizing use strange units and there's a very good chance the 375/500 and 17/120 ratios I worked out are specific to my environment/dpi/resolution/whatever. But these ratios are what enabled me, on my system, to produce the content in the screenshot below where the column and rows are resized to the dimensions of the image. I understand Excel has a maximum size for rows and columns so this doesn't take that into account yet either.

With the below elseif in place, I was able to pass a [pscustomobject] with a property containing a [System.Drawing.Image] object to Export-Excel

elseif ($v -is [System.Drawing.Image]) {
    $picture = $ws.Drawings.AddPicture("$Name_$ColumnIndex_$row", $v)
    $picture.SetPosition($row - 1, 0, $ColumnIndex - 1, 0)
    if ($ws.Row($row).Height -lt $v.Height * (375/500)) {
        $ws.Row($row).Height = $v.Height * (375/500)
    }
    if ($ws.Column($ColumnIndex).Width -lt $v.Width * (17/120)) {
        $ws.Column($ColumnIndex).Width = $v.Width * (17/120)
    }
}

[dfinke]

Nice work! Could you share how you set up the [pscustomobject] to pass in? Need to think through if this could be easily surfaced with the different checks for height and width. Plus supporting this may be a bit much.

An alternative way is to use -PassThru on Export-Excel and you can add the images to each row. You'd need to handle the row column.

Would like to see your code and will think about it.

Thanks, I like it.

[joshooaj]

Here's a commit showing the changes I made. Sorry about the tabs instead of spaces - I'm surprised VSCode didn't try to match the indentation style of the file.

I noticed that it doesn't play well with AutoSize since Excel doesn't consider the image as cell content, so AutoSize will cause the column width to collapse despite my attempt to carefully match it to the image.

My [pscustomobject] is fairly simple/flat. Here's a contrived example:

try {
    $file = Join-Path $env:TEMP 'hero.jpg'
    Invoke-WebRequest -Uri 'https://aka.ms/powershellhero' -OutFile $file -ErrorAction Ignore
    $image = [System.Drawing.Image]::FromFile($file)

    $obj = [pscustomobject]@{
        Name = 'PowerShell Hero'
        Snapshot = $image
    }
    $obj | Export-Excel -Show -AutoSize:$false
}
finally {
    if ($null -ne $image) {
        $image.Dispose()
    }
    if (Test-Path -Path $file) {
        Remove-Item -Path $file
    }
}

[dfinke]

Thanks. Did you notice if AddPicture took a filename?

[joshooaj]

Yeah it takes a FileInfo or an Image

For my use case, accepting an Image object is ideal because the snapshots I get are never touching the disk - I get them over the network from our video management software. Of course I could write images to disk and remove them as I go if that would greatly simplify things.

[dfinke]

Thanks, was just curious. That is something I'd like to surface so someone using the module, they could specify either.

I'm leaning towards -PassThru and setting this up in the examples folder. I will think about it.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.