PowerShell Excel Spreadsheet Creation

Publié le: 28 février 2016
sur la chaîne: 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

Sur cette page du site, vous pouvez voir la vidéo en ligne PowerShell Excel Spreadsheet Creation durée heure minute seconde en bonne qualité , qui a été Téléchargé par l'utilisateur PowerShellNerd 28 février 2016, Partagez le lien avec vos amis et connaissances, sur youtube cette vidéo a déjà été regardée 25,428 fois et il a aimé 283 téléspectateurs. Bon visionnage!

625