PowerShell Excel Spreadsheet Creation

Pubblicato il: 28 febbraio 2016
sul canale di: 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

In questa pagina del sito puoi guardare il video online PowerShell Excel Spreadsheet Creation della durata di ore minuti seconda in buona qualità , che l'utente ha caricato PowerShellNerd 28 febbraio 2016, condividi il link con amici e conoscenti, su youtube questo video è già stato visto 25,428 volte e gli è piaciuto 283 spettatori. Buona visione!

355