View source for Excel (PowerShell)

Using PowerShell you can easily export a table of data to a CSV file...
<source lang="powershell">
Export-CSV FileToWriteTo.CSV -InputObject $DataTable -NoTypeInformation
</source>
... 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 that creates a beautifully formatted spreadsheet, which then 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 [https://www.wikipedia.org/wiki/Blue_moon blue moon], it can still fail for no obvious reason.

* See [http://support.microsoft.com/kb/257757 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 <code>Exception calling “SaveAs” with “1″ argument(s): “SaveAs method of Workbook class failed”</code> error.  Creating either of the following files resolves the error...
* <code> C:\Windows\System32\config\systemprofile\Desktop </code>
** For 32 bit only or 64 bit only systems
* <code> C:\Windows\SysWOW64\config\systemprofile\Desktop </code>
** 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:''' [http://www.techdecode.com/wp/?p=58 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...
* Its easier to create/maintain your script
* 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.  
<source lang="powershell">
$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()
</source>

= Formatting =
{|class="vwikitable"
|- 
! 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 on 1st row
|-
| <code> $Excel.Rows.Item(1).HorizontalAlignment = -4108 </code> || Centre (horizontally) text on 1st row
|-
| <code> $Excel.Columns.Item(1).columnWidth = 12 </code>         || Make column 1 have a width of 12
|-
| <code> $Excel.Cells.EntireColumn.AutoFit() </code>             || Auto-fit all columns
|-
| <code> $Excel.Cells.EntireColumn.AutoFilter() </code>          || Auto-filter all columns
|-
| <code> $Excel.Columns.Borders.LineStyle = 1 </code>            || Give all cells a simple border
|}

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>

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

[[Category:PowerShell]]