Tome's Land of IT

IT Notes from the Powertoe – Tome Tanasovski

Category Archives: Office

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

How to Get Data into an Excel Spreadsheet Very Quickly with PowerShell

Make sure you also read part II of this article that shows another technique to satisfy the same requirement, i.e., getting data into an Excel spreadsheet very quickly with PowerShell.

Having spent two years of my life developing and supporting a couple of successful Outlook add-ins back in 2003 I felt at ease taking on the burden of writing a chapter in the PowerShell Bible (due in Summer 2011) on the Office COM objects.  Prior to this I had only dabbled in the Excel.Application for simple things here and there; I had never really hit the Excel objects hard.  One thing I noticed very quickly is that to go cell by cell to insert data is extremely slow.  To be honest, it’s kind of fun to watch the cells populate one at a time the first time you do it.  It’s like a little mini stop motion Excel movie where color and formatting turn your little project into a Sesame Street number counting cut scene.  A fun little movie, but it becomes clear that it’s not practical to create an enterprise-class document.  There are methods to tackle this problem, but I wanted to take a moment to shine a light on a very elegant solution I thought of over dinner with my family while pondering this question that was posed in the Windows PowerShell forum.

Copy/Paste

The process is simple.  Take an object and use convertto-csv to turn it into tab delimited text, put that text into your clipboard, open Excel, create a new worksheet (or open an existing one), and paste the data into the cells you want to put the data into.  It’s really quick, and it allows you to maintain your formatting for the cells.  Here’s some code that does this.  It pastes the output from a Get-ChildItem (dir) of the c-drive into a new xlsx document into B5 down to B5 + the number of rows in my data:

$dir = dir c:\
$dir |ConvertTo-Csv -Delimiter "`t" -NoTypeInformation|out-clipboard
$excel = New-Object -ComObject Excel.Application
$excel.visible = $true
$workbook = $excel.Workbooks.Add()
$range = $workbook.ActiveSheet.Range("b5","b$($dir.count + 5)")
$workbook.ActiveSheet.Paste($range, $false)
$workbook.SaveAs("c:\output.xlsx")

The following example is the one used to anwer the forum post.  It’s nearly identical, but it uses Get-Process, and it opens an existing Excel document:

$procs = Get-Process
$procs |ConvertTo-Csv -Delimiter "`t" -NoTypeInformation |out-clipboard
$excel = New-Object -ComObject Excel.Application
$excel.visible = $true
$workbook = $excel.Workbooks.Open("C:\Dropbox\My Dropbox\scripts\test.xlsx")
$range = $workbook.ActiveSheet.Range("b5","b$($procs.count + 5)")
$workbook.ActiveSheet.Paste($range, $false)

There’s one small problem with the technique outlined in these samples.  It relies on you having an Out-Clipboard cmdlet that you can pipe data into.  Fortunately there are two places you can get such a cmdlet.

Out-Clipboard

The PowerShell Community Extensions have this cmdlet in their module.  My only problem with PSCX is that it’s very bulky, and requires a lot of customization to strip out the things you don’t want.  I personally prefer to maintain code in my own modules where possible.  You can grab the code out of PSCX if you desire – it is open source after all.

My recent preference is to use the Set-Clipboard script that Lee Holmes uses in his “PowerShell Cookbook, version 2“.  You can download the code directly from poshcode and create your own clipboard module by wrapping the text of the script into a function like this:

function out-clipboard {
    #############################################################################
    ##
    ## Set-Clipboard
    ##
    ## From Windows PowerShell Cookbook (O'Reilly)
    ## by Lee Holmes (http://www.leeholmes.com/guide)
    ##
    ##############################################################################

    <#
    .SYNOPSIS

    Sends the given input to the Windows clipboard.

    .EXAMPLE

    dir | Set-Clipboard
    This example sends the view of a directory listing to the clipboard

    .EXAMPLE

    Set-Clipboard "Hello World"
    This example sets the clipboard to the string, "Hello World".

    #>

    param(
        ## The input to send to the clipboard
        [Parameter(ValueFromPipeline = $true)]
        [object[]] $InputObject
    )

    begin
    {
        Set-StrictMode -Version Latest
        $objectsToProcess = @()
    }

    process
    {
        ## Collect everything sent to the script either through
        ## pipeline input, or direct input.
        $objectsToProcess += $inputObject
    }

    end
    {
        ## Launch a new instance of PowerShell in STA mode.
        ## This lets us interact with the Windows clipboard.
        $objectsToProcess | PowerShell -NoProfile -STA -Command {
            Add-Type -Assembly PresentationCore

            ## Convert the input objects to a string representation
            $clipText = ($input | Out-String -Stream) -join "`r`n"

            ## And finally set the clipboard text
            [Windows.Clipboard]::SetText($clipText)
        }
    }
}

Note: I name my function Out-Clipboard even though I use Lee’s Set-Clipboard script because I relied on the PSCX cmdlet so heavily before I received the snippit from Lee that I couldn’t think of calling it anything else.

ZAP!  Lightning fast data transfer directly into an Excel Spreadsheet!

Check out the follow up article that keeps the speed going

%d bloggers like this: