CustomPatternClient_Excel_Range_Inventory

0.1.0

CustomPatternClient_Excel_Range_Inventory.ps1

<#PSScriptInfo
 
.VERSION 0.1.0
 
.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 -window "Book1" -sheet "Sheet1" -type "shape"
 
.EXAMPLE
CustomPatternClient_Excel_Range_Inventory.ps1 Book1 Sheet1 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:
    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(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_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
$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_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"
    }
}