CustomPatternClient_Excel_Range_Values

0.1.0

CustomPatternClient_Excel_Range_Values.ps1


<#PSScriptInfo
 
.VERSION 0.1.0
 
.GUID 04ce7afe-5b57-4880-bb7a-fde6c59f1b74
 
.AUTHOR slobo@microsoft.com
 
.COMPANYNAME Microsoft Corp.
 
.COPYRIGHT
 
.TAGS
 
.LICENSEURI
 
.PROJECTURI
 
.ICONURI
 
.EXTERNALMODULEDEPENDENCIES
 
.REQUIREDSCRIPTS
 
.EXTERNALSCRIPTDEPENDENCIES
 
.RELEASENOTES
 
 
#>

<#
 
.DESCRIPTION
 Outputs an array of cell address, value pairs in the range specified.
 
#> 

Param()


<#
.SYNOPSIS
This script exercises the GetRangeValues capability of the ISheetRangeInventory custom pattern
 
.DESCRIPTION
This script outputs an array of cell address and cell value pairs within the sheet and range specified.
The ordering (column or row major) and the delimiter string may be specified on input.
 
.NOTE
PowerShell must be setup with CustomPatternClient_Excel_Setup.ps1
 
.EXAMPLE
CustomPatternClient_Excel_Range_Values.ps1 -book "Book1" -sheet "Sheet1" -cellRange "C3:E5" -delimiter ": " -rowMajor $false
 
.EXAMPLE
CustomPatternClient_Excel_Range_Values.ps1 -window "NewBookName.xlsx - Excel" -pane NewBookName -sheet "Sheet1" -cellRange "C3:E5"
 
.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 cellRange
The range of cells for which cell values are required.
 
.PARAMETER delimiter
The string separator between the cell address and its value.
 
.PARAMETER rowMajor
The boolean value to specify ordering - rowMajor (true) or columnMajor.
 
#>

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]$cellRange,
    [Parameter(Mandatory=$False)][string]$delimiter,
    [Parameter(Mandatory=$False)][bool]$rowMajor
    )

# Setup
Set-PSRepository -Name "PSGallery" -InstallationPolicy Trusted
Install-Module -Name YellowBox -Scope CurrentUser -MinimumVersion 0.0.2.0 | Import-Module

# Pattern GUID
[Guid] $IID_SheetCellValue = "238037C4-BBA3-4C0E-9371-66046B81E957"

# Method GUID
[Guid] $IID_GetRangeValues = "1D69601E-D934-472B-BDCD-13147EE5A3A9"

# 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"
}

# Default delimiter is a spaced hyphen, if not specified
if (-not($delimiter))
{
    $delimiter = " - "
}

# Default rowMajor to true, if not specified
if (-not($rowMajor))
{
    $rowMajor = $false
}

# 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 $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_SheetCellValue, <# out #> $customPattern)

# Prepare an output list
$addressValuePairs = [YellowBox.Client.ExtensionMethodArgument]::new()

# Call Method
Write-Output "Doing evaluation of cell $name"
$customPattern.CallExtensionMethod($IID_GetRangeValues, <# in #> $cellRange, <# in #> $delimiter, <# in #> $rowMajor, <# out #> $addressValuePairs)

if ($null -eq $addressValuePairs)
{
    Throw "GetRangeValues returned null value"
}
foreach ($pair in $addressValuePairs.Value)
{
    Write-Output "$pair"
}