Saturday 28 March 2015

Practical work with Powershell

One of the things I sometimes need to do is to be able to generate sets of reports with different parameters. As some of these reports can take a while to run this is an ideal opportunity to script the task and as I’m currently spending more time in PowerShell it seemed like the obvious tool.

For working with Excel I’ve been using ClosedXML although in the future I’m going to investigate EPPlus as there are claims that it’s better for larger files.

So the first thing to do is to demonstrate how easy it is to use ClosedXML with PowerShell to write to a file

Then it’s a case of getting the data out of the database and into a DataTable

One of the things I like about ClosedXML is the ability to pass in a datatable as an argument to the new sheet method and have it do the work automatically.

This results in the final script which can be used as base for further customisation