POWERSHELL   Powershell – Excel reports automation (I)



Powershell can be very useful to extract data from our systems.
Excel can be very useful to show that data in a friendly way.
Powershell + Excel = Automatic generation of useful reports

These are the basic powershell sentences and functions needed to start generating our Excel reports using Powershell scripts:

Excel COM object

It is neccesary to reference a COM object to manipulate Excel documents

$EXCEL = New-Object -ComObject Excel.Application

Make it visible or not:

Visible is useful when creating the script for troubleshooting

$EXCEL.visible = $false

Open or create new

Open a preexisting excel:

$WORKBOOK = $EXCEL.workbooks.open("D:\MYEXCEL.xlsx")

Create new excel:

$WORKBOOK = $EXCEL.workbooks.Add()

Reference a worksheet

By index:

$WORKSHEETS = $WORKBOOK.worksheets
$WORKSHEET = $WORKBOOK.worksheets.Item(1)

By name:

$WORKSHEETS = $WORKBOOK.worksheets
$WORKSHEET = $WORKBOOK.worksheets.Item("MAILBOXES")

Assign values to cells

$WORKSHEET.cells.item(1,1)="Name 1"
$WORKSHEET.cells.item(1,2)="Value 1"
$WORKSHEET.cells.item(2,1)="Name 2"
$WORKSHEET.cells.item(2,2)="Value 2"

Save

$WORKBOOK.SaveAs("D:\MYEXCEL.xlsx")

Finish

$EXCEL.workbooks.Close()
$EXCEL.Application.Quit()

 

Simple example script

Example of script EXCEL.ps1 that shows the creation of a new excel document (D:\MYNEWEXCEL.xlsx) and assings values to 4 cells:

$OUPUTFILE = "D:\MYNEWEXCEL.xlsx"
$EXCEL = New-Object -ComObject Excel.Application
$EXCEL.visible = $false

$WORKBOOK = $EXCEL.workbooks.Add()
$WORKSHEETS = $WORKBOOK.worksheets
$WORKSHEET = $WORKBOOK.worksheets.Item(1)

$WORKSHEET.cells.item(2,1)="Name 1"
$WORKSHEET.cells.item(2,2)="Value 1"
$WORKSHEET.cells.item(3,1)="Name 2"
$WORKSHEET.cells.item(3,2)="Value 2"

Write-Host "Saving $OUPUTFILE"
$WORKBOOK.SaveAs($OUPUTFILE)
Write-Host "Exiting..."
$EXCEL.workbooks.Close()
$EXCEL.Application.Quit()

Execution of the script:

Powershell Excel Automation Example Execution

Final result:

Powershell Excel Automation Example

This is the first post related to basic Excel manipulation using Powershell. Please, wait for the next 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *