Interlude I : Excel with PowerShell

One of the things that comes up frequently is being able to provide auditors with documentation. Often the result is something that can be done in Excel. With PowerShell this can be done as part of a script.

First we need to recognize that the machine generating the Excel document will need Excel installed. No Excel, no document using this method.

First we need to start by creating a new COM object instance of Excel:

$xl = New-Object - ComObject "Excel.Application"

So we can watch as the document in populated by the script we add in the following:

$xl.visible = $true

post1

With this we just get a nice empty Excel window. Nothing at all going on. First we need to add a workbook and then sheets to the workbook.

$xlbook = $xl.workbooks.Add()
$xlbook.worksheets.Add() | out-null

post2

That’s looking better. Now we create an object to reference the sheets within the workbook more easily:

$xlsheets = $xlbook.worksheets
$xlsheet1 = $xlsheets.item(1)
$xlsheet1.name = "Report"

What we’ve done is reduce our typing so that we don’t have to type $xlbook.Worksheets.item(1).name. It also makes more sense to us slow-brained humans than a long string of properties run together – translation : lower risk of error.

Now we can add our column headers.

$xlsheet1.Cells.Item(1,1) = "Header1"
$xlsheet1.Cells.Item(1,2) = "Header2"
$xlsheet1.Cells.Item(1,3) = "Header3"

post3

It should be obvious in the above that the value “1,1” refers to row 1 cell 1, “1,2” to row 1 cell 2, etc. Knowing this it should be easy to figure out how to place data into cells within the sheet.

All of this is kind of useful but what would be the point of scripting all up to this point if you don’t go ahead and save the sheet. That takes just two lines of code:

$xlbook.SaveAs("REPORT.XLSX")
$xl.quit() | Out-Null

The code talked about in this post:

$xl = New-Object -ComObject "Excel.Application"
$xl.visible = $true
$xlbook = $xl.workbooks.Add()
$xlbook.worksheets.Add() | out-null
$xlsheets = $xlbook.worksheets
$xlsheet1 = $xlsheets.item(1)
$xlsheet1.name = "Report"
$xlsheet1.Cells.Item(1,1) = "Header1"
$xlsheet1.Cells.Item(1,2) = "Header2"
$xlsheet1.Cells.Item(1,3) = "Header3"
$xlbook.SaveAs("REPORT.XLSX")
$xl.quit() | Out-Null

Next post I’ll talk about how to make this better looking.

Advertisements

Comments are closed.