How to Get Data into an Excel Spreadsheet Very Quickly with PowerShell – Cont’d

I feel the need – the need for speed

Maverick, Goose – Top Gun, 1986

Yesterday I posted an article that showed a method to insert a set of data into a range of cells by using the clipboard in your script.  Apparently, my genius was beaten by LucD back in May!  So I’m back today to 1-up that!  I’m sure the following method has also been blogged about somewhere before, but I couldn’t find anything on the subject so here it is!

Range.Value2

If you take a range of cells on a worksheet you can directly send a multidimensional array to the Value2 property of the range.  Here’s a simple example that shows this in action:

$excel = New-Object -ComObject Excel.Application
$excel.visible = $true
$workbook = $excel.Workbooks.Add()
$range = $workbook.ActiveSheet.Range("a1","b2")
$array = New-Object 'object[,]' 2,2
$array[0,0] = 'Cell1'
$array[0,1] = 'Cell2'
$array[1,1] = 'Cell3'
$array[1,0] = 'Cell4'
$range.Value2 = $array

So the technique seems simple, but how do we convert PowerShell objects easily into multidimensional arrays. The answer is there is no easy way to do this. That’s why I wrote the following utility function called ConvertTo-MultiArray (code posted on poshcode too):

ConvertTo-MultiArray

function ConvertTo-MultiArray {
 <#
 .Notes
 NAME: ConvertTo-MultiArray
 AUTHOR: Tome Tanasovski
 Website: https://powertoe.wordpress.com
 Twitter: http://twitter.com/toenuff
 Version: 1.0
 CREATED: 11/5/2010
 LASTEDIT:
 11/5/2010 1.0
 Initial Release
 11/5/2010 1.1
 Removed array parameter and passes a reference to the multi-dimensional array as output to the cmdlet
 11/5/2010 1.2
 Modified all rows to ensure they are entered as string values including $null values as a blank ("") string.

 .Synopsis
 Converts a collection of PowerShell objects into a multi-dimensional array

 .Description
 Converts a collection of PowerShell objects into a multi-dimensional array.  The first row of the array contains the property names.  Each additional row contains the values for each object.

 This cmdlet was created to act as an intermediary to importing PowerShell objects into a range of cells in Exchange.  By using a multi-dimensional array you can greatly speed up the process of adding data to Excel through the Excel COM objects.

 .Parameter InputObject
 Specifies the objects to export into the multi dimensional array.  Enter a variable that contains the objects or type a command or expression that gets the objects. You can also pipe objects to ConvertTo-MultiArray.

 .Inputs
 System.Management.Automation.PSObject
        You can pipe any .NET Framework object to ConvertTo-MultiArray

 .Outputs
 [ref]
        The cmdlet will return a reference to the multi-dimensional array.  To access the array itself you will need to use the Value property of the reference

 .Example
 $arrayref = get-process |Convertto-MultiArray

 .Example
 $dir = Get-ChildItem c:\
 $arrayref = Convertto-MultiArray -InputObject $dir

 .Example
 $range.value2 = (ConvertTo-MultiArray (get-process)).value

 .LINK
 https://powertoe.wordpress.com

#>
    param(
        [Parameter(Mandatory=$true, Position=1, ValueFromPipeline=$true)]
        [PSObject[]]$InputObject
    )
    BEGIN {
        $objects = @()
        [ref]$array = [ref]$null
    }
    Process {
        $objects += $InputObject
    }
    END {
        $properties = $objects[0].psobject.properties |%{$_.name}
        $array.Value = New-Object 'object[,]' ($objects.Count+1),$properties.count
        # i = row and j = column
        $j = 0
        $properties |%{
            $array.Value[0,$j] = $_.tostring()
            $j++
        }
        $i = 1
        $objects |% {
            $item = $_
            $j = 0
            $properties | % {
                if ($item.($_) -eq $null) {
                    $array.value[$i,$j] = ""
                }
                else {
                    $array.value[$i,$j] = $item.($_).tostring()
                }
                $j++
            }
            $i++
        }
        $array
    }
}

So we’re halfway there.  We can convert things into multidimensional arrays, but we still need a way to select an appropriate range in a worksheet that will fit the array.  The following code does just that.  It will insert the return value of Get-Process into a new Excel worksheet starting at cell A1:

$excel = New-Object -ComObject Excel.Application
$excel.visible = $true
$workbook = $excel.Workbooks.Add()

### Magic Line
$array = (Get-Process |ConvertTo-MultiArray).Value

$starta = [int][char]'a' - 1
if ($array.GetLength(1) -gt 26) {
    $col = [char]([int][math]::Floor($array.GetLength(1)/26) + $starta) + [char](($array.GetLength(1)%26) + $Starta)
} else {
    $col = [char]($array.GetLength(1) + $starta)
}

$range = $workbook.ActiveSheet.Range("a1","$col$($array.GetLength(0)+1)")
$range.Value2 = $array

Obviously, this is screaming to be turned into a more robust function that can start at any cell, open an existing Excel document, and have the proper cleanup to prevent runaway Excel.exe processes.  But, for now I’m done with this little distraction.  Perhaps LucD will feel inspired to carry on 🙂  I’ve felt comfortable taking this vacation from writing the PowerShell Bible because I will be writing the chapter on the COM objects, but now I must get back to the world of IIS 7 where I am needed.