Friday, June 12, 2015

How do I fill an excel worksheet with data using PowerShell?

It is quite easy, if you really want to.

The following code brings up an Excel workbook on your desktop
$Excel = New-Object -Com Excel.Application
$Excel.visible = $True

This line adds a worksheet to the workbook with default name, Sheet1
$Excel = $Excel.Workbooks.Add()

Name the worksheet you created with the following:
$Excel.WorkSheets.Item(1).Name = "Contacts"
The default is changed from "Sheet1" to "Contacts"

The worksheet has cells with (columnNum, RowNum) reference
Fill up the sheet with the data using:
$Sheet.Cells.Item(1,1) = “First Name”
$Sheet.Cells.Item(1,2) = “Last Name”
$Sheet.Cells.Item(1,3) = “Phone”
$Sheet.Cells.Item(1,4) = “Email”
This displays the Excel Book1 as shown here:

The full code is here:

Whenever possible look for intellisense help!
p.s: Tom Hoskins is an imaginary name

No comments: