PowerShell Excel Spreadsheet Creation

Publicado em: 28 Fevereiro 2016
no canal de: PowerShellNerd

25,428

283

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

Nesta página do site você pode assistir ao vídeo on-line PowerShell Excel Spreadsheet Creation duração hora minuto segundo em boa qualidade , que foi baixado pelo usuário PowerShellNerd 28 Fevereiro 2016, compartilhe o link com seus amigos e conhecidos, no youtube este vídeo já foi visto 25,428 vezes e gostou 283 espectadores. Boa visualização!