Out-Excel

PowerShell has an excellent Export-CSV cmdlet, but nothing like a cmdlet to output directly to Excel.

This is no biggy for most, as there are plenty ways to get the results of Export-CSV and put them in an Excel-file, using common Office automation.

But I had an issue with Excel-automation, as described in kb320369:

If the client computer runs the English version of Excel and the locale for the current user is configured for a language other than English, Excel will try to locate the language pack for the configured language.

If the language pack is not found, an error "Old format or invalid type library" is reported.

The KB lists 4 different workarounds for this bug.

1. Install the appropriate Multilingual User Interface Pack
2. Set the CultureInfo prior to calling the Excel method.
3. Copy excel.exe to xllex.dll
4. Use the InvokeMember method, specifying a CultureInfo

As the first 3 options were not applicable in my case, I decided to write a PowerShell module with some functions that all use the [System.__ComObject].InvokeMember-method with an optional culture-object. 

This module Out-Excel.psm1 exposes the following functions:

• Out-Excel
• Test-ExcelFile
• Invoke-ExcelMethod
• Get-ExcelProperty
• Set-ExcelProperty
• Remove-ComObject
• Remove-Garbage
• SheetExists
• Get-SheetNames
• ExcelIsInUse

The following functions are fully documented, so Get-Help <functionname> displays syntax and examples:

---

Syntax Out-Excel
    Out-Excel [[-ExcelFile] <String>] [[-SheetName] <String>] [[-Property] <String[]>] [[-Culture] <String>] [-Raw] [-NoForce] [[-InputObject] <Object>] [<CommonParameters>]

Example
Get-Service | select -last 3 | Out-Excel -ExcelFile "C:\Temp\Services.xls" -SheetName "Last3"

---

Syntax Invoke-ExcelMethod
Invoke-ExcelMethod [-Object] <Object> [-Method] <String> [[-Parameters] <Object>] [[-Culture] <String>] [<CommonParameters>]

Example
Invoke-ExcelMethod $sheet "UsedRange.EntireColumn.AutoFit"

---

Syntax Get-ExcelProperty
Get-ExcelProperty [-object] <Object> [-property] <String> [[-culture] <String>] [<CommonParameters>]

Example
Get-ExcelProperty $sheet "UsedRange.Interior.ColorIndex"

---

Syntax Set-ExcelProperty
Set-ExcelProperty [-object] <Object> [-property] <String> [[-parameters] <Object>] [[-culture] <String>] [<CommonParameters>]

Example
Set-ExcelProperty $sheet "Name" "Last3"

---

Syntax Test-ExcelFile
Test-ExcelFile [-ExcelFile] <String> [[-SheetName] <String>] [[-RequiredColumns] <String[]>] [<CommonParameters>]

Example
$ExcelFile = "D:\Work\TSM\Scripts\InstallTSM\InstallManager\Data\Test.xls"
$sheet = "Servers"
$RequiredColumns = ("Environment", "Domain", "CI Name")
if (Test-ExcelFile $ExcelFile $sheet $RequiredColumns){
    # do some stuff
}
else {
    Write-Warning "failed the test"
}