Categories

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

Leave a Reply