Examples/AddImage/Add-ExcelImage.ps1 - master - diev/dfinke-ImportExcel

Examples/AddImage/Add-ExcelImage.ps1
119 строк4 KB
dfinke
@joshooaj added throw if not on Windows, pls review
04 фев 2022, 02:43
04 фев 2022, 02:435ab6a91
function Add-ExcelImage {
    <#
    .SYNOPSIS
        Adds an image to a worksheet in an Excel package.
    .DESCRIPTION
        Adds an image to a worksheet in an Excel package using the
        `WorkSheet.Drawings.AddPicture(name, image)` method, and places the
        image at the location specified by the Row and Column parameters.
        
        Additional position adjustment can be made by providing RowOffset and
        ColumnOffset values in pixels.
    .EXAMPLE
        $image = [System.Drawing.Image]::FromFile($octocat)
        $xlpkg = $data | Export-Excel -Path $path -PassThru
        $xlpkg.Sheet1 | Add-ExcelImage -Image $image -Row 4 -Column 6 -ResizeCell
        
        Where $octocat is a path to an image file, and $data is a collection of
        data to be exported, and $path is the output path for the Excel document,
        Add-Excel places the image at row 4 and column 6, resizing the column
        and row as needed to fit the image.
    .INPUTS
        [OfficeOpenXml.ExcelWorksheet]
    .OUTPUTS
        None
    #>
    [CmdletBinding()]
    param(
        # Specifies the worksheet to add the image to.
        [Parameter(Mandatory, ValueFromPipeline)]
        [OfficeOpenXml.ExcelWorksheet]
        $WorkSheet,

        # Specifies the Image to be added to the worksheet.
        [Parameter(Mandatory)]
        [System.Drawing.Image]
        $Image,        

        # Specifies the row where the image will be placed. Rows are counted from 1.
        [Parameter(Mandatory)]
        [ValidateRange(1, [int]::MaxValue)]
        [int]
        $Row,

        # Specifies the column where the image will be placed. Columns are counted from 1.
        [Parameter(Mandatory)]
        [ValidateRange(1, [int]::MaxValue)]
        [int]
        $Column,

        # Specifies the name to associate with the image. Names must be unique per sheet.
        # Omit the name and a GUID will be used instead.
        [Parameter()]
        [string]
        $Name,

        # Specifies the number of pixels to offset the image on the Y-axis. A
        # positive number moves the image down by the specified number of pixels
        # from the top border of the cell.
        [Parameter()]
        [int]
        $RowOffset = 1,

        # Specifies the number of pixels to offset the image on the X-axis. A
        # positive number moves the image to the right by the specified number
        # of pixels from the left border of the cell.
        [Parameter()]
        [int]
        $ColumnOffset = 1,

        # Increase the column width and row height to fit the image if the current
        # dimensions are smaller than the image provided.
        [Parameter()]
        [switch]
        $ResizeCell
    )

    begin {
        if ($IsWindows -eq $false) {
            throw "This only works on Windows and won't run on $([environment]::OSVersion)"
        }
        
        <#
          These ratios work on my machine but it feels fragile. Need to better
          understand how row and column sizing works in Excel and what the
          width and height units represent.
        #>
        $widthFactor = 1 / 7
        $heightFactor = 3 / 4
    }

    process {
        if ([string]::IsNullOrWhiteSpace($Name)) {
            $Name = (New-Guid).ToString()
        }
        if ($null -ne $WorkSheet.Drawings[$Name]) {
            Write-Error "A picture with the name `"$Name`" already exists in worksheet $($WorkSheet.Name)."
            return
        }

        <#
          The row and column offsets of 1 ensures that the image lands just
          inside the gray cell borders at the top left.
        #>
        $picture = $WorkSheet.Drawings.AddPicture($Name, $Image)
        $picture.SetPosition($Row - 1, $RowOffset, $Column - 1, $ColumnOffset)
        
        if ($ResizeCell) {
            <#
              Adding 1 to the image height and width ensures that when the
              row and column are resized, the bottom right of the image lands
              just inside the gray cell borders at the bottom right.
            #>
            $width = $widthFactor * ($Image.Width + 1)
            $height = $heightFactor * ($Image.Height + 1)
            $WorkSheet.Column($Column).Width = [Math]::Max($width, $WorkSheet.Column($Column).Width)
            $WorkSheet.Row($Row).Height = [Math]::Max($height, $WorkSheet.Row($Row).Height)
        }
    }
}