function out-excel
param ([string[]]$property,[switch]$raw)
begin {
# start Excel and open a new workbook
$Excel = New-Object -Com Excel.Application
$Excel.visible = $True
$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)
# initialize our row counter and create an empty hashtable
# which will hold our column headers
$Row = 1
$HeaderHash = @{}
}
process {
if ($_ -eq $null) {return}
if ($Row -eq 1) {
# when we see the first object, we need to build our header table
if (-not $property) {
# if we haven’t been provided a list of properties,
# we’ll build one from the object’s properties
$property=@()
if ($raw) {
$_.properties.PropertyNames | %{$property+=@($_)}
} else {
$_.PsObject.get_properties() | % {$property += @($_.Name.ToString())}
}
}
$Column = 1
foreach ($header in $property) {
# iterate through the property list and load the headers into the first row
# also build a hash table so we can retrieve the correct column number
# when we process each object
$HeaderHash[$header] = $Column
$Sheet.Cells.Item($Row,$Column) = $header.toupper()
$Column ++
}
# set some formatting values for the first row
$WorkBook = $Sheet.UsedRange
$WorkBook.Interior.ColorIndex = 19
$WorkBook.Font.ColorIndex = 11
$WorkBook.Font.Bold = $True
$WorkBook.HorizontalAlignment = -4108
}
$Row ++
foreach ($header in $property) {
# now for each object we can just enumerate the headers, find the matching property
# and load the data into the correct cell in the current row.
# this way we don’t have to worry about missing properties
# or the “ordering” of the properties
if ($thisColumn = $HeaderHash[$header]) {
if ($raw) {
$Sheet.Cells.Item($Row,$thisColumn) = [string]$_.properties.$header
} else {
$Sheet.Cells.Item($Row,$thisColumn) = [string]$_.$header
}
}
}
}
end {
# now just resize the columns and we’re finished
if ($Row -gt 1) { [void]$WorkBook.EntireColumn.AutoFit() }
}
}
Pingback: out-this, out-that « Pathological Scripter
Pingback: PowerScripting Podcast - Episode 11 « PowerScripting Podcast
Pingback: How to Backup Twitter Friends with PowerShell and Excel Automatically | Fredrik Wall
Pingback: How to export content in Powershell to Excel ? | Jean-Benoit Paux's blog
Pingback: Returns Null » 2 Great Powershell Functions
Pingback: Visualizing and Automating SQL Server health with PowerShell and Excel | $hell Your Experience !!! #PowerShellLifeStyle