PowerShell Excel Spreadsheet Creation
Make sure to check out my PowerShell forum BlogPowerShell.com
This tutorial will show you how to use Power Shell to pull information from AD on computer objects, then create a spreadsheet and color the enabled objects green and disabled objects red.
🔔 Subscribe to become a PowerShell Pro! https://www.youtube.com/@matthewdaugh...
#Creates Excel application
$excel = New-Object -ComObject excel.application
#Makes Excel Visable
$excel.Application.Visible = $true
$excel.DisplayAlerts = $false
#Creates Excel workBook
$book = $excel.Workbooks.Add()
#Adds worksheets
#gets the work sheet and Names it
$sheet = $book.Worksheets.Item(1)
$sheet.name = 'Computer Information'
#Select a worksheet
$sheet.Activate() | Out-Null
#Create a row and set it to Row 1
$row = 1
#Create a Column Variable and set it to column 1
$column = 1
#Add the word Information and change the Font of the word
$sheet.Cells.Item($row,$column) = "Computer Information"
$sheet.Cells.Item($row,$column).Font.Name = "Cooper Black"
$sheet.Cells.Item($row,$column).Font.Size = 21
$sheet.Cells.Item($row,$column).Font.ColorIndex = 16
$sheet.Cells.Item($row,$column).Interior.ColorIndex = 2
$sheet.Cells.Item($row,$column).HorizontalAlignment = -4108
$sheet.Cells.Item($row,$column).Font.Bold = $true
#Merge the cells
$range = $sheet.Range("A1:c1").Merge() | Out-Null
#Move to the next row
$row++
#Create Intial row so you can add borders later
$initalRow = $row
#create Headers for your sheet
$sheet.Cells.Item($row,$column) = "Computer Name"
$sheet.Cells.Item($row,$column).Font.Size = 16
$sheet.Cells.Item($row,$column).Font.ColorIndex = 1
$sheet.Cells.Item($row,$column).Interior.ColorIndex = 48
$sheet.Cells.Item($row,$column).Font.Bold = $true
$column++
$sheet.Cells.Item($row,$column) = "LastLogonTimeStamp"
$sheet.Cells.Item($row,$column).Font.Size = 16
$sheet.Cells.Item($row,$column).Font.ColorIndex = 1
$sheet.Cells.Item($row,$column).Interior.ColorIndex = 48
$sheet.Cells.Item($row,$column).Font.Bold = $true
$column++
$sheet.Cells.Item($row,$column) = "Enabled"
$sheet.Cells.Item($row,$column).Font.Size = 16
$sheet.Cells.Item($row,$column).Font.ColorIndex = 1
$sheet.Cells.Item($row,$column).Interior.ColorIndex = 48
$sheet.Cells.Item($row,$column).Font.Bold = $true
#Now that the headers are done we go down a row and back to column 1
$row++
$column = 1
#command you want to use to get infromation
$computers = Import-Csv C:\Users\mdaugherty\Desktop\Computers.csv
foreach($i in $computers){
$info = Get-ADComputer -Identity $($i.name) -Properties name, enabled, PasswordLastSet
#$TimeStamp = [datetime]::FromFileTime($info.lastlogontimestamp)
$sheet.Cells.Item($row,$column) = $info.Name
$column++
$sheet.Cells.Item($row,$column) = $info.PasswordLastSet
$column++
Switch($info.Enabled){
True{$Enabled = "Enabled"; $sheet.Cells.Item($row,$column).Interior.ColorIndex = 4}
False{$Enabled = "Disabled"; $sheet.Cells.Item($row,$column).Interior.ColorIndex = 3}
}
$sheet.Cells.Item($row,$column) = $Enabled
$row++
$column = 1
}
$row--
$dataRange = $sheet.Range(("A{0}" -f $initalRow),("c{0}" -f $row))
7..12 | ForEach {
$dataRange.Borders.Item($_).LineStyle = 1
$dataRange.Borders.Item($_).Weight = 2
}
#Fits cells to size
$UsedRange = $sheet.UsedRange
$UsedRange.EntireColumn.autofit() | Out-Null
Watch video PowerShell Excel Spreadsheet Creation online without registration, duration hours minute second in high quality. This video was added by user PowerShellNerd 28 February 2016, don't forget to share it with your friends and acquaintances, it has been viewed on our site 25,266 once and liked it 284 people.