Currently, there are a couple of ways (painful ways) to get data from PowerShell to Excel. You can do it using  the COM (Component Object Model) interface:

$xl=New-Object -ComObject Excel.Application

Then create a workbook, a sheet, get access to the sheet cells, loop over the data copying each property of each boject to the correct row or column.

Or, you can use Export-Csv on the data, and Invoke-Item on the file to launch Excel.

Viable options for sure

Buy What If

You wanted to create an Excel spreadsheet, without Excel being installed? Like on a server. Create Pivot tables, charts. Or import and Excel spreadsheet, do something to it and export it again. What if you wanted to take all the sheets in a an Excel workbook and create each one as a text file for use elsewhere?

Well, that’s what my new PowerShell Excel Module makes easy.

In Action

Here is how you can export all the process info from a box, to Excel, without Excel.

Get-Process | Export-Excel .\test.xlsx

With Charts!

Adding a few more switches in Export-Excel, you create a pivot table (By Company, summing Physical Memory) and include a chart.

Get-Process | Export-Excel .\test.xlsx ` -WorkSheetname Processes ` -IncludePivotTable `

-IncludePivotChart ` -Show -PivotRows Company -PivotData PM

See The Result

Using the –Show switch, will launch Excel on the newly created file. Here is what you get, data, pivoted data and charts!

Watch the Video

Want to see it in action? Check it out.

Grab the PowerShell Module

Get the module and get the updates as it moves forward here on GitHub.

{ 7 comments }

I gave an all-day PowerShell workshop at the Philly.NET Code Camp 2015.1.

It was a great group of folks and I had a blast.

Seeing software professionals invest in their future by learning how productive (and profitable) PowerShell can be, is very exciting.

If you want to learn more about my PowerShell Workshops, leave a message or contact me directly.

You can find me on twitter https://twitter.com/dfinke.

Definitely check out my free open source PowerShell repositories on GitHub, https://github.com/dfinke

{ 0 comments }

PowerShell version 5.0 introduces the new class keyword. This is aimed at developers and aligns with object oriented programming. If you’re an IT Pro and have been using PowerShell for a bit, you’ve been exposed to object oriented concepts. PowerShell is based on .NET, which is object oriented and therefore you work with objects and access information through methods and properties. For example, the following prints out company information for the first running process in  your system.

(ps)[0].Company

The object is (ps)[0] and .Company is the property on the object.

In this post we’ll look at new terms like inheritance and polymorphism. These are good because it helps you get more done with less work, just like PowerShell itself.

A class is an extensible blueprint  or template for creating objects. We’ll use Animal, as an example throughout the post.

class Animal {}

It’s that simple to create a class in PowerShell. It looks like a function, workflow or configuration. One difference is, if it were a function you could type in Animal and it would work. Because it is a class, we need to “new” it up, like this:

[Animal]::new()

This creates and instance of the type Animal. These means we can now do this:

$animal1=[Animal]::new()            
$animal2=[Animal]::new()

And we’ll have two separate instances of the Animal class, one assigned to $Animal1 and the other to $Animal2.

Flesh Out The Animal Class

Two new concepts are introduced here. The constructor and a void method. Again, if you’ve been working with PowerShell, you’ve probably seen a void method, for example, you could kill the notepad process like this:

(ps notepad).Kill()

Kill() is an example of a void method. It does something, kills the process here, and returns nothing.

A constructor (abbreviated ctor), is a fancy object oriented way of saying, initialize. When you “new” up an class, this is called first, it cannot return anything and it can take parameters.

Here, in our Animal class, we are defining a constructor and three void methods Greet, Talk, and Sing. Each method, including the constructor writes something to the console.

class Animal {                                    
    Animal() { "Animal constructor" | Out-Host }                       
                        
    Greet() { "Animal says Hello" | Out-Host }                        
    Talk()  { "Animal talk"  | Out-Host }                        
    Sing()  { "Animal song"  | Out-Host }                        
}

Let’s exercise our Animal class by instantiating it (“newing” it up) and calling our void methods.

$animal = [Animal]::new()            
            
$animal.Greet()            
$animal.Talk()            
$animal.Sing()

This produces the following output:

Animal constructor
Animal says Hello
Animal talk
Animal song

Notice the first line printed is“Animal constructor”. This happens when the statement [Animal]::new() is executed.

Inherit the Animal

Let’s look at inheritance. We’ll create a Dog class, called a subtype, and have it inherit the Animal class.

class Animal {            
            
    Animal() {            
        "Animal constructor" | Out-Host            
    }            
            
    Greet() { "Animal says Hello" | Out-Host }            
    Talk()  { "Animal talk"  | Out-Host }            
    Sing()  { "Animal song"  | Out-Host }            
}            
            
class Dog : Animal {}

That last line is all we need to create the Dog class and inherit all the functionality from Animal. We can instantiate the class Dog class and use GetType to see what it is.

$Dog = [Dog]::new()            
$Dog.GetType()

Notice the Name and BaseType properties.

IsPublic IsSerial Name BaseType
-------- -------- ---- --------
True     False    Dog  Animal

Since Dog inherits everything from the base class Animal, let’s try it out.

$Dog = [Dog]::new()            
            
$Dog.Greet()            
$Dog.Talk()            
$Dog.Sing()

These statements print exactly what was printed when we did this with the instance of Animal.

Animal constructor
Animal says Hello
Animal talk
Animal song

So, inheritance can save us a huge amount of work. We can create a new class, inherit from another and reuse all of the base classes functionality.

But what if the new class is a bit different?

No problem. Let’s say we want the Dog class to do something different when it is instantiated. We’ll do that in the Dog constructor. Also, we want it to print “Dog Song“ when the Sing() method invoked, not “Animal Song”.

Here is the new Dog class implementation.

class Dog : Animal { Dog() { "Dog constructor" | Out-Host }

Sing() { "Dog song"|Out-Host } } $dog=[Dog]::new() $dog.Greet() $dog.Talk() $dog.Sing()

What’s printed

Instantiating the Dog class, we get two constructor messages. The first message from the Animal constructor (because the subclass (Dog) checks to see if the base class has a constructor and calls it) and second message  from the Dog constructor. Next, we see where inheritance can really pay off. We call the Greet and Talk methods and the already implemented code does it thing.

In the Dog class, we created a Sing() method. It is the same name as the method in the base class Animal. This is called overriding. So, the Dog Sing() method overrides the Animal SingO method.

Animal constructor
Dog constructor
Animal says Hello
Animal talk
Dog song

Notice it only prints out “Dog song”. Those are the rules of inheritance.When a subtype overrides a method in the base class, only the subtype code is executed (and, you can call the base class method explicitly).

Wrap Up

This is a quick introduction to inheritance in PowerShell v5.0. If you want to step up your game, with a little practice you’ll be using classes in PowerShell to provide all sorts solutions.

{ 2 comments }

You are forbidden!