Powershell Excel Add row from array

Question

I am aware of how to update an Excel speadsheet cell-by-cell via a COM object but it's incredibly slow. I have a pscustomobject array of around 600 objects (user details) with 15 properties (string or boolean).

How would I update a row in Excel by passing each array object to it using Value2? Can someone kindly paste a working example of passing an array and updating a whole row? The following code works for the first row (heading) but no further rows are updated.

$obj_fields = $obj | Get-Member | ?{$_.membertype -eq 'noteproperty'}
$alpha_column = [char]($obj_fields.count + 96)

If ($row -eq 2) {
   #heading
   $range = $SPREADSHEET_WORKSHEET_2.cells.Item(1,1).range("a1`:$($alpha_column)1")
   $range.Value2 = $obj_fields.Name
}
#values 
$range = $SPREADSHEET_WORKSHEET_2.cells.Item($row,1).range("a$row`:$alpha_column$row")
$tmp = ($obj_fields | %{$obj."$_"})
$range.Value2 = $tmp

This post looks similar to what I'm trying to do but I'm not using activesheet and can't get it working.

Thanks all

Answer 1

I'm guessing that you want all of your user objects inserted, and are assuming that you can only do a row at a time since that would be better than a cell at a time. Let's just do the whole array at once instead, what do you say? You don't give us some of the information that would be needed, so I'll make up what I don't have already. Obviously you want to use the worksheet represented by $SPREADSHEET_WORKSHEET_2, which is fine we can work with that. Since you posted code that supposedly enters the names of the properties as a header row I assume that you do in fact want a header row, which is good because we are going to paste one in by default (skipping that gets a little more complicated, but I digress). I am going to use $arrUsers as your array variable name since you did not specify one (I am assuming that $obj is one object from the array). So, let's do this...

We will be pasting the array of objects with a header row at cell A1. When pasting to Excel you want things to be tab delimited, which we can do easily enough with ConvertTo-CSV -delimeter "t"and we will also use the-NoTypeInformation` switch for that same cmdlet so that it doesn't insert the object type info as the first row and just gives us the header and records. We will pipe to Clip (if your PowerShell doesn't like to pipe to Clip you can pipe to c:\windows\system32\clip.exe instead which basically inserts things into your clipboard, which is awesome for this purpose).

#Convert array of object to a CSV that is tab delimited and without object type information, and copy it to the clipboard
$arrUsers | ConvertTo-CSV -Delimeter "`t" -NoType | Clip

#Select the first cell of the desired sheet and activate it's PasteSpecial method with no arguments (typing it as `[void]` so we don't get "True" spam because it was successful)
[void]$SPREADSHEET_WORKSHEET_2.cells.Item(1).PasteSpecial()

Done. No, really, that's it. That will paste the entire array with headers to the desired sheet of your workbook at cell A1.

Ok, so that didn't actually directly answer your question, but I think it's what you wanted. To directly answer your question I will tell you how to fill an entire row's worth of values at once. Basically it is the same thing, but we only select one object from the array, and also add |select -skip 1 right before we pipe to clip so that it skips the header row.

$arrUsers[20] | ConvertTo-CSV -NoType -Del "`t" | Select -Skip 1 | Clip
[void]$SPREADSHEET_WORKSHEET_2.cells.Item(1).PasteSpecial()

That pastes the 21st user's record into A1. You don't want A1? Well, that's manageable, let's say you want A22 (21st user, plus header row, it makes sense in my head). We can do:

[void]$SPREADSHEET_WORKSHEET_2.columns.Item(1).cells.item(22).PasteSpecial()

That's the first row, and the 22nd cell in that row. Or you can use the Range method like you did before:

[void]$SPREADSHEET_WORKSHEET_2.Range("A22").cells.Item(1).PasteSpecial()

Same thing. Just a last comment here before I go...

We use .cells.item(1) so often because the Cell object is what has the PasteSpecial method that we want to use. With no arguments it works just like a generic paste. The WorkSheet object does have a Paste method, but that goes off the currently selected cell, and from the way you were talking it sounds like you really don't want to do that. This lets you specify a cell and paste directly to it.