Tome's Land of IT

IT Notes from the Powertoe – Tome Tanasovski

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.

Advertisements

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

  1. Pingback: How to Get Data into an Excel Spreadsheet Very Quickly with PowerShell « Tome's Land of IT

  2. LucD November 5, 2010 at 6:51 pm

    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 😉

  3. John November 30, 2010 at 7:37 am

    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!

  4. Lubo March 16, 2011 at 10:27 am

    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.

  5. Jace August 19, 2011 at 2:08 pm

    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!

  6. gidodongas June 21, 2012 at 1:19 am

    Was very helpful that I found this page.Processing has become incredibly fast.Thank you!!

  7. Zafrir Benyehuda October 11, 2012 at 6:20 pm

    Thank you so much for this. My process was running hours; now down to a couple of minutes.

  8. Wouter October 17, 2012 at 9:35 am

    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))

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: