Excel (PowerShell)

Create From CSV

Basic example, converting an existing CSV file into a XLSX file...

$INfile = "VMs.csv"                                     # Has three columns, labelled VC, Name, Datastore
$OUTfile = "VMs.xlsx"

$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $False                                 # Set to $true if you want actually see the script interact with Excel

$ExcelBook = $Excel.Workbooks.Add()

$Excel.Rows.Item(1).Font.Bold = $true                   # Bold heading text
$Excel.Rows.Item(1).VerticalAlignment = -4108           # Centre (vertically) heading
$Excel.Rows.Item(1).HorizontalAlignment = -4108         # Centre (horizontally) heading

$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()

Formatting

Example	Description
$Excel.Rows.Item(1).Font.Bold = $True	Make row 1 bold
$Excel.Rows.Item(1).WrapText = $True	Make row 1 wrap text (may affect row height)
$Excel.Rows.Item(1).VerticalAlignment = -4108	Centre (vertically) text on 1st row
$Excel.Rows.Item(1).HorizontalAlignment = -4108	Centre (horizontally) text on 1st row
$Excel.Columns.Item(1).columnWidth = 12	Make column 1 have a width of 12
$Excel.Cells.EntireColumn.AutoFit()	Auto-fit all columns
$Excel.Cells.EntireColumn.AutoFilter()	Auto-filter all columns
$Excel.Columns.Borders.LineStyle = 1	Give all cells a simple border

To freeze panes...

[void]$Excel.Cells.Item(2,3).Select()           # Select the appropriate cell to freeze around
$Excel.ActiveWindow.FreezePanes = $True         # Freeze

Conditional Formatting

See http://robertrobelo.wordpress.com/2010/10/07/excels-conditional-formatting-and-powershell/#comments