Using Awesome Export-Excel Cmdlet (Part 1)

Doug Finke has created an awesome PowerShell module called ImportExcel which comes with all the commands you need to import and export data from and to Microsoft Excel. It does not require Office to be installed.

We can’t cover all the richness this module delivers, but in this tip, we’d like to provide you with the basics to get it up and running, and in follow-up tips we’ll deal about some formatting tricks.

To use the Excel commands, simply download and install the free module:

 
PS> Install-Module -Name ImportExcel -Scope CurrentUser -Force
 

When you do this for the first time, you may have to consent to downloading a “NuGet” open source DLL. Once the command finished, you now have access to a ton of new Excel commands, of which the most important is Export-Excel.

You now can pipe data directly to an Excel file, and provided Microsoft Office is installed, you can even open and show the results in Excel (Office is not required to create the .xlsx file).

Here is a simple example:

$Path = "$env:tempreport.xlsx"
Get-Process | Where-Object MainWindowTitle |
  Export-Excel -Path $path -ClearSheet -WorksheetName Processes -Show

It’s really as simple as this. Creating Excel files has never been easier. Here are a couple of points you want to keep in mind, though:

  • Use Select-Object to select the properties you want to export before you pipe the data to Export-Excel
  • Use -ClearSheet to clear previous data. If you omit this parameter, new data will be appended to existing data in the .xlsx file.
  • You may want to consider deleting an old .xlsx file manually before creating a new one with the same name. Else, there is the chance that Export-Excel takes into account existing settings from the old file.

Twitter This Tip! ReTweet this Tip!

Auto-Creating a List of HTTP Response Codes

In the previous example we looked at how numeric HTTP response codes can automatically be converted to descriptive text, simply by converting them to the type System.Net.HttpStatusCode.

 
PS> [System.Net.HttpStatusCode]500
InternalServerError
 

This works because System.Net.HttpStatusCode is a so-called “enumeration” and acts like a “lookup table”. You can easily dump all members of an enumeration, and for example create a nice table of HTTP response codes:

[Enum]::GetValues([System.Net.HttpStatusCode]) |
  ForEach-Object {
    [PSCustomObject]@{
        Code = [int]$_
        Description = $_.toString()
    }
  }

That’s all you need to get a table of the most common HTTP response codes:

 
Code Description                 
---- -----------                 
 100 Continue                    
 101 SwitchingProtocols          
 200 OK                          
 201 Created                     
 202 Accepted                    
 203 NonAuthoritativeInformation 
 204 NoContent                   
 205 ResetContent                
 206 PartialContent              
 300 MultipleChoices             
 300 MultipleChoices             
 301 MovedPermanently            
 301 MovedPermanently            
 302 Redirect                    
 302 Redirect                    
 303 SeeOther                    
 303 SeeOther                    
 304 NotModified                 
 305 UseProxy                    
 306 Unused                      
 307 TemporaryRedirect           
 307 TemporaryRedirect           
 400 BadRequest                  
 401 Unauthorized                
 402 PaymentRequired             
 403 Forbidden                   
 404 NotFound                    
 405 MethodNotAllowed            
 406 NotAcceptable               
 407 ProxyAuthenticationRequired 
 408 RequestTimeout              
 409 Conflict                    
 410 Gone                        
 411 LengthRequired              
 412 PreconditionFailed          
 413 RequestEntityTooLarge       
 414 RequestUriTooLong           
 415 UnsupportedMediaType        
 416 RequestedRangeNotSatisfiable
 417 ExpectationFailed           
 426 UpgradeRequired             
 500 InternalServerError         
 501 NotImplemented              
 502 BadGateway                  
 503 ServiceUnavailable          
 504 GatewayTimeout              
 505 HttpVersionNotSupported   
 

The approach works for any enumeration you may come across. Simply change the name of the enumeration data type. This example dumps the available console color codes:

[Enum]::GetValues([System.ConsoleColor]) |
  ForEach-Object {
    [PSCustomObject]@{
        Code = [int]$_
        Description = $_.toString()
    }
  }
 
Code Description
---- -----------
   0 Black      
   1 DarkBlue   
   2 DarkGreen  
   3 DarkCyan   
   4 DarkRed    
   5 DarkMagenta
   6 DarkYellow 
   7 Gray       
   8 DarkGray   
   9 Blue       
  10 Green      
  11 Cyan       
  12 Red        
  13 Magenta    
  14 Yellow     
  15 White    
 

Twitter This Tip! ReTweet this Tip!

Converting HTTP Response Codes

In the previous example we created a small PowerShell function that checks web site availability, and as part of the test results, a HTTP response code was returned. Let’s check out how this numeric code can be easily converted into a meaningful text message.

Here is the function again that tests web sites:

function Test-Url
{
  param
  (
    [Parameter(Mandatory,ValueFromPipeline)]
    [string]
    $Url
  )
  
  Add-Type -AssemblyName System.Web
  
  $check = "https://isitdown.site/api/v3/"
  $encoded = [System.Web.HttpUtility]::UrlEncode($url)
  $callUrl = "$check$encoded"
  
  Invoke-RestMethod -Uri $callUrl |
    Select-Object -Property Host, IsItDown, Response_Code
}

And this would be a typical result:

 
PS C:> Test-Url -Url powershellmagazine.com

host                   isitdown response_code
----                   -------- -------------
powershellmagazine.com    False           200
 

In this example, the response code is “200” which happens to stand for “OK”. If you’d like to convert HTTP response codes to text, simply convert the data type to [System.Net.HttpStatusCode]. That’s all:

 
PS C:> 200 -as [System.Net.HttpStatusCode]
OK
 

Here is a version that incorporates this conversion for you:

function Test-Url
{
  param
  (
    [Parameter(Mandatory,ValueFromPipeline)]
    [string]
    $Url
  )
  
  Add-Type -AssemblyName System.Web
  
  $check = "https://isitdown.site/api/v3/"
  $encoded = [System.Web.HttpUtility]::UrlEncode($url)
  $callUrl = "$check$encoded"
  $response = @{
    Name = 'Response'
    Expression = { 
        '{0} ({1})' -f 
            ($_.Response_Code -as [System.Net.HttpStatusCode]), 
            $_.Response_Code 
    }
  }
  Invoke-RestMethod -Uri $callUrl | 
    Select-Object -Property Host, IsItDown, $response
} 

And this would be the result:

 
PS C:> Test-Url -Url powershellmagazine.com

host                   isitdown Response
----                   -------- --------
powershellmagazine.com    False OK (200)
 

Note how the calculated column “Response” now reports both the original numeric response code and the friendly text for it.


Twitter This Tip! ReTweet this Tip!

Test Web Site Availability

When a web site is unavailable, often the question is whether it’s you, or whether the web site is generally down for everyone else, too. PowerShell can ask a web service to check web site availability for you. Here is a simple wrapper function:

function Test-Url
{
  param
  (
    [Parameter(Mandatory,ValueFromPipeline)]
    [string]
    $Url
  )
  
  Add-Type -AssemblyName System.Web
  
  $check = "https://isitdown.site/api/v3/"
  $encoded = [System.Web.HttpUtility]::UrlEncode($url)
  $callUrl = "$check$encoded"
  
  Invoke-RestMethod -Uri $callUrl |
    Select-Object -Property Host, IsItDown, Response_Code
}

It calls a RESTful API and submits the URL to check via URL arguments. That’s why the URL to test needs to be URL-encoded. Next, the code calls Invoke-RestMethod and received the test result as an object.

 
PS C:> Test-Url -Url powershellmagazine.com

host                   isitdown response_code
----                   -------- -------------
powershellmagazine.com    False           200
 

Please note that the web service used in this example is free, and does not require registration or an API key. The downside is that the web service is throttled, so if you check a large number of URLs, it may respond with an exception stating that you submitted too many requests. When this happens, just wait a moment and try again.


psconf.eu – PowerShell Conference EU 2019 – June 4-7, Hannover Germany – visit www.psconf.eu There aren’t too many trainings around for experienced PowerShell scripters where you really still learn something new. But there’s one place you don’t want to miss: PowerShell Conference EU – with 40 renown international speakers including PowerShell team members and MVPs, plus 350 professional and creative PowerShell scripters. Registration is open at www.psconf.eu, and the full 3-track 4-days agenda becomes available soon. Once a year it’s just a smart move to come together, update know-how, learn about security and mitigations, and bring home fresh ideas and authoritative guidance. We’d sure love to see and hear from you!

Twitter This Tip! ReTweet this Tip!

Unit Conversion via Web Service

Accessing RESTful web services is trivial for PowerShell: simply send your input data to a public web service, and receive the results.

Here are three PowerShell functions designed to each do a numeric conversion:

function Convert-InchToCentimeter
{
  param
  (
    [Parameter(Mandatory)]
    [Double]
    $Inch
  )
  [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
  $url = 'https://ucum.nlm.nih.gov/ucum-service/v1/ucumtransform/{0}/from/%5Bin_i%5D/to/cm' -f $Inch
  $result = Invoke-RestMethod -Uri $url -UseBasicParsing 
  $result.UCUMWebServiceResponse.Response
}


function Convert-FootToMicrometer
{
  param
  (
    [Parameter(Mandatory)]
    [Double]
    $Foot
  )
  [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
  $url = 'https://ucum.nlm.nih.gov/ucum-service/v1/ucumtransform/{0}/from/%5Bft_i%5D/to/um' -f $Foot
  $result = Invoke-RestMethod -Uri $url -UseBasicParsing 
  $result.UCUMWebServiceResponse.Response
}


function Convert-GramToOunce
{
  param
  (
    [Parameter(Mandatory)]
    [Double]
    $Gram
  )
  [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
  $url = 'https://ucum.nlm.nih.gov/ucum-service/v1/ucumtransform/{0}/from/g/to/%5Boz_ap%5D' -f $Gram
  $result = Invoke-RestMethod -Uri $url -UseBasicParsing 
  $result.UCUMWebServiceResponse.Response
}

Provided you have Internet access, then doing conversions is as simple as a function call:

 
PS C:> Convert-GramToOunce -Gram 230

SourceQuantity SourceUnit TargetUnit ResultQuantity
-------------- ---------- ---------- --------------
230.0          g          [oz_ap]    7.3946717   
 

The important points to keep in mind are:

  • You need to allow Tls12 to enable HTTPS connections (see code)
  • You need to follow the rules set forth by the web service, i.e. when it requires whole numbers, you cannot submit decimals

There are plenty more conversions available at https://ucum.nlm.nih.gov/ucum-service.html#conversion, so you can use the functions provided as a template to create more conversion functions.


Twitter This Tip! ReTweet this Tip!

Validating Active Directory Credentials

PowerShell can validate AD username and passwords against the Active Directory:

Add-Type -AssemblyName System.DirectoryServices.AccountManagement
$account = New-Object System.DirectoryServices.AccountManagement.PrincipalContext([DirectoryServices.AccountManagement.ContextType]::Domain, $env:userdomain)

$account.ValidateCredentials('user12', 'topSecret')

Note that this approach is for diagnostic purposes only. It accepts a plain text password.


Twitter This Tip! ReTweet this Tip!