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 · Accepted Answer · 2018-07-03 05:52:14Z

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

answered Jul 3, 2018 at 5:52

f6a4

1,8021 gold badge11 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SPark Over a year ago

Where do I put my variables in? Right now I have it set up so I re-use the variable names for each row, like

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

f6a4 Over a year ago

You have to put the values to an Array! foreach( $line....) { $excelArray[row, col] = data $excelArray[row, col+1] = data ......

Collectives™ on Stack Overflow

Powershell script- add data to excel

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related