My intention is to collect/present useful examples of scripting using Microsoft's PowerShell. The definition of "useful" will probably depend mostly on what I'm working on at the time. But since I have a tendency to go off on tangents just because I find them interesting, who knows where this will lead…
—
Jim Holbach
Advertisements
I changed your fucntion out-excel to include the options save the excel to file automaticlly and quit the excel file.
this can be used by SQL Server developers or admin to put query results into Excel.
can you please (or if you want I’ll do it) send this file to “the powerShell Guy” and http://www.sqlservercentral.com/ web site.
# the script can be viewed at the end.
# sample Usage:
get-process | where {$_.handles -gt 500} | out-excel c:\7.xls -pr name,handles,path
#sample Usage for DB users
Get-SqlData ‘localhost’ ran ‘SELECT * FROM dbo.aaa’| out-excel c:\test.xls -pr a,b
#note that I used LibrarySmo.ps1
#see also :SQLPSX_1.6.1
again I belive it can be very useful for lots of people, thanks you for your script.
Ran
######################
#the new script starts here
. ./LibrarySmo.ps1
function out-excel {
param ($par_file_name=”c:\default_excel_file.xls” , [string[]]$property,[switch]$raw )
begin {
# start Excel and open a new workbook
$Excel = New-Object -Com Excel.Application
$Excel.visible = $True
# DisplayAlerts = $False means that Excel does not prompt even in a file already exist
$Excel.DisplayAlerts = $False
$Excel2 = $Excel.Workbooks.Add()
$Sheet = $Excel2.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() }
$Sheet.SaveAs($par_file_name)
$Excel2.close()
$Excel.quit()
}
}