________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
function Out-Excel{
<#
.SYNOPSIS
Writes data to Excel-sheet.
.DESCRIPTION
Writes data to Excel-sheet, using appropriate column headers.
.PARAMETER ExcelFile
Full path to resulting Excel-file
File-extension determines Excel-version.
Defaults to C:\Temp\Results.xls (which is Excel97-2003)
.PARAMETER SheetName
Specifies the sheet name.
Defaults to Results
.PARAMETER property
Specifies the properties to report.
Defaults to all properties.
.PARAMETER Culture
Specifies the culture to use.
Defaults to [System.Threading.Thread]::CurrentThread.CurrentUICulture.Name
Normally, 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 is reported.
This function circumvents this issue () by calling [System.__ComObject].InvokeMember-method with a culture-object.
.PARAMETER raw
Switch to return raw objects
.PARAMETER NoForce
Switch to prevent overwrite of existing sheet.
Default is to overwrite existing sheet.
.NOTES
Written by FurBall - FurBall Productions
.EXAMPLE
$LogFolder = "C:\Temp"
$OutputFile = Join-Path $LogFolder "ProcessesWith500PlusHandles.xls"
Get-Process | where {$_.handles -gt 500} | Out-Excel -property name, handles -ExcelFile $OutputFile
.EXAMPLE
Get-Service | select -last 3 | Out-Excel -ExcelFile "C:\Temp\Services.xls" -SheetName "Last3" -NoForce
.EXAMPLE
Get-Service | where {$_.NAme -match "sp"}| Out-Excel -Culture "nl-NL"
.LINK
http://support.microsoft.com/kb/320369/en-us
#>
param (
[Parameter(ValueFromPipeline=$false,ValueFromPipelineByPropertyName=$false, Mandatory=$false)][string]$ExcelFile = "C:\Temp\Results.xls",
[Parameter(ValueFromPipeline=$false,ValueFromPipelineByPropertyName=$false, Mandatory=$false)][string]$SheetName = "Results",
[Parameter(ValueFromPipeline=$false,ValueFromPipelineByPropertyName=$false, Mandatory=$false)][string[]]$Property,
[Parameter(ValueFromPipeline=$false,ValueFromPipelineByPropertyName=$false, Mandatory=$false)][string]$Culture = [System.Threading.Thread]::CurrentThread.CurrentUICulture.Name,
[Parameter(ValueFromPipeline=$false,ValueFromPipelineByPropertyName=$false, Mandatory=$false)][switch]$Raw,
[Parameter(ValueFromPipeline=$false,ValueFromPipelineByPropertyName=$false, Mandatory=$false)][switch]$NoForce,
[Parameter(ValueFromPipeline=$true,ValueFromPipelineByPropertyName=$true, Mandatory=$false)]$InputObject
)
begin {
$bContinue = $true
# start Excel
$Excel = New-object -com Excel.Application
if ($Excel -eq $null){
Write-Warning "no Excel available"
$bContinue = $false
return
}
# Turn off screen updating so script runs faster
Set-ExcelProperty $Excel Visible $false -Culture $Culture
Set-ExcelProperty $Excel ScreenUpdating $false -Culture $Culture
Set-ExcelProperty $Excel DisplayAlerts $false -Culture $Culture
if (!(Test-Path $ExcelFile)) {
# open a new workbook
$Workbook = Invoke-ExcelMethod $Excel.Workbooks Add -Culture $Culture
# get the 1st sheet
$Sheet = $Workbook.Worksheets.Item(1)
# rename sheet
Set-ExcelProperty $Sheet name $SheetName -Culture $Culture
# activate the sheet
Invoke-ExcelMethod $Sheet Activate -Culture $Culture
}
else{
if (ExcelIsInUse $ExcelFile){
Write-Warning "$ExcelFile is in use"
$bContinue = $false
return
}
else{
if (!(SheetExists $ExcelFile $SheetName)){
# open existing Excelfile
$workbook = Invoke-ExcelMethod $excel.workbooks open $excelfile -Culture $Culture
# add a sheet
$Sheet = Invoke-ExcelMethod $Workbook "worksheets.add" -Culture $Culture
# rename sheet
Set-ExcelProperty $Sheet name $SheetName -Culture $Culture
# activate the sheet
Invoke-ExcelMethod $Sheet Activate -Culture $Culture
}
else{
if ($NoForce){
Write-Warning "$ExcelFile already exists and contains a sheet named $sheetname"
$bContinue = $false
return
}
else{
# open existing Excelfile
$workbook = Invoke-ExcelMethod $Excel "Workbooks.Open" $ExcelFile -Culture $Culture
# get sheets
$worksheets = Get-ExcelProperty $workbook "worksheets" -Culture $Culture
# delete specific worksheet
for ($i = 0 ; $i -lt $worksheets.Count ; $i ++){
if ((Get-ExcelProperty $worksheets[$i] Name -Culture $Culture) -like $SheetName){
Invoke-ExcelMethod $worksheets[$i] Delete -Culture $Culture
}
}
# add a sheet
$Sheet = Invoke-ExcelMethod $Workbook "worksheets.add" -Culture $Culture
# rename sheet
Set-ExcelProperty $Sheet name $SheetName -Culture $Culture
# activate the sheet
Invoke-ExcelMethod $Sheet Activate -Culture $Culture
}
}
}
}
# initialize row counter and empty hashtable to hold column headers
$row = 1
$headers = @{}
}
process {
if (!$bContinue){
return
}
else{
if ($_ -eq $null) {return}
# when we receive the object, we build the header table
if (-not $property) {
# if no list of properties was provided, we build one from the object’s properties
$property=@()
if ($raw) {
$_.properties.PropertyNames | ForEach-Object {$property += @($_)}
}
else {
$_.PsObject.get_properties() | ForEach-Object {$property += @($_.Name.ToString())}
}
}
if ($row -eq 1) {
$Column = 1
foreach ($header in $property) {
$headers[$header] = $Column
$Sheet.Cells.Item($row,$Column).FormulaLocal = $header
$Column ++
}
# set some formatting values for the headers
Set-ExcelProperty $Sheet "UsedRange.Interior.ColorIndex" "19" -Culture $Culture
Set-ExcelProperty $Sheet "UsedRange.Font.ColorIndex" "11" -Culture $Culture
Set-ExcelProperty $Sheet "UsedRange.Font.Bold" $true -Culture $Culture
Set-ExcelProperty $Sheet "UsedRange.HorizontalAlignment" "7" -Culture $Culture
}
$row ++
foreach ($header in $property) {
# NOTE: next line of code is not the evaluation of a comparison, but the evaluation of an assignment!!
if ($thisColumn = $headers[$header]) {
if ($raw) {
$Sheet.Cells.Item($row,$thisColumn).FormulaLocal = [string]$_.properties.$header
}
else {
$Sheet.Cells.Item($row,$thisColumn).FormulaLocal = [string]$_.$header
}
}
}
}
}
end {
if ($bContinue){
if ($row -gt 1) { Invoke-ExcelMethod $Sheet "UsedRange.EntireColumn.AutoFit" -Culture $Culture }
# save as Excel 97-2003 (works with Excel 2010, but not with 2003)
if (($ExcelFile.endswith(".xls")) -and ($Excel.version -ge "14.0")){
$arguments = $ExcelFile, "56"
Invoke-ExcelMethod $Workbook SaveAs $arguments -Culture $Culture
}
else {
Invoke-ExcelMethod $Workbook SaveAs $ExcelFile -Culture $Culture
}
Invoke-ExcelMethod $Workbook Close -Culture $Culture
}
$Excel.Quit()
$PreviousErrorActionPreference = $ErrorActionPreference
$ErrorActionPreference = "SilentlyContinue"
Remove-Variable worksheets
Remove-Variable sheet
Remove-Variable workbook
$ErrorActionPreference = $PreviousErrorActionPreference
#get rid of the remaining Com-object, left behind despite .Quit()
Remove-ComObject $Excel
Remove-Variable Excel
}
}
function Remove-ComObject{
<#
.SYNOPSIS
Releases COM-object
.DESCRIPTION
Releases specified COM-object
Using .Quit() on an Excel.Application-object does not get rid of the process.
Use this function to get rid of that process.
.PARAMETER Object
Excel-object
.NOTES
.EXAMPLE
Remove-ComObject $Excel
.LINK
#>
param(
[Parameter(ValueFromPipeline=$false,ValueFromPipelineByPropertyName=$false, Mandatory=$true)][System.__ComObject]$ComObject
)
Write-Verbose "Remove-ComObject $($ComObject.Name)"
$hide = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($ComObject)
$hide = [System.GC]::Collect()
$hide = [System.GC]::WaitForPendingFinalizers()
}
function ExcelIsInUse( [string] $ExcelFile ){
<#
.SYNOPSIS
Checks if Excel is in use.
.DESCRIPTION
Returns True if specified Excel-file is in use.
.PARAMETER Object
Excel-object
.NOTES
Written by FurBall - FurBall Productions
Requires function Remove-ComObject
.EXAMPLE
$MyFile = "C:\Temp\Results.xls"
if (ExcelIsInUse $MyFile){
Write-Warning "$MyFile is in use"
return
}
.LINK
#>
$objXLS = New-Object -comobject Excel.Application
# Turn off screen updating so script runs faster
$hide = Set-ExcelProperty $objXLS Visible $false
$hide = Set-ExcelProperty $objXLS DisplayAlerts $false
$hide = Invoke-ExcelMethod $objXLS "Workbooks.Open" $ExcelFile
$bReadOnly = Get-ExcelProperty $objXLS "ActiveWorkbook.ReadOnly"
If ($bReadOnly ){
$returnvalue = $true
}
else{
$returnvalue = $false
}
$hide = Invoke-ExcelMethod $objXLS.ActiveWorkbook Close
$hide = $objXLS.Quit()
#get rid of the remaining Com-object, left behind despite .Quit()
$hide = Remove-ComObject $objXLS
$hide = Remove-Variable objXLS
return $returnvalue
}
function Get-SheetNames{
Param(
[Parameter(ValueFromPipeline=$false,ValueFromPipelineByPropertyName=$false, Mandatory=$true)][string]$ExcelFile
)
$ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$ExcelFile;Extended Properties=Excel 8.0;"
$Conn = new-object System.Data.OleDb.OleDbConnection($connString)
$conn.open()
$return = $Conn.GetOleDbSchemaTable([System.Data.OleDb.OleDbSchemaGuid]::tables,$null) |select TABLE_NAME | where{($_.TABLE_NAME).endswith('$')} | foreach{
($_.TABLE_NAME).tostring()
}
$conn.Close()
return $return
}
function SheetExists{
#pass SheetName without trailing $
Param(
[Parameter(ValueFromPipeline=$false,ValueFromPipelineByPropertyName=$false, Mandatory=$true)][string]$ExcelFile,
[Parameter(ValueFromPipeline=$false,ValueFromPipelineByPropertyName=$false, Mandatory=$true)][string]$SheetName
)
[bool]$SheetExists = $false
Get-SheetNames $ExcelFile | foreach {
if ($_ -like "$SheetName`$"){$SheetExists = $true}
}
return $SheetExists
}
function Test-ExcelFile{
<#
.SYNOPSIS
Checks if Excel-file meets requirements
.DESCRIPTION
Checks if Excel-file meets requirements
- checks if file exists
- checks that file is not in use
- option: checks if required sheet exists
- option: checks if all required columns exist
.PARAMETER ExcelFile
Full name of the Excel-file
.PARAMETER SheetName
Name of the sheet
pass SheetName without trailing $
.PARAMETER RequiredColumns
Name(s) of required column(s)
.NOTES
Written by FurBall - FurBall Productions
.EXAMPLE
$ExcelFile = "D:\Work\TSM\Scripts\InstallTSM\InstallManager\Data\Test.xls"
$sheet = "Servers"
$RequiredColumns = ("Behind Firewall", "Environment", "Domain", "CI Name", "ChangeNr", "Date", "Time", "InstallState", "StateDetails", "ErrorDetails", "Action", "Commentaar", "Backup Control", "Backup Controled By", "Backup Medium", "CheckControls", "CI Accepted", "CI Monitor", "Cluster", "Instance State", "Management Domain", "OS CINAME BASELINE", "Remote Control Type", "ROOM LOCATION", "RACK SUB LOCATION")
if (Test-ExcelFile $ExcelFile $sheet $RequiredColumns){
# do some stuff
}
else {
Write-Warning "failed the test"
}
.LINK
#>
Param(
[Parameter(ValueFromPipeline=$false,ValueFromPipelineByPropertyName=$false, Mandatory=$true)][string]$ExcelFile,
[Parameter(ValueFromPipeline=$false,ValueFromPipelineByPropertyName=$false, Mandatory=$false)][string]$SheetName = "",
[Parameter(ValueFromPipeline=$false,ValueFromPipelineByPropertyName=$false, Mandatory=$false)][string[]]$RequiredColumns = @()
)
# ADO-Constants for Excel
$adoLockOptimistic = 3
$adoOpenStatic = 3
$adoCmdText = 1
$returnvalue = $false
# check if file exists
if (!(Test-Path $ExcelFile)){Write-Warning "missing file: $ExcelFile"
}
else{
# check if file is in use
if (ExcelIsInUse $ExcelFile){Write-Warning "file in use: $ExcelFile"
}
else{
if ($SheetName -eq ""){
# no check for sheet is needed and previous checks were ok, so we return True
$returnvalue = $true
}
else{
# check if required sheet exists
if (!(SheetExists $ExcelFile $SheetName)){Write-Warning "missing sheet $SheetName in $ExcelFile"
}
else{
#calling SheetExists does not leave an open connection, so we open it here
$adoConnection = New-Object -comobject ADODB.Connection
$ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$ExcelFile;Extended Properties=Excel 8.0;"
$hide = $adoConnection.Open($ConnString)
if (!$?){
Write-Warning "error connecting to $ExcelFile"
break
}
else{
#check if all required columns exist
$Sql = "Select * FROM [$SheetName`$] WHERE 1=0"
$adoRecordset = New-Object -comobject ADODB.Recordset
$hide = $adoRecordset.Open($Sql, $adoConnection, $adoOpenStatic, $adoLockOptimistic)
$MissingColumns = Get-MissingColumns $adoRecordset $RequiredColumns
if ($MissingColumns){
Write-Warning "missing these columns in $ExcelFile"
Write-Warning $MissingColumns
}
else{
$returnvalue = $true
}
$hide = $adoRecordset.Close()
$hide = $adoConnection.Close()
}
}
}
}
}
return $returnvalue
}
function Get-ColumnNames{
Param(
[Parameter(ValueFromPipeline=$false,ValueFromPipelineByPropertyName=$false, Mandatory=$true)]$recordset)
if ($recordset.fields.count -gt 0){
For($counter = 0; $counter -lt $recordset.fields.count; $counter++){
$recordset.Fields.Item($counter).Name
}
}
}
function Get-MissingColumns{
Param(
[Parameter(ValueFromPipeline=$false,ValueFromPipelineByPropertyName=$false, Mandatory=$true)]$recordset,
[Parameter(ValueFromPipeline=$false,ValueFromPipelineByPropertyName=$false, Mandatory=$false)][string[]]$RequiredColumns = @()
)
$MissingColumns = @()
$ExistingColumns = Get-ColumnNames $recordset
foreach($ColumNameToLookFor In $RequiredColumns){
$bColumnFound = $false
$ExistingColumns | foreach{
if ($_ -like $ColumNameToLookFor){
$bColumnFound = $true
}
}
if (!$bColumnFound){$MissingColumns += $ColumNameToLookFor}
}
return $MissingColumns
}
function Invoke-ExcelMethod{
<#
.SYNOPSIS
Invokes Excel-method.
.DESCRIPTION
Invokes Excel-method, using a [System.__ComObject].InvokeMember-method with a culture-object.
.PARAMETER Object
Excel-object
.PARAMETER Method
Method to invoke
.PARAMETER Parameters
Parameters for the method.
Multiple parameters must be passed as array.
.PARAMETER Culture
Specifies the culture to use.
Defaults to [System.Threading.Thread]::CurrentThread.CurrentUICulture.Name
Normally, 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 is reported.
This function circumvents this issue () by calling [System.__ComObject].InvokeMember-method with a culture-object.
.NOTES
Written by FurBall - FurBall Productions
.EXAMPLE
Invoke-ExcelMethod $sheet "UsedRange.EntireColumn.AutoFit" "nl-NL"
or
$params = @{
Object = $sheet
Method = "UsedRange.EntireColumn.AutoFit"
}
Invoke-ExcelMethod @params "nl-NL"
.EXAMPLE
# Open existing Excel-file, delete and activate sheets
# instantiate Excel-object
$Excel = New-object -com Excel.Application
# turn off screen updating so script runs faster
Set-ExcelProperty $Excel Visible $false
Set-ExcelProperty $Excel ScreenUpdating $false
# turn off alerts and force deletes/overwrites
Set-ExcelProperty $Excel DisplayAlerts $false
# open existing Excelfile
$workbook = Invoke-ExcelMethod $excel "workbooks.open" $excelfile
# get sheets
$worksheets = Get-ExcelProperty $workbook "worksheets"
# delete specific worksheet
$SheetToDelete = "OldResults"
for ($i = 0 ; $i -lt $worksheets.Count ; $i ++){
if ((Get-ExcelProperty $worksheets[$i] Name) -like $SheetToDelete){
Invoke-ExcelMethod $worksheets[$i] Delete
}
}
# activate specific worksheet
$WantedSheet = "Results"
for ($i = 0 ; $i -lt $worksheets.Count ; $i ++){
if ((Get-ExcelProperty $worksheets[$i] Name) -like $WantedSheet){
Invoke-ExcelMethod $worksheets[$i] Activate
# and get a reference to it
$Sheet = $Worksheets[$i]
}
}
# change value of a cell
$row = 1
$Column = 1
$Sheet.Cells.Item($row,$Column).FormulaLocal = "New ColumnName"
# save and close Excelfile
Invoke-ExcelMethod $Workbook SaveAs $ExcelFile
Invoke-ExcelMethod $Workbook Close
$Excel.Quit()
#get rid of the remaining Com-object, left behind despite .Quit()
Remove-ComObject $Excel
.EXAMPLE
# Open new Excel-file, rename, add and format sheets
# instantiate Excel-object
$Excel = New-object -com Excel.Application
# turn off screen updating so script runs faster
Set-ExcelProperty $Excel Visible $false
Set-ExcelProperty $Excel ScreenUpdating $false
# turn off alerts and force deletes/overwrites
Set-ExcelProperty $Excel DisplayAlerts $false
# add workbook
$Workbook = Invoke-ExcelMethod $Excel.Workbooks Add
# get sheets
$worksheets = Get-ExcelProperty $workbook "worksheets"
# rename sheet
Set-ExcelProperty $worksheets[0] name "Results"
# activate the worksheet
Invoke-ExcelMethod $worksheets[0] Activate
# and get a reference to it
$Sheet = $Worksheets[0]
# change value of a cell
$row = 1
$Column = 1
$Sheet.Cells.Item($row,$Column).FormulaLocal = "First column"
$Sheet.Cells.Item($row,2).FormulaLocal = "Second column"
# autofit columns
Invoke-ExcelMethod $sheet "UsedRange.EntireColumn.AutoFit"
# set some formatting values for the headers
Set-ExcelProperty $Sheet "UsedRange.Interior.ColorIndex" "19"
Set-ExcelProperty $Sheet "UsedRange.Font.ColorIndex" "11"
Set-ExcelProperty $Sheet "UsedRange.Font.Bold" $true
Set-ExcelProperty $Sheet "UsedRange.HorizontalAlignment" "7"
# add a sheet
$newsheet = Invoke-ExcelMethod $workbook.worksheets Add
Set-ExcelProperty $newsheet name "EmptySheet"
# save and close Excelfile
Invoke-ExcelMethod $Workbook SaveAs $ExcelFile
Invoke-ExcelMethod $Workbook Close
$Excel.Quit()
#get rid of the remaining Com-object, left behind despite .Quit()
Remove-ComObject $Excel
.LINK
http://support.microsoft.com/kb/320369/en-us
http://www.fourcats.nl/Out-Excel.html
#>
Param(
[Parameter(ValueFromPipeline=$false,ValueFromPipelineByPropertyName=$true, Mandatory=$true)][object]$Object,
[Parameter(ValueFromPipeline=$false,ValueFromPipelineByPropertyName=$true, Mandatory=$true)][string]$Method,
[Parameter(ValueFromPipeline=$false,ValueFromPipelineByPropertyName=$true, Mandatory=$false)]$Parameters,
[Parameter(ValueFromPipeline=$false,ValueFromPipelineByPropertyName=$true, Mandatory=$false)][string]$Culture = [System.Threading.Thread]::CurrentThread.CurrentUICulture.Name
)
Write-Verbose "Invoke-ExcelMethod $method` $parameters for object $($object.Name) with culture $culture"
$MethodsToTreatDifferently = @{
Open = ""
Save = ""
SaveAs = ""
SaveAsXMLData = ""
SaveCopyAs = ""
SaveWorkSpace = ""
MergeWorkbook = ""
}
if ($parameters){
if (!($parameters -is [array])){
[array]$parameters = @($parameters)
}
}
$CultureObject = [System.Globalization.CultureInfo]$culture
$binding = "System.Reflection.BindingFlags" -as [type]
if (!($MethodsToTreatDifferently.Contains($method)) ){
$arrProperties = $method.Split(".")
$method = $arrProperties[-1]
for ($i = 0 ; $i -lt $arrProperties.Count -1 ; $i ++){
$object = [System.__ComObject].InvokeMember($arrProperties[$i], $binding::GetProperty, $null, $object, $null, $CultureObject)
}
}
[System.__ComObject].InvokeMember($method, $binding::InvokeMethod, $null, $object, $parameters, $CultureObject)
}
function Get-ExcelProperty{
<#
.SYNOPSIS
Returns property of Excel-object.
.DESCRIPTION
Returns property of Excel-object, using a [System.__ComObject].InvokeMember-method with a culture-object.
.PARAMETER Object
Excel-object
.PARAMETER Property
Property to get.
.PARAMETER Culture
Specifies the culture to use.
Defaults to [System.Threading.Thread]::CurrentThread.CurrentUICulture.Name
Normally, 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 is reported.
This function circumvents this issue () by calling [System.__ComObject].InvokeMember-method with a culture-object.
.NOTES
Written by FurBall - FurBall Productions
.EXAMPLE
Get-ExcelProperty $sheet "UsedRange.Interior.ColorIndex"
.EXAMPLE
$params = @{
Object = $sheet
Property = "Name"
}
Get-ExcelProperty @params "nl-NL"
.EXAMPLE
# Open existing Excel-file, delete and activate sheets
# instantiate Excel-object
$Excel = New-object -com Excel.Application
# turn off screen updating so script runs faster
Set-ExcelProperty $Excel Visible $false
Set-ExcelProperty $Excel ScreenUpdating $false
# turn off alerts and force deletes/overwrites
Set-ExcelProperty $Excel DisplayAlerts $false
# open existing Excelfile
$workbook = Invoke-ExcelMethod $excel "workbooks.open" $excelfile
# get sheets
$worksheets = Get-ExcelProperty $workbook "worksheets"
# delete specific worksheet
$SheetToDelete = "OldResults"
for ($i = 0 ; $i -lt $worksheets.Count ; $i ++){
if ((Get-ExcelProperty $worksheets[$i] Name) -like $SheetToDelete){
Invoke-ExcelMethod $worksheets[$i] Delete
}
}
# activate specific worksheet
$WantedSheet = "Results"
for ($i = 0 ; $i -lt $worksheets.Count ; $i ++){
if ((Get-ExcelProperty $worksheets[$i] Name) -like $WantedSheet){
Invoke-ExcelMethod $worksheets[$i] Activate
# and get a reference to it
$Sheet = $Worksheets[$i]
}
}
# change value of a cell
$row = 1
$Column = 1
$Sheet.Cells.Item($row,$Column).FormulaLocal = "New ColumnName"
# save and close Excelfile
Invoke-ExcelMethod $Workbook SaveAs $ExcelFile
Invoke-ExcelMethod $Workbook Close
$Excel.Quit()
#get rid of the remaining Com-object, left behind despite .Quit()
Remove-ComObject $Excel
.EXAMPLE
# Open new Excel-file, rename, add and format sheets
# instantiate Excel-object
$Excel = New-object -com Excel.Application
# turn off screen updating so script runs faster
Set-ExcelProperty $Excel Visible $false
Set-ExcelProperty $Excel ScreenUpdating $false
# turn off alerts and force deletes/overwrites
Set-ExcelProperty $Excel DisplayAlerts $false
# add workbook
$Workbook = Invoke-ExcelMethod $Excel.Workbooks Add
# get sheets
$worksheets = Get-ExcelProperty $workbook "worksheets"
# rename sheet
Set-ExcelProperty $worksheets[0] name "Results"
# activate the worksheet
Invoke-ExcelMethod $worksheets[0] Activate
# and get a reference to it
$Sheet = $Worksheets[0]
# change value of a cell
$row = 1
$Column = 1
$Sheet.Cells.Item($row,$Column).FormulaLocal = "First column"
$Sheet.Cells.Item($row,2).FormulaLocal = "Second column"
# autofit columns
Invoke-ExcelMethod $sheet "UsedRange.EntireColumn.AutoFit"
# set some formatting values for the headers
Set-ExcelProperty $Sheet "UsedRange.Interior.ColorIndex" "19"
Set-ExcelProperty $Sheet "UsedRange.Font.ColorIndex" "11"
Set-ExcelProperty $Sheet "UsedRange.Font.Bold" $true
Set-ExcelProperty $Sheet "UsedRange.HorizontalAlignment" "7"
# add a sheet
$newsheet = Invoke-ExcelMethod $workbook.worksheets Add
Set-ExcelProperty $newsheet name "EmptySheet"
# save and close Excelfile
Invoke-ExcelMethod $Workbook SaveAs $ExcelFile
Invoke-ExcelMethod $Workbook Close
$Excel.Quit()
#get rid of the remaining Com-object, left behind despite .Quit()
Remove-ComObject $Excel
.LINK
http://support.microsoft.com/kb/320369/en-us
#>
Param(
[Parameter(ValueFromPipeline=$false,ValueFromPipelineByPropertyName=$true, Mandatory=$true)][object]$object,
[Parameter(ValueFromPipeline=$false,ValueFromPipelineByPropertyName=$true, Mandatory=$true)][string]$property,
[Parameter(ValueFromPipeline=$false,ValueFromPipelineByPropertyName=$true, Mandatory=$false)][string]$culture = [System.Threading.Thread]::CurrentThread.CurrentUICulture.Name
)
Write-Verbose "Get-ExcelProperty $property for object $($object.Name) with culture $culture"
$CultureObject = [System.Globalization.CultureInfo]$culture
$binding = "System.Reflection.BindingFlags" -as [type]
$arrProperties = $property.Split(".")
$property = $arrProperties[-1]
for ($i = 0 ; $i -lt $arrProperties.Count -1 ; $i ++){
$object = [System.__ComObject].InvokeMember($arrProperties[$i], $binding::GetProperty, $null, $object, $null, $CultureObject)
}
return [System.__ComObject].InvokeMember($property, $binding::GetProperty, $null, $object, $null, $CultureObject)
}
function Set-ExcelProperty{
<#
.SYNOPSIS
Changes property-value of Excel-object.
.DESCRIPTION
Changes property-value of Excel-object, using a [System.__ComObject].InvokeMember-method with a culture-object.
.PARAMETER Object
Excel-object
.PARAMETER Property
Property to change.
.PARAMETER Parameters
Parameters for the property.
Multiple parameters must be passed as array.
.PARAMETER Culture
Specifies the culture to use.
Defaults to [System.Threading.Thread]::CurrentThread.CurrentUICulture.Name
Normally, 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 is reported.
This function circumvents this issue () by calling [System.__ComObject].InvokeMember-method with a culture-object.
.NOTES
Written by FurBall - FurBall Productions
.EXAMPLE
Set-ExcelProperty $sheet "UsedRange.Interior.ColorIndex" "19"
.EXAMPLE
$params = @{
Object = $sheet
Property = "Name"
Parameters = "Hoera"
}
Set-ExcelProperty @params "nl-NL"
.EXAMPLE
# Open existing Excel-file, delete and activate sheets
# instantiate Excel-object
$Excel = New-object -com Excel.Application
# turn off screen updating so script runs faster
Set-ExcelProperty $Excel Visible $false
Set-ExcelProperty $Excel ScreenUpdating $false
# turn off alerts and force deletes/overwrites
Set-ExcelProperty $Excel DisplayAlerts $false
# open existing Excelfile
$workbook = Invoke-ExcelMethod $excel "workbooks.open" $excelfile
# get sheets
$worksheets = Get-ExcelProperty $workbook "worksheets"
# delete specific worksheet
$SheetToDelete = "OldResults"
for ($i = 0 ; $i -lt $worksheets.Count ; $i ++){
if ((Get-ExcelProperty $worksheets[$i] Name) -like $SheetToDelete){
Invoke-ExcelMethod $worksheets[$i] Delete
}
}
# activate specific worksheet
$WantedSheet = "Results"
for ($i = 0 ; $i -lt $worksheets.Count ; $i ++){
if ((Get-ExcelProperty $worksheets[$i] Name) -like $WantedSheet){
Invoke-ExcelMethod $worksheets[$i] Activate
# and get a reference to it
$Sheet = $Worksheets[$i]
}
}
# change value of a cell
$row = 1
$Column = 1
$Sheet.Cells.Item($row,$Column).FormulaLocal = "New ColumnName"
# save and close Excelfile
Invoke-ExcelMethod $Workbook SaveAs $ExcelFile
Invoke-ExcelMethod $Workbook Close
$Excel.Quit()
#get rid of the remaining Com-object, left behind despite .Quit()
Remove-ComObject $Excel
.EXAMPLE
# Open new Excel-file, rename, add and format sheets
# instantiate Excel-object
$Excel = New-object -com Excel.Application
# turn off screen updating so script runs faster
Set-ExcelProperty $Excel Visible $false
Set-ExcelProperty $Excel ScreenUpdating $false
# turn off alerts and force deletes/overwrites
Set-ExcelProperty $Excel DisplayAlerts $false
# add workbook
$Workbook = Invoke-ExcelMethod $Excel.Workbooks Add
# get sheets
$worksheets = Get-ExcelProperty $workbook "worksheets"
# rename sheet
Set-ExcelProperty $worksheets[0] name "Results"
# activate the worksheet
Invoke-ExcelMethod $worksheets[0] Activate
# and get a reference to it
$Sheet = $Worksheets[0]
# change value of a cell
$row = 1
$Column = 1
$Sheet.Cells.Item($row,$Column).FormulaLocal = "First column"
$Sheet.Cells.Item($row,2).FormulaLocal = "Second column"
# autofit columns
Invoke-ExcelMethod $sheet "UsedRange.EntireColumn.AutoFit"
# set some formatting values for the headers
Set-ExcelProperty $Sheet "UsedRange.Interior.ColorIndex" "19"
Set-ExcelProperty $Sheet "UsedRange.Font.ColorIndex" "11"
Set-ExcelProperty $Sheet "UsedRange.Font.Bold" $true
Set-ExcelProperty $Sheet "UsedRange.HorizontalAlignment" "7"
# add a sheet
$newsheet = Invoke-ExcelMethod $workbook.worksheets Add
Set-ExcelProperty $newsheet name "EmptySheet"
# save and close Excelfile
Invoke-ExcelMethod $Workbook SaveAs $ExcelFile
Invoke-ExcelMethod $Workbook Close
$Excel.Quit()
#get rid of the remaining Com-object, left behind despite .Quit()
Remove-ComObject $Excel
.LINK
http://support.microsoft.com/kb/320369/en-us
#>
Param(
[Parameter(ValueFromPipeline=$false,ValueFromPipelineByPropertyName=$true, Mandatory=$true)][object]$object,
[Parameter(ValueFromPipeline=$false,ValueFromPipelineByPropertyName=$true, Mandatory=$true)][string]$property,
[Parameter(ValueFromPipeline=$false,ValueFromPipelineByPropertyName=$true, Mandatory=$false)]$parameters,
[Parameter(ValueFromPipeline=$false,ValueFromPipelineByPropertyName=$true, Mandatory=$false)][string]$culture = [System.Threading.Thread]::CurrentThread.CurrentUICulture.Name
)
Write-Verbose "Set-ExcelProperty $property` $parameters for object $($object.Name) with culture $culture"
if ($parameters){
if (!($parameters -is [array])){
[array]$parameters = @($parameters)
}
}
$CultureObject = [System.Globalization.CultureInfo]$culture
$binding = "System.Reflection.BindingFlags" -as [type]
$arrProperties = $property.Split(".")
$property = $arrProperties[-1]
for ($i = 0 ; $i -lt $arrProperties.Count -1 ; $i ++){
$object = [System.__ComObject].InvokeMember($arrProperties[$i], $binding::GetProperty, $null, $object, $null, $CultureObject)
}
return [System.__ComObject].InvokeMember($property, $binding::SetProperty, $null, $object, $parameters, $CultureObject)
}
function Remove-Garbage {
<#
.SYNOPSIS
Removes orphaned processes
.DESCRIPTION
Remove-ComObject releases a specified COM-object, but even this will not always remove the process.
Use Remove-Garbage after the function that called Remove-ComObject to get rid of that process.
.EXAMPLE
Remove-Garbage
.LINK
#>
[System.GC]::Collect()
}
Set-Alias cg Remove-Garbage
Export-ModuleMember -function Out-Excel, Remove-ComObject, ExcelIsInUse, Test-ExcelFile, Invoke-ExcelMethod, Get-ExcelProperty, Set-ExcelProperty, Remove-Garbage, SheetExists, Get-SheetNames
