Powershell script- add data to excel

Question

I am trying to modify a Windows Powershell script I wrote from outputting a csv to creating an excel file because I want to add images to my spreadsheet. When I add data to the csv file I pull values from a file into different variables (displayed here as $var1, $var2, and $var3...), then just use

Write-Output "$var1   $var2   $var3..." | Out-File file.csv

where I just tab between variables to put each one in a different column. Is there a simple way to do something similar on an excel sheet using a ComObject? The only thing I have found would be to break up my variables into different lines like

$sh.Cells.Item($row, 1) = $var1
$sh.Cells.Item($row, 2) = $var2
$sh.Cells.Item($row, 3) = $var3
...

I have a lot of variables, and I was looking for a cleaner/easier way to add the data. Here is the set-up of my excel file:

$xl = New-Object -ComObject Excel.Application -Property @{
Visible = $true
DisplayAlerts = $false
}

$wb = $xl.WorkBooks.Add()
$sh = $wb.Sheets.Item('Sheet1')

I'm running Windows 10 and I have Powershell version 5.0.

Any help would be greatly appreciated! Thanks!

f6a4 f6a4 · Accepted Answer · 2018-07-03T05:52:14

Use an Array and paste it to Excel. This is much more faster than addressing each field separately. This is an example how it works:

$excel = New-Object -ComObject Excel.Application

$excel.Visible = $true
$excel.ScreenUpdating = $true

$workbook = $excel.Workbooks.Add()

$worksheet= $workbook.Worksheets.Item(1) 

# Array must type object!
$excelArray = New-Object 'object[,]' 4,5 

# Fill Array with some data
for( $row = 0; $row -lt $excelArray.GetLength(0); $row++ ) {
    for( $col = 0; $col -lt $excelArray.GetLength(1); $col++ ) {
        $excelArray[$row, $col] = '''' + $row.ToString() + '/' + $col.ToString()
    }
}

# Copy data to Excel
$range = $worksheet.Range('A1', ([char](64 + $excelArray.GetLength(1))).ToString() + $excelArray.GetLength(0).ToString() )
$range.Value2 = $excelArray

Powershell script- add data to excel

1 Answers