2,187
edits
Difference between revisions of "Power Shell"
Jump to navigation
Jump to search
Added "Excel"
m (→With Attachments: Corrections) |
(Added "Excel") |
||
| Line 454: | Line 454: | ||
[void] $UDPclient.Send($packet, $packet.Length) | [void] $UDPclient.Send($packet, $packet.Length) | ||
write-debug "Wake-On-Lan magic packet of length $($packet.Length) sent to $macString" | write-debug "Wake-On-Lan magic packet of length $($packet.Length) sent to $macString" | ||
</source> | |||
== Excel == | |||
Basic example, converting an esxisting CSV file into a XLSX file... | |||
<source lang="powershell"> | |||
$INfile = "VMs.csv" | |||
$OUTfile = "VMs.xlsx" | |||
$Excel = New-Object -ComObject Excel.Application | |||
$Excel.Visible = $True | |||
$ExcelBook = $Excel.Workbooks.Add() | |||
$Excel.Cells.Item(1,1) = "VC" | |||
$Excel.Cells.Item(1,2) = "VM Name" | |||
$Excel.Cells.Item(1,3) = "Datastore" | |||
$CSVfile = Import-Csv -Path $INfile | |||
$row = 2 | |||
foreach($line in $CSVfile) { | |||
$Excel.Cells.Item($row,1) = $line.VC | |||
$Excel.Cells.Item($row,2) = $line.Name | |||
$Excel.Cells.Item($row,3) = $line.Datastore | |||
$row += 1 | |||
Write-Host "." -NoNewLine | |||
} | |||
$ExcelBook.SaveAs($OUTfile) | |||
$Excel.Quit() | |||
Remove-Variable -Name Excel | |||
[gc]::Collect() | |||
[gc]::WaitForPendingFinalizers() | |||
</source> | |||
=== Formating === | |||
{|cellpadding="1" cellspacing="0" border="1" | |||
|- style="background-color:#bbddff;" | |||
! Example !! Description | |||
|- | |||
| <code> $Excel.Rows.Item(1).Font.Bold = $True </code> || Make row 1 bold | |||
|- | |||
| <code> $Excel.Rows.Item(1).WrapText = $True </code> || Make row 1 wrap text (may affect row height) | |||
|- | |||
| <code> $Excel.Rows.Item(1).VerticalAlignment = -4108 </code> || Centre (vertically) text | |||
|- | |||
| <code> $Excel.Rows.Item(1).HorizontalAlignment = -4108 </code> || Centre (horizontally) text | |||
|- | |||
| <code> $Excel.Columns.Item(1).columnWidth = 12 </code> || Make column 1 have a width of 12 | |||
To freeze panes... | |||
<source lang="powershell"> | |||
[void]$Excel.Cells.Item(2,3).Select() # Select the appropriate cell to freeze around | |||
$Excel.ActiveWindow.FreezePanes = $True # Freeze | |||
</source> | </source> | ||