PowerShell + Excel Culumns Formating

RobertKaucher

I've created a nice powershell script that extracts data from a SQL Server view, manipulates it, and then places it into Excel. I fave figured out how to set attributes on the columns like the width, but I do not know how to set the column format to currency or date for example.

Any ideas?

RobertKaucher

I'm thinking it must be some property of

$worksheet.columns.item('a').

But I am clueless...

RobertKaucher

I just recorded a macro and hacked the code from that.

$worksheet.columns.item('d').NumberFormat = "$#,##0.00"
$worksheet.columns.item('c').NumberFormat = "m/d/yyyy"

blargoe

Cool... thanks for sharing

HeroPsycho

You may want to checkout PowerTabs down the road. It allows for more easy discoverability for various .net objects. The author is also looking to add the same functionality to COM objects in the future.