CustomPatternClient_Excel_Object_Inventory.ps1
|
<#PSScriptInfo
.VERSION 0.1.0 .GUID 15fd4745-be9b-4227-8418-de1efa930281 .AUTHOR damienb@microsoft.com .COMPANYNAME Microsoft Corp. .COPYRIGHT .TAGS .LICENSEURI .PROJECTURI .ICONURI .EXTERNALMODULEDEPENDENCIES .REQUIREDSCRIPTS .EXTERNALSCRIPTDEPENDENCIES .RELEASENOTES #> <# .DESCRIPTION This script will write the names of all object on the sheet of the input type. .SYNOPSIS This script is used to exercise the ISheetObjectInventory custom pattern .EXAMPLE CustomPatternClient_Excel_Cell_Inventory.ps1 Book1 Sheet1 shape .EXAMPLE CustomPatternClient_Excel_Cell_Inventory.ps1 -window "Book1" -sheet "Sheet1" -type "shape" .PARAMETER book Workbook name without the file extension. It is used to identify the window root. .PARAMETER sheet The name of any sheet tab in the Excel window. It does not have to be active. .PARAMETER type can be any one of the following: chart - GetChartNames - return names of all charts on Sheet smartart - GetSmartDiagramNames - return names of all smart art diagrams on Sheet slicer - GetSlicerNames - return names of all slicers on Sheet timeslicer - GetTimeSlicerNames - return names of all timeline slicers on Sheet image - GetImageNames - return names of all images (pictures) on Sheet shape - GetShapeNames - return names of all shapes on sheet #> Param( [Parameter(Position = 0, Mandatory=$True)][string]$book, [Parameter(Position = 1, Mandatory=$True)][string]$sheet, [Parameter(Position = 2, Mandatory=$True)][string]$type ) # Setup Set-PSRepository -Name "PSGallery" -InstallationPolicy Trusted Install-Module -Name YellowBox -Scope CurrentUser -MinimumVersion 0.0.2.0 | Import-Module # pattern guid [Guid] $IID_SheetObjectInventory = "BEA372DC-2FD8-4852-A8BC-2DA34CEA5456" # method guids [Guid] $IID_GetChartNames = "6D199DC0-7AF0-43CC-A572-8CF9EE556F75" [Guid] $IID_GetSmartDiagramNames = "AF8706FA-6389-4F90-9EF8-F64AED618C4F" [Guid] $IID_GetSlicerNames = "7D6295DF-E9CC-4B86-BE9F-60BACCB7289D" [Guid] $IID_GetTimeSlicerNames = "F8768BF3-9733-4BD9-9251-507649FF3257" [Guid] $IID_GetImageNames = "A2807141-BA1F-4088-85A1-4C00F140998C" [Guid] $IID_GetShapeNames = "C50C4B9E-2F62-4E47-993D-A961D0C6A03D" # hash table to map type to method id $typeToMethodId = @{ "chart" = $IID_GetChartNames "smartart" = $IID_GetSmartDiagramNames "slicer" = $IID_GetSlicerNames "timeslicer" = $IID_GetTimeSlicerNames "image" = $IID_GetImageNames "shape" = $IID_GetShapeNames } # Validate input type if ($null -eq $typeToMethodId[$type]) { Throw "Invalid type $type specified" } # root element is the top level book pane $windowName = "$book - Excel" # the pattern is on the sheet. Get the element for the sheet $sheetPane = "Sheet " + $sheet $sheetElement = (Select-UIXPath "Window[@Name = `"$windowName`"]/Pane/Pane[@Name=`"$book`"]/Pane[@Name=`"$sheetPane`"]") if ($null -eq $sheetElement) { Throw "Failed to find $sheetpane in $windowName" } # Get the custom pattern $customPattern = [YellowBox.Client.ExtensionMethodContainer]::new() $sheetElement.CallExtensionMethod($IID_SheetObjectInventory, <# out #> $customPattern) # prepare an output list $List = [YellowBox.Client.ExtensionMethodArgument]::new() # call method Write-Output "Finding objects of type $type" $customPattern.CallExtensionMethod($typeToMethodId[$type], <# out #> $List) if ($List -eq $null) { Throw "Get $type returned null value" } foreach ($object in $List.Value) { Write-Output "$object" } |