Other Languages - Format Excel file using Powershell and create chart

Asked By Dave Naumann on 26-Mar-12 12:37 PM
I have a powershell script I created which pulls information from a few of my servers listed in a .txt file and exports it to an excel spreadsheet.  I need to format the cells so the data can be read without manually expanding the cell.  I would also like to take this data and create a chart.  I can't figure out where to put the autofit since my script ends with me exporting it out to a .csv file.  I'm sure it is something simple I am missing.  Here is my current script.

$servname = Get-Content c:\FSserv.txt
Get-WmiObject win32_logicalDisk -ComputerName $servname | Where-Object {$_.VolumeName -like "JH*R*"} | select-object `
    DeviceID,VolumeName, `
   
    @{label="Size";Expression={"{0:n2}" -f ($_.Size/1GB)}},
    @{label="FreeSpace";Expression={"{0:n2}" -f ($_.FreeSpace/1GB)}},
    @{label="%Free";Expression={"{0:p2}" -f ($_.FreeSpace/$_.Size)}} | Export-CSV c:\jhFSfreespace.csv -NoTypeInformation
Donald Ross replied to Dave Naumann on 26-Mar-12 01:35 PM
Dave,

weather you are using a Text Tab delimited or a CSV file to inport into ecel I have found that it is near imposible to make it autofit without opening the excel file and selecting all and then double clicking on the colums or row.  but i did find a bit of code that you can use inthe workbook to do just what you want. 
try this in the workbook you are exporting to.

Option Explicit

Sub AutoFitAll()
  
    Application.ScreenUpdating = False
    Dim wkSt As String
    Dim wkBk As Worksheet
    wkSt = ActiveSheet.Name
    For Each wkBk In ActiveWorkbook.Worksheets
      On Error Resume Next
      wkBk.Activate
      Cells.EntireColumn.AutoFit
    Next wkBk
    Sheets(wkSt).Select
    Application.ScreenUpdating = True
  
End Sub

Don
Dave Naumann replied to Donald Ross on 26-Mar-12 01:55 PM
Thanks.  That works but I would have to do this everytime this script is ran correct.  I have another piece of the script that I include that runs this every Monday and e-mails to a group at a certain time.  I would like it to be formatted for them when they open the excel file.  Any other suggestions?
Donald Ross replied to Dave Naumann on 26-Mar-12 11:26 PM
Not sure at this point might be in over my head but is your script file creating a new excel file each time? if so you are correct you will have to insert the code i gave you and that is not what you want. 

If you had a way of adding the VB to all workbooks under personal setting or something rather than with each book, then when your script ran and created a new book, it would already have it and run on open for the receiver.

I will do some more checking.

Don

Somesh Yadav replied to Dave Naumann on 27-Mar-12 01:53 AM

There is no easy "conversion" from CSV to Excel.  You would need to instatiate an Excel COM object, and work with it directly.  You would not be using Export-CSV.

See the following link to get you started:

http://powershell.com/cs/blogs/tobias/archive/2010/08/19/automating-office-and-excel-in-powershell.aspx