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.
Pingback: How to Get Data into an Excel Spreadsheet Very Quickly with PowerShell « Tome's Land of IT
Great idea, even simpler then using the clipboard.
When I’m finished with the final chapter of our book, I’ll most probably build this into my Export-Xls function 😉
Excellent technique! This is incredibly fast compared to the standard Cell(1,1) = “” method. I’ve already incorporated this into one of my processes. BTW, the second to the last line should read :
$range = $workbook.ActiveSheet.Range("a1","$col$($array.GetLength(0))")
The “+1” was putting an extra line at the end.
Thanks for posting this!
Can only echo the “excellent technique” comment above.
Outputting an array 300+ x 3 takes one second.
In contrast, outputting the same 300+ x 3 data block cell by cell takes 97(!!) seconds
True, you cannot format the cells individually (color, font, numeric) but this is a small price to pay.
Thanks.
This took a process from 9 minutes to 4 seconds! I did modify it a bit to be able to convert Datatables instead of PSObjects, but other than that, Thank You!
Was very helpful that I found this page.Processing has become incredibly fast.Thank you!!
Thank you so much for this. My process was running hours; now down to a couple of minutes.
Thanks a lot, this is indeed way faster than the cell-method.
I’d like to use this to load records from a database, so i don’t know the number of rows and columns in advance. I’m new to PowerShell, but i wasn’t able to dynamically expand arrays of the type “New-Object ‘object[,]’ 1,1”. This only seems to work for arrays of the type @((‘value’,’value’),(‘value’, ‘value’)). So now, i first have to load into an @ array, to then transfer it into an []-array. Not sure what the difference is, but Value2 only seems to accept [] arrays.
Also, the last piece of code in your post can be replaced with:
$range = $workbook.ActiveSheet.Range(“a1:a1”)
$range = $range.resize($array.GetLength(0),$array.GetLength(1))