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.

 

Install-Module -Name ImportExcel
Install-Module -Name PSExcel

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

Then

cd "C:\Program Files\WindowsPowerShell\Modules"
dir

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

Import-Module .\ImportExcel\1.82\ImportExcel.psm1
Import-Module .\PSExcel\1.0\PSExcel.psm1

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

Get-Module

ModuleType Version    Name                                ExportedCommands
---------- -------    ----                                ----------------
Script     0.0        ImportExcel                         {Add-WorkSheet, ConvertFrom-ExcelSheet...
Manifest   3.1.0.0    Microsoft.PowerShell.Management     {Add-Computer, Add-Content, Checkpoint...
Manifest   3.1.0.0    Microsoft.PowerShell.Utility        {Add-Member, Add-Type, Clear-Variable,...
Binary     1.0.0.0    PackageManagement                   {Find-Package, Get-Package, Get-Packag...
Script     1.0        PowerShellGet                       {Find-Module, Get-InstalledModule, Get...
Script     0.0        PSExcel                             {Add-PivotChart, Add-PivotTable, Add-T...
Script     1.1        PSReadline                          {Get-PSReadlineKeyHandler, Get-PSReadl...

 

How to use it

I’m going to start with a quick example.

(Get-Process)[0] | Export-XLSX c:\temp\Process.xlsx -force

This will create the following output (output shortened)

__Noun Name	Name	Handles	VM	WS	PM	NPM	Path	Company	CPU	FileVersion
Process	AESTSr64	46	14397440	843776	573440	4408	C:\Program Files\IDT\WDM\AESTSr64.exe	Andrea Electronics Corporation	0,02	1.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.

Get-Process | Sort Name,Path | Group-Object Path |%{$_.group | Export-Xlsx -Path $env:USERPROFILE\Documents\Process.xlsx -WorksheetName ($_.Group.Name|Get-unique)}

Here is the output:

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.

Import-XLSX C:\temp\Process.xlsx


__NounName                 : Process
Name                       : AESTSr64
Handles                    : 46
VM                         : 14397440
WS                         : 843776
PM                         : 573440
NPM                        : 4408
Path                       : C:\Program Files\IDT\WDM\AESTSr64.exe
Company                    : Andrea Electronics Corporation
CPU                        : 0,015625
FileVersion                : 1.0.64.7
ProductVersion             :
Description                : Andrea filters APO access service (64-bit)
Product                    : APO Access Service (64-bit)
BasePriority               : 8
ExitCode                   :
HasExited                  : False
ExitTime                   :
Handle                     : 0
SafeHandle                 : Microsoft.Win32.SafeHandles.SafeProcessHandle
HandleCount                : 46
Id                         : 2396
MachineName                : .
MainWindowHandle           : 0
MainWindowTitle            :
MainModule                 : System.Diagnostics.ProcessModule (AESTSr64.exe)
MaxWorkingSet              : 0
MinWorkingSet              : 0
Modules                    : System.Diagnostics.ProcessModuleCollection
NonpagedSystemMemorySize   : 4408
NonpagedSystemMemorySize64 : 4408
PagedMemorySize            : 573440
PagedMemorySize64          : 573440
PagedSystemMemorySize      : 25096
PagedSystemMemorySize64    : 25096
PeakPagedMemorySize        : 643072
PeakPagedMemorySize64      : 643072
PeakWorkingSet             : 3100672
PeakWorkingSet64           : 3100672
PeakVirtualMemorySize      : 17567744
PeakVirtualMemorySize64    : 17567744
PriorityBoostEnabled       : True
PriorityClass              : Normal
PrivateMemorySize          : 573440
PrivateMemorySize64        : 573440
PrivilegedProcessorTime    : 00:00:00.0156250
ProcessName                : AESTSr64
ProcessorAffinity          : 0
Responding                 : True
SessionId                  : 0
StartInfo                  : System.Diagnostics.ProcessStartInfo
StartTime                  : 11-09-2015 02:49:14
SynchronizingObject        :
Threads                    : System.Diagnostics.ProcessThreadCollection
TotalProcessorTime         : 00:00:00.0156250
UserProcessorTime          : 00:00:00
VirtualMemorySize          : 14397440
VirtualMemorySize64        : 14397440
EnableRaisingEvents        : False
StandardInput              :
StandardOutput             :
StandardError              :
WorkingSet                 : 843776
WorkingSet64               : 843776
Site                       :
Container                  :