About Pathological Scripter

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

One response to “About Pathological Scripter

  1. 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()

    }

    }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s