Excel (PowerShell)

{{#widget:Widget:GoogleAdSenseSkyscraper}} Using PowerShell you can easily export a table of data to a CSV file...

Export-CSV FileToWriteTo.CSV -InputObject $DataTable -NoTypeInformation

... but you can't create an Excel file, directly. Which is a limitation if you want to create an automated (spreadsheet) report for anybody that's incapable of digesting information that's not nicely presented. To do so, requires direct automation of Excel...

Limitations

Microsoft Office applications are not built for background automation. It can be achieved, but there are some gotchas. Most notably when it comes to scheduling automation to run outside of an interactive user session. Which means you spend time crafting a wonderful script that creates a beautifully formatted spreadsheet; but which fails to complete properly when run as a scheduled task when you're not logged into the machine its running from. It also means that every once in a blue moon, it can still fail for no obvious reason.

• See MS KB 257757 - Considerations for server-side Automation of Office for further info.
  • server-side includes anything run outside of an interactive (logged in to the GUI) session, be it on a server, desktop, laptop or other.

Unable to Save Workaround

At the final stage of automation, when your script comes to attempt to save the spreadsheet it fails with an unimaginative Exception calling “SaveAs” with “1″ argument(s): “SaveAs method of Workbook class failed” error. Creating either of the following files resolves the error...

• C:\Windows\System32\config\systemprofile\Desktop
  • For 32 bit only or 64 bit only systems
• C:\Windows\SysWOW64\config\systemprofile\Desktop
  • For mixed systems where 32 bit Excel is running on 64 bit OS

This error is probably specific to Excel 2007 and later (Excel 2010 is affected), running on Windows 7, 2008 and later

• Source: TechDecode: Gotcha! – Office 2007 Automation with Powershell in Windows 2008

Create From CSV

Whilst its not necessary to create a CSV as an intermediate step, it can be beneficial to do so because...

• Its easier to create/maintain your script as you can see whats going on with the data
• When the Excel automation fails you can attempt to repeat or complete manually, rather than re-running the data collection/creation part.

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