Using Excel functions in PowerShell

As I was saying, we can do some basic arithmetic in PowerShell, but not much.  A .Net System class named [math] has 30 math functions we can use for doing pretty much any math you will ever need to do in PowerShell.

But sometimes we need to get a little crazy.  Sometimes you wish you could do something that's easy in Excel, but next to impossible in PowerShell.

Why not do it in Excel within PowerShell?

Everything in Excel is built on the same object models as everything else, and has all the same programmatical accessibility as everything else Microsoft these days.  So we can just reach inside, grab it by its objects, and tell it what we want it to do.  (That didn't come out right.)

Excel needs to be installed on the machine you are running this on, so you pretty much have to run it on your workstation.  It is going to load an instance of Excel in the background, so it is going to use some computer resources and time.  But we have computers with nothing better to do than what we tell them to do, and we're a little crazy.

First load the assembly so that PowerShell knows what we are talking about when we start speaking in Excelese.

[reflection.assembly]::LoadWithPartialName( "Microsoft.Office.Interop.Excel" )

Next load an Excel application into an object to do our bidding.

$XL = New-Object -ComObject Excel.Application

$XL.WorksheetFunctions holds the functions we want to use.  To see the full list, pipe it to Get- Member.

$XL.WorksheetFunctions | Get-Member

398 functions available!  Now we’ve got math coming out of our asymptotes!

Do you have a number that you need to round to the nearest multiple of 22.5?

Just use

$XL.WorksheetFunction.MRound( $X, 22.5 )

Do you want to know the size range that 80% of your file sizes fall into?

Try

$XL.WorksheetFunction.Percent( ( $Files | ForEach { $_.Length } ), 0.1 )
$XL.WorksheetFunction.Percent( ( $Files | ForEach { $_.Length } ), 0.9 )

(If you are using PowerShell 3.0 or higher, you may be tempted to try using $Files.Length instead of ( $Files | ForEach { $_.Length } ).  It won't work.  The array $Files has a property called .Length, so PowerShell is going to use that, and give you the number of files in the array, instead of giving you an array of the .Length’s of the files in the array.  So you have to loop through the array to pull out the lengths.)

Do you want to know the monthly payment on a 3-year $2,000 loan at 4.25% interest?

That's easy.

$XL.WorksheetFunction.Pmt( 0.0425 / 12, 36, -2000 )

If you need details on how to use the functions, open up Excel and use its Help.  You are, by definition, on a machine with Excel installed.

That ends my two-part series of Math in PowerShell articles for now, but I like PowerShell and I like math, so there will probably be more in the future.