Excel Column Format

[RobertKaucher]

I have used PowerShell to get data from SQL server, perform some calculations and then export the data to an Excel spreaksheet. I have managed to format the column width but would like to go one step further to modify the format of the columns contents (ie currency, date, etc)

Thank you!

 

[Zelandakh]

Wouldn't it be easier to use SQL Studio or Excel to do this?

 

[RobertKaucher]

No. Using SQL is not possible due to the complexity of the calculations. I am using Excel. Working with Excel in PowerShell is actually quite easy. I am simply missing one piece of the puzzle. The point here is that this report is being generated with no user interaction. It runs daily and saves the report based on the date. I'm not aware of any way you can get Excel to do this on its own.

 

[RobertKaucher]

I recorded a macro and stole the code for the object model from the macro. This is what it looks like:

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

 

[Zelandakh]

Coolio - nice one.