Multi tool use

Powershell script- add data to excel

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!

1 Answer
1

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


foreach ($line in Get-Content success.txt){ $var1 = data $var2 = data $var3 = data Write-Output "$line $var1 $var2 $var3"

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.