PowerShell + Excel Culumns Formating

RobertKaucherRobertKaucher Member Posts: 4,299 ■■■■■■■■■■
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?

Comments

  • RobertKaucherRobertKaucher Member Posts: 4,299 ■■■■■■■■■■
    I'm thinking it must be some property of

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

    But I am clueless...

  • RobertKaucherRobertKaucher Member Posts: 4,299 ■■■■■■■■■■
    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"
  • blargoeblargoe Member Posts: 4,174 ■■■■■■■■■□
    Cool... thanks for sharing
    IT guy since 12/00

    Recent: 11/2019 - RHCSA (RHEL 7); 2/2019 - Updated VCP to 6.5 (just a few days before VMware discontinued the re-cert policy...)
    Working on: RHCE/Ansible
    Future: Probably continued Red Hat Immersion, Possibly VCAP Design, or maybe a completely different path. Depends on job demands...
  • HeroPsychoHeroPsycho Inactive Imported Users Posts: 1,940
    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.
    Good luck to all!
Sign In or Register to comment.