Open-XML. This is what it's all about.

  • Article

I have been saying for ages that most IT professionals really don't give two hoots about Open-XML. It's a file format. Who cares ? My first chart - click for full size version

The old file format had been around for ages, and was pretty opaque, so hardly anyone dug into it. The new format is XML (which is good), and rich (also good), but complicated (not so good). It's joined the ranks of formats approved by ISO, which might matter if to those Government trying to follow de-Jure standards (and ignoring de-facto ones... X400 vs SMTP anyone ?). But to IT Professionals in the commercial sector using and supporting office, does any of this matter ? Don't they see file formats as "black boxes" ... they might say that in theory it's lovely that instead of something proprietary file internals are now XML (and standard's body controlled XML)  but in practice they still need to deal with a change in file formats. Will they take advantage of the changes  ? And some will wonder if manipulation through the Office Object model which we've been doing since word 6 wasn't enough ?

Time to Reconsider.

This week we released the Open-XML SDK. It allows developers to work much more quickly with Open-XML. That's obviously a Good Thing because it will bring more things to market which can work with the format. Still to the kind of IT-Pro I'm thinking about ... the kind who I think reads this blog ... a new SDK isn't exactly a reason to crack open the champagne. OK we can manipulate the files without having the applications present, and  developers who slave away in C# might produce stuff which IT pros want... but what can I do with this RIGHT NOW ?

How about charting your Data Centre Activity in Excel. I can almost feel the interest, but it's tempered with a "but that needs a bucket full of code .... doesn't it". At risk of repeating myself

Time to Reconsider.

Eric White - a fellow evangelist, although I'll confess not one I could pick in an ID parade - has posted some the C# code for a PowerShell snap-in to CodePlex. This shows there might be a bucketful of code, but you don't have to be one who writes it.   You can view what's on Codeplex as two things, one is a demo of what can be done with the Open-XML SDK. The other is a bunch of Powershell cmdlets which are useful in their own right. Now those readers who have seen some of my PowerShell might still feel cautious - there may still be some nasties here.

Time to Reconsider.

How's this for a command line to get running processes into a spreadsheet.

    Get-Process | Export-OpenXMLSpreadSheet -OutputPath Process.xlsx 

That's it. Not nasty is it ? Cynics might think "I could use Export-CSV and open that in Excel". But we're just getting started. How about a graph ? These two lines of PowerShell get the total CPU time used by running processes, and then gets the 10 heaviest processes, and spits out their ProcessName  and %CPU - the proportion of CPU they've used (the method is a shade simplistic but bear with me)

    get-process | foreach -begin {$TotalCPU=0;} -process {$TotalCPU += $_.CPU;}   
   Get-Process | sort -descending cpu | `
   select -first 10 -property ProcessName, @{name="%CPU" ;expression={100*$_.cpu/$TotalCPU}}

You've already seen that we could pipe that into Export-OpenXMLSpreadSheet. But I want a bar chart of CPU used for each process, what would that need.  The video Eric has on his blog gives the answer - the extra switches needed by Export-OpenXMLSpreadSheet would be

    -chart -ChartType Bar -ColumnsToChart %CPU -HeaderColumn ProcessName

I thought I'd have a go, I had the Express version of C# on my demo server but no Office. I downloaded the SDK, and the Stuff from CodePlex, copied what I'd seen Eric do in the video and hey presto I had a compiled version set-up for 32 bit powershell only. This is not the first and won't be the last Snap in to only register as 32 bit. Fortunately I know how to fix that and next post will explain it.  But within 30 minutes I'd produced the chart you see here, and opened it using office in a VM on the same box.

In my previous role as a consultant I would have loved tools like this. Giving the client reports that were easy to understand and looked great from Exchange , System Center Virtual Machine Manager, HPC, you name it... The PowerShell community extensions could even mail them as an attachment. .

Eric's video shows how multiple documents can be given a common style and gives a bunch more detail. Seriously if you didn't think you could learn anything from an 8 minute video today it might be time to reconsider.

 

Technorati Tags: Microsoft,Windows,Powershell,Office,XML,Open XML