Export-Xlsx extended…

I have added some new features to the Export-Xlsx function. One of these is the Transpose parameter, which allows you to transpose the table. And optionally you can set the value of a property of the input object as column title.

Two other new features are on the cosmetic side. The FreezePanes parameter allows you to freeze the header row, so that when scrolling down your data in Excel you will not lose track of the column headers.
The TableStyle parameter allows you to apply a tablestyle to your table. There are a few predefined styles available in Excel. You can see a preview of the styles here. Because of the ValidateSet in the function you can tab through the available options. The table styles for pivot tables are not being used in this function.
Note that this parameter will be ignored if you have Excel 2003 installed.

Update Feb 01 2013

The function has received a couple of updates.
Instead of defining validatesets I’ve used the DynamicParam block to create custom argument completers for the ChartType and TableStyle parameters.
Another change is that using TransposeColumnProperty will now move the object property to the first row.
Using a path in the form of ‘.\somefolder\targetfile.xlsx’ is now possible.
And all the Excel COM operations are now executed in the en-US culture, regardless of your system culture. Thanks to Kenneth for testing and providing input.

Additionally the error handling is improved and the Verbose parameter will generate output now.

The Import-Xlsx function has received similar updates.

Tagged . Bookmark the permalink.

Comments are closed.