I got this question on twitter recently so I decided to blog it real quick. The question is, how do you output data to excel from powershell?
Well, there’s a free function that I’ve used and I’ve been quite happy with it. I’m not going to give a lot of explanation here except to say that when using it on a large dataset I’ve found it to be painfully slow. And by painfully slow I mean several hours. However, using it with a small amount of data isn’t too bad. I like it because it does all the lifting for me. I just output to the function and I’m done. So anyway, here’s the link to the site. The dev does a good job of giving examples.
http://www.lucd.info/2010/05/29/beyond-export-csv-export-xls/
I hate to be the naysayer, but I really detest any program/script solution for making excel files that uses Excel via COM interop or PIA. I have two reasons for this. The first is excel holds on to memory, and if your script or program writes thousands of spreadsheets in a for loop, you have to manually call gabage collection even if you do everything right to prevent memory leaks. The second is you presume excel is installed on all machines. Your program or script will not run on a lot of servers. Personally, I actually pay for an MSDN subscription, but refuse to use MS office on personal machines.
If you want to write Xls documents with powershell I’d suggest a module based upon the microsoft OpenXml SDK, or better yet the wonderful EPPlus on codeplex which I’ve used to write some very large spreadsheets.
All that being said, it is a nice Cmdlet if you want something quick and dirty to write an excel file and you can count on excel being installed.. I just would be very wary of generating thousands of spreadsheets in one script run.