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
На этой странице сайта вы можете посмотреть видео онлайн PowerShell Excel Spreadsheet Creation длительностью часов минут секунд в хорошем качестве, которое загрузил пользователь PowerShellNerd 28 Февраль 2016, поделитесь ссылкой с друзьями и знакомыми, на youtube это видео уже посмотрели 25,428 раз и оно понравилось 283 зрителям. Приятного просмотра!
