Script

Creating Excel spreadsheet with PowerShell

If you have followed this blog from the beginning, you would have seen that I have done things with PowerShell and Excel in the past. Like in Enclosure Overview, where I draw blade enclosures, with blades in them, name them and color code them according to the cluster they are in. This was done to give a clear understanding of how clusters where spread out through the data center. This is a great way to visualize fault domains(in this case sites/enclosures) and how they are utilized. Another example is my blog post about pxe boot, called Yet another ESXi pxe boot solution. Where I read data from en Excel spreadsheet. That way I can populate host names and IP addresses in a fast and efficient way, from a spreadsheet.

 

Recently I found a better way of doing this. The problem with the way it way done before was that it required that Excel was installed on the computer/server on which the script was executed. Like I said I recently found a way to do this with out having to have Excel installed on the device. This is very useful especially in sandbox environment, but also usually in the data center as Excel seldom are installed on servers.

 

Excel on Git

It was PSExcel I found on Git, which got me on the right track. Actually PSExcel doesn’t do all the magic. The project that makes this and other project possible is EPPlus which is found over on codeplex. Another great project is ImportExcel also on Git.

 

How to install

I’m testing this on Windows 10, with PowerShell v5. There have been lots of improvement both to Windows and to PowerShell, the last one is the most important one here. I makes the installation of the needed module so much easier, all you have to do is run the following lines.

 

You will be asked to confirm you want to download these modules from untrusted sources.

Then

Verify that the directories “ImportExcel” and “PSExcel” are there. Now import the modules

Lastly verify the modules are imported – again look for “ImportExcel” and “PSExcel”

 

How to use it

I’m going to start with a quick example.

This will create the following output (output shortened)

__Noun NameNameHandlesVMWSPMNPMPathCompanyCPUFileVersion
ProcessAESTSr6446143974408437765734404408C:\Program Files\IDT\WDM\AESTSr64.exeAndrea Electronics Corporation0,021.0.64.7

I selected only the first line of the Get-Process command in order to get a fast output. The -force option is there to in force overwrite of the spreadsheet.

 

Next example… Just so that out get an idea of what it is capable of.

Here is the output:

PSExcel-ProcessGrouped

What happen here… First interesting thing is that I grouped the Get-Process output on the path parameter(Since I use $_.Group.Name later, you could just aswell had used Name instead of Path). Next comes a foreach loop, where in I Export the output of each group into its own worksheet, with the name of the process as the worksheet name.

So as long as you can find a way to structure your data you can as easily get it presented in a nice way in  Excel.

 

This is where I would have showed you that you can also do charts with this module, all those know from Excel, be it pie, area or some other chart. But this laptop i’m writing this on doesn’t won’t to. Which brings me to the conclusion.

 

Conclusion

As I have shown you, there is an easy way of doing Excel spreadsheets, but currently the modules that are available are community driving and there for might have a few bugs and untested platforms.

That leaves us with a way of doing Excel Spreadsheets, thanks to the EPPlus project, which does require some work on you the reader, or you can try one of the modules which lets you get going easy and fast but might not be quite finished.

So far I have only showed you the Export-XLSX command, but there were another modules which was installed, the Import-XLSX, which is just as important. With that I will leave you with the below command and output. Thanks for reading this far.

 

 

 

One thought on “Creating Excel spreadsheet with PowerShell

  1. Hello, I found your page using google and it made sense and I got my script working in a very short time thanks to your page. Thankyou for your help. Regards Bruce.

Leave a Reply

Your email address will not be published. Required fields are marked *