Scripting Games Commentary: III PowerShell and Excel
Some of the events had the production of a CSV file as the end result with a bonus point if you opened the file in Excel.
The quickest way and easiest way to open a CSV file in Excel is like this
Invoke-Item -Path chapt4.csv
This will work if you have the CSV file extension opened with Excel. If you don’t you need to fall back on opening Excel first
$xl = New-Object -ComObject "Excel.Application"
$xl.WorkBooks.Open("C:\Scripts\chapt4.csv")
$xl.Visible = $true
If at all possible create your data in a CSV file and then open in Excel. Trying to populate an Excel spread sheet from PowerShell is SLOOOOOOOOOOOOOOOOOW.
Be aware that if you want to work directly with Excel there is a bug in Excel 2007 and earlier.
You would expect to be able to do this
$xl = New-Object -comobject "Excel.Application"
$xl.visible = $true
$xlbooks =$xl.workbooks
$wkbk = $xlbooks.Add()
It works if your system is set to en-US culture otherwise it fails. In Excel 2010 it works for some cultures such as en-GB but still fails for others. The way round it is to open Excel like this
$xl = New-Object -Comobject "Excel.Application"
$xl.Visible = $true
$xlbooks =$xl.Workbooks
$newci = [System.Globalization.CultureInfo]"en-US"
$wkbk = $xlbooks.PSBase.GetType().InvokeMember("Add", [Reflection.BindingFlags]::InvokeMethod, $null, $xlbooks, $null, $newci)
Its slightly more painful but works for all versions and all cultures that I am aware of