CustomPatternClient_Excel_Cell_Inventory.ps1
|
<#PSScriptInfo
.VERSION 0.1.0 .GUID bf0f6ab8-cef7-4d6c-973f-b03fe0888167 .AUTHOR damienb@microsoft.com .COMPANYNAME Microsoft Corp. .COPYRIGHT .TAGS .LICENSEURI .PROJECTURI .ICONURI .EXTERNALMODULEDEPENDENCIES .REQUIREDSCRIPTS .EXTERNALSCRIPTDEPENDENCIES .RELEASENOTES #> <# .SYNOPSIS This script is used to exercise the ISheetCellInventory custom pattern .DESCRIPTION This script will write the names of all cells on the sheet with the input trait. .EXAMPLE CustomPatternClient_Excel_Cell_Inventory.ps1 -book Book1 -sheet Sheet1 -trait formula .EXAMPLE CustomPatternClient_Excel_Cell_Inventory.ps1 Book1 Sheet1 formula .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 trait can be any one of the following: formula - GetCellsWithFormula - Get all cells with formulas in the current sheet hyperlink - GetCellsWithHyperlink - Get all cells with hyperlinks in the current sheet formulahyperlink - GetCellsWithFormulaHyperlink - Get all cells with formula hyperlinks in the current sheet note - GetCellsWithNote - Get all cells with notes in the current sheet comment - GetCellsWithComment - Get all cells with comments in the current sheet #> Param( [Parameter(Position = 0, Mandatory=$True)][string]$book, [Parameter(Position = 1, Mandatory=$True)][string]$sheet, [Parameter(Position = 2, Mandatory=$True)][string]$trait ) # Setup Set-PSRepository -Name "PSGallery" -InstallationPolicy Trusted Install-Module -Name YellowBox -Scope CurrentUser -MinimumVersion 0.0.2.0 | Import-Module [Guid] $IID_SheetCellInventory = "654823FE-A483-4915-8709-67266866E518" [Guid] $IID_GetCellsWithFormula = "24E137F2-4FFF-4F50-84AD-2ACD780E7E1F" [Guid] $IID_GetCellsWithHyperlink = "B640F40E-E51A-4CDD-9604-843AA5107C1C" [Guid] $IID_GetCellsWithFormulaHyperlink = "0AAF2B49-015B-4AB3-A093-21F055E8E0F5" [Guid] $IID_GetCellsWithNote = "8F171892-A3B1-4F92-9CE1-CE818B848F52" [Guid] $IID_GetCellsWithComment = "4D4E49BC-CE16-44CD-AC48-1DAA2E862C41" # hash table to map trait to method id $traitToMethodId = @{ "formula" = $IID_GetCellsWithFormula "hyperlink" = $IID_GetCellsWithHyperlink "formulahyperlink" = $IID_GetCellsWithFormulaHyperlink "note" = $IID_GetCellsWithNote "comment" = $IID_GetCellsWithComment } # Validate input trait if ($null -eq $traitToMethodId[$trait]) { Throw "Invalid trait $trait 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_SheetCellInventory, <# out #> $customPattern) $List = [YellowBox.Client.ExtensionMethodArgument]::new() # prepare an output list $customPattern.CallExtensionMethod($traitToMethodId[$trait], <# out #> $List) # Call the custom method Write-Output "Finding cells with $trait" if ($List -eq $null) { Write-Output "Get cells with $trait returned null value" } foreach ($cell in $List.Value) { Write-Output "$cell" } |