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:
Final result:
This is the first post related to basic Excel manipulation using Powershell. Please, wait for the next 🙂
