CustomPatternClient_Excel_Range_Inventory.ps1
|
<#PSScriptInfo
.VERSION 0.1.1 .GUID ed51c477-c18b-458c-ad25-7390a0495182 .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 cell ranges on the sheet of the input type. It will stream the names both with and without the range reference. .SYNOPSIS This script is used to exercise the ISheetRangeInventory custom pattern .EXAMPLE CustomPatternClient_Excel_Range_Inventory.ps1 -book Book1 -sheet Sheet1 -type table .EXAMPLE CustomPatternClient_Excel_Range_Inventory.ps1 -window "NewBookName.xlsx - Excel" -pane BookNameOnOpen -sheet Sheet1 -type rowbreaks .PARAMETER book Workbook name without the file extension. It is used to identify the window root, and the second level child pane. This option only works if the workbook name has not changed and file extensions are not shown in file explorer. Otherwise, you must provide explicit naming for the parent window name and the pane name. .PARAMETER window The UIA window name. If you specify a book param, this is assumed to be book - Excel. If file extensions are turned on, then you need to specify the full name book.xlsx - Excel. .PARAMETER pane The UIA name of the pane representing the book view. If you specify a book param, this is assumed to be simply book. If the file has been renamed since opening, this pane will still be named according to book name on open. .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: table - GetTableNamesAndRanges - return ranges of all list tables on Sheet. pivottable - GetPivotTableNamesAndRanges - return ranges of all pivot tables on sheet named - GetNamedRanges - return all named ranges on sheet that are not any kind of table unnamed - GetUnnamedDataRanges - return all data ranges on sheet not part of any named data range or table rowbreaks - GetRowRangesOnPageBreaks - returns ranges of rows that begin a new page colbreaks - GetColumnRangesOnPageBreaks - returns ranges of columns that begin a new page #> Param( [Parameter(Mandatory=$False)][string]$book, [Parameter(Mandatory=$False)][string]$window, [Parameter(Mandatory=$False)][string]$pane, [Parameter(Mandatory=$True)][string]$sheet, [Parameter(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_SheetRangeInventory = "62F8DBCE-13DC-4EF2-AF53-5247ED2A4980" # Method guids [Guid] $IID_GetTableNamesAndRanges = "3FB8E49A-BB4A-4B6C-8B25-CB7E612DA92E" [Guid] $IID_GetPivotTableNamesAndRanges = "B22CD312-E265-49AE-9C73-11CE3D7B8526" [Guid] $IID_GetNamedRanges = "EFC59F27-E965-48A5-8C79-7BB9E70958FD" [Guid] $IID_GetUnnamedDataRanges = "60EBB5D1-299D-471D-8DAB-00EE39EBA79E" [Guid] $IID_GetRowRangesOnPageBreaks = "2544B784-764D-4EAA-A72F-BABBF28B5504" [Guid] $IID_GetColumnRangesOnPageBreaks = "094D1D58-77C4-4302-A922-C3ED850ED2A6" #hash table to map type to method id $typeToMethodId = @{ "table" = $IID_GetTableNamesAndRanges "pivottable" = $IID_GetPivotTableNamesAndRanges "named" = $IID_GetNamedRanges "unnamed" = $IID_GetUnnamedDataRanges "rowbreak" = $IID_GetRowRangesOnPageBreaks "colbreak" = $IID_GetColumnRangesOnPageBreaks } if ($null -eq $typeToMethodId[$type]) { Throw "Invalid type $type specified" } #hash lookup to see if range refs can be included $typeSupportsName = @{ "table" = $true "pivottable" = $true "named" = $true "unnamed" = $false "rowbreak" = $false "colbreak" = $false } # root element is the top level book pane if ($book) { $windowName = "$book - Excel" $paneName = "$book" } elseif ($window -and $pane) { $windowName = "$window" $paneName = "$pane" } else { Throw "Must specify either book or both window and pane arguments" } # See if we have a window by that name $windowElement = Select-UIXPath "Window[@Name = `"$windowName`"]" if ($null -eq $windowElement) { Throw "Failed to find $windowName" } # Find the pane within the window $paneElement = Select-UIXPath "Pane/Pane[@Name=`"$paneName`"]" $windowElement #if ($null -eq (Select-UIXPath "Window[@Name = `"$windowName`"]/Pane/Pane[@Name=`"$paneName`"]")) if ($null -eq $paneElement) { Throw "Failed to find pane $paneName in $windowName" } # the pattern is on the sheet. Get the element for the sheet $sheetPaneName = "Sheet " + $sheet $sheetElement = Select-UIXPath "Pane[@Name=`"$sheetPaneName`"]" $paneElement if ($null -eq $sheetElement) { Throw "Failed to find $sheetPaneName in pane $paneName in window $windowName" } # Get the custom pattern $customPattern = [YellowBox.Client.ExtensionMethodContainer]::new() $sheetElement.CallExtensionMethod($IID_SheetRangeInventory, <# out #> $customPattern) if ($typeSupportsName[$type]) { Write-Output "Finding $type names" $delimiter = " - " $RangeNamesOnly = [YellowBox.Client.ExtensionMethodArgument]::new() $customPattern.CallExtensionMethod($typeToMethodId[$type], <# in #> $false, <# in #> $delimiter, <# out #> $RangeNamesOnly) if ($null -eq $RangeNamesOnly) { Throw "Get $type ranges returned null value" } foreach ($range in $RangeNamesOnly.Value) { Write-Output "$range" } Write-Output "Finding $type names with ranges" $RangeNamesWithRef = [YellowBox.Client.ExtensionMethodArgument]::new() $customPattern.CallExtensionMethod($typeToMethodId[$type], <# in #> $true, <# in #> $delimiter, <# out #> $RangeNamesWithRef) if ($null -eq $RangeNamesWithRef) { Throw "Get $type ranges returned null value" } foreach ($range in $RangeNamesWithRef.Value) { Write-Output "$range" } } else { Write-Output "Finding $type ranges" $RangeList = [YellowBox.Client.ExtensionMethodArgument]::new() $customPattern.CallExtensionMethod($typeToMethodId[$type], <# out #> $RangeList) if ($null -eq $List) { Throw-Output "Get $type ranges returned null value" } foreach ($range in $RangeList.Value) { Write-Output "$range" } } |