Tag Archives: Powershell

Powershell related content

Anonymous functions in Powershell

While going through a number of javascript tutorials there were one thing that I wondered if it existed in Powershell.

Which is the notion of the “anonymous function”.
In Powershell this is more likely to be refered to as a “script block”.

The use case (in my opinion) is short repetitive code blocks that you want to call with different values, throughout your script/function.
Any more then that it’s probably better to just create a normal function as it will be be easier to follow and maintain.

Anyway, how do we create an “anonymous function”.
For example lets say you want to calculate the volume of a box in different parts of your code.
You could then create a variable holding a script block to do it.

# The variable containing the script block
$calculateVolume={param($width,$height,$depth) $width*$height*$depth}

# Calling the anonymous function using & before the variable.
&$calculateVolume 2 5 10

You can extend the script block between the curly braces just as any other function.
But then you might consider creating a normal function to “modularize” your code, rather than creating a big monolithic script/function.

Finally I want to leave a short example of something that I’ve found useful recently.
Which will examine a value and determine if the value is odd or even.
Then execute some code depending on if it’s odd or even.

# The IF statement will return true if it's odd and false if it's even.
$oddValue={param([int]$value) if($value % 2 -eq 1){$true}else{$false}}

# Some value to evaluate
$value = 5

# Calling the anonymous function in a switch statement.
switch(&$oddValue $value)
{
    $true {Write-Output "Some code if the value is odd";break}
    $false {Write-Output "Some code if the value is even";break}
}

Later on in the code when using it in the switch statement it looks much cleaner in my opinion.
Again, if you’re only going to use it once in your script/function, you may as well just use that one ‘IF’ statement.

Using QuickIO.Net with Powershell

This time around another .Net library called QuickIO.Net by the author Benjamin Abt.

The reason for this “project” was because a client of mine suffered from a Crypto Locker type event. It was caught quickly but not before some files out of those millions of files were encrypted.

The reason for using QuickIO.Net were threefold.

  1. Get-ChildItem don’t support paths of more than 260 characters.
  2. Conduct additional scripting/reporting after the list was created.
  3. Another workaround was in regard to using Robocopy and then RegEx to pull out the desired information. This produced issues with the file names when using any of the nordic special characters. E.g. åÅ, äÄ, öÖ.

The third point my not have been a big issue if it were one or two files but this was a scan of millions of files.

QuickIO.Net was very usefull as a workaround to the 260+ character limit.
I also thought this was a much cleaner solution compared to the Robocopy/RegEx solution.

The client also needed to check which files were changed at the time of the incident and only files with an extension of six characters long. The list could then be exported to a .csv file or used for further scripting e.g. move files to a quarantine location and so forth.

To use QuickIO.Net you need to download it from NuGet, a link is provided at the bottom of the page. Worth noting is that the QuickIO library can do much more than detailed in this post.

The input to the method that will search through the directories have three parameters.
The path to start from, any pattern filter using standard filters (e.g. “*.doc”) and if it should look through all the subdirectories or not.

The whole script is available via the links at the bottom of the page.
Here are the initial code and the first line that will load the .Net library.
You may want to change the path or even create a parameter for it if you want.

function Get-FilesQuickIO
{
    [CmdletBinding()]
    Param
    (
        # Path to start from
        [Parameter(Mandatory=$true,ValueFromPipelineByPropertyName=$true)]
        [string]$FilePath,

        # To search recursively
        [Parameter(Mandatory=$false,ValueFromPipelineByPropertyName=$true)]
        [switch]$Recursive,

        # To filter
        [Parameter(Mandatory=$true,ValueFromPipelineByPropertyName=$true)]
        [string]$Filter
    )

    # Load QuickIO Assmebly
    Add-Type -Path .\QuickIO\SchwabenCode.QuickIO.dll

I created some logic to handle the “Recursive” switch as the search option is named differently when calling the library. It also seems that if you use a filter and recursive at the same time the “EnumerateFiles” method will not do it recursively.
As a workaround I also created some logic to check if the pattern contains “*” or “*.*”.
If not then we first grab all the directories and then loop through those directories one by one.
The reason for not just using the directory loop and skip the extra logic, is that it takes about twice the time.
Actually it’s faster to get all the files and then use a pipe to “where” and filter it that way instead.
But for the sake of completeness I include the logic to handle the filter option.

    # Initiate the file list
    $fileList = @()

    # Set search option based on recursive or not
    if(($Recursive -eq $true) -and (($Filter -eq "*") -or ($Filter -eq "*.*")))
        {
        # Set the search option value
        $searchOption = "AllDirectories"

        # Get the list of files
        $fileList = [SchwabenCode.QuickIO.QuickIODirectory]::EnumerateFiles([system.string]$FilePath,[system.string]$Filter,[System.IO.SearchOption]$searchOption)
        }
    elseif ($Recursive -eq $true)
        {
        # Set the search option value
        $searchOption = "AllDirectories"

        # Set the directory pattern
        $directoryPattern = "*"

        # Get all the directories as the recursive option doesn't work when using a filter.
        $directoryList = [SchwabenCode.QuickIO.QuickIODirectory]::EnumerateDirectories([system.string]$FilePath,[system.string]$directoryPattern,[System.IO.SearchOption]$searchOption) | select -ExpandProperty FullName

        # Loop through the directories with the file pattern
        foreach($d in $directoryList)
            {
            $fileList += try{[SchwabenCode.QuickIO.QuickIODirectory]::EnumerateFiles([system.string]$d,[system.string]$Filter,[System.IO.SearchOption]$searchOption)}catch{$null}
            }
        }
    else{
        # Set the search option value
        $searchOption = "TopDirectoryOnly"

        # Get the list of files
        $fileList = try{[SchwabenCode.QuickIO.QuickIODirectory]::EnumerateFiles([system.string]$FilePath,[system.string]$Filter,[System.IO.SearchOption]$searchOption)}catch{$null}
        }
    return $fileList

As you may have noticed in this example I’m not dealing with any exceptions.
Which you may/should do of course but as this is an example code I’ll leave that up to you to create.

Finaly to solve the six character extension check I created some code outside the function.
I could’ve included it in the code above or created another function for it but this was kind of a one off, hopefully.

    $result = @()    

    foreach($f in $fileList)
        {
        $extension = $f.Name.Split(".") | select -last 1
    
        if($extension -match "\b.{6}\b")
            {
            $result += $f
            }
        }

The result from QuickIO.Net will include the full path, file name, size, dates and so forth.
You can then easily use standard powershell filters like “Where” or “Select” to pull the information you want.
E.g. in the scenario described above we used a Where statement on the returned result to just select the files changed on that particular date.

Hope you found this usefull and again the library is capable of a lot more if you need it for other purposes.

Script source code
QuickIO.Net

Creating Excel files with Powershell and EPPlus

One of the great things with Powershell is that it’s a layer ontop of the .Net framework. It may not be obvious at first but it’s kind of a “hybrid” of a command line shell and for a lack of better analogy, a cousin to C#.

What it means for us that use Powershell is that we can extend the capabilities in our scripts and modules beyond the already large collection of cmdlets in Powershell. By using built-in or third party .Net libraries.

In this blog post we are going to look at creating Excel ‘.xlsx’ files without installing Excel. Which is quite handy if you need to create Excel files on a system where you don’t want to install Office and e.g. create automated reports as Excel files.

To achieve this we are going to use the downloadable and free .NET library “EPPlus” for the creation of the Excel file and content.

A couple of things worth noting before we begin.

  • This post was made using Windows Server 2012 R2, Powershell version 4 and the DotNet4 version of the EPPlus library.
  • The post is going to show you how to create the Excel file and add content, however the EPPlus library is capable of much more but would require a much longer post. So this post will concentrate on the basics to get you going.
  • If you don’t want to know how to do it from scratch there is an (as far as I can tell) excellent module in the PS Gallery by “Douglas Finke” called “ImportExcel” that let you use standard Powershell syntax. ImportExcel also utilizes EPPlus to create the files.

With that said you may ask yourself, why go through all this if there is an available module already?

Good question, from the last project I was involved in these were some of my reasons.

  • Some features were not implemented yet unless you used the Export-Excel function. E.g. the autofit column width.
  • The spread sheets I was going to create was quite specific, so I didn’t need to deal with “generic” data input.
  • From a code perspective, I wanted the code to be as condensed as possible and not mix and match parts from a seperate module  and then calling functions directly from EPPlus.

Your milage may wary of course but those were some of the reasons which made me look into EPPlus in more detail.

If you want to follow the code in a text-version then I’ve created a link to the sample code on GitHub/Gist (EPPlus_Sample.ps1), you can find the links at the bottom of this post.

Before we can start we need to download the EPPlus library, the link to the library is available at the bottom of this post.

Note: You may need to unblock the files, otherwise you’ll get an error when you try to add the .dll to the powershell session.

But lets start looking at the interesting stuff, the code. I’ll be going through this from top to bottom. Again if you want to follow this in a “text” format then you have the link to the sample code.

epplus_sample

  1. First we need to add the EPPlus library to our session. This is where you’ll get an error if the .dll is blocked.
  2. Then we create a path to the .xlsx that we want to create and initiate the “Excel Package”. The Excel Package have “overloaded” methods for the initation but for this example we reference a file path.
  3. Next we create a reference to the workbook of the package, this way it will be cleaner to reference the underlaying properties.
  4. In the next section we add an Author and Title to the Excel file, this is optional.
  5. Next we add the first worksheet, if you want more than one sheet in your excel file then you can create more by using the same syntax. The pipe to Out-Null isn’t really needed but will supress the output to console if you don’t want to see it.
  6. Next we add a reference to the specific worksheet that we just created. You reference the worksheet by it’s index number and they start from 1.
  7. Next we add a value to the first cell in the first column.
  8. The next section will add a background color with a solid red color. As you can see we reference the cells between A1->E1.
  9. Once you’ve filled the sheet(s) with information then it might be a good time to autofit the colums. It’s good to do this at the end of the file creation. Since otherwise you may’ve added more information later on that will not have been part of a previous autofit.
  10. Next you find an example of setting the column width to a specific size. The columns are referenced by a number 1=A, 2=B and so forth.
  11. Next we save the file and at the very end we clean up any references to the package.

The result should look something like this:

result

I never promised it would look pretty 🙂

But what if you need to edit an Excel file that already exist?
It’s very similar to what we did before, you can find the sample code at the bottom of the post.

epplus_sample2

The only difference from what we did before is:

  • We don’t add a new sheet
  • We get the value from cell A1 and add more information to it.
  • Then change the value of cell A1 to the new value.
  • I also replaced the ugly red color of the first row to something more pleasing to the eye.
  • And finaly added some information in the A2 cell.

It should now look like this:

result2

To conclude this post the basic structure to create an Excel file is not that complicated once you’ve seen the structure and initiation.The reference to cells are the same as if you were using Excel. E.g. you can reference multiple cells using the standard range syntax like A1:A5.

You can add dropdown lists and add validation (may become a future post) and many more features that are available from the EPPlus library.

Finaly, I hope you’ve found this information useful and I wish you good luck with the next Excel automation project.

Reference Links:

EPPlus Library
ImportExcel – Douglas Finke
EPPlus_Sample.ps1
EPPlus_Sample2.ps1