More tricks with PowerShell and Excel

I’ve already written about Doug Finke’s ImportExcel module – for example, this post from last year covers

Basic exporting (use where-object to reduce the number of rows , select-object to remove columns that aren’t needed)

Using -ClearSheet to remove old data, –Autosize to get the column-widths right, setting titles, freezing panes and applying filters, creating tables

Setting formats and conditional format      

In this post I want to round up a few other things I commonly use.

Any custom work after the export means asking Export-Excel to pass through the unsaved Excel Package object like this

$xl = Get-WmiObject -Class win32_logicaldisk | select -Property DeviceId,VolumeName, Size,Freespace |
           Export-Excel -Path "$env:computerName.xlsx" -WorkSheetname Volumes –PassThru

Then we can set about making modifications to the sheet. I can keep referring to it via the Excel package object, but it’s easier to use a variable. 
$Sheet = $xl.Workbook.Worksheets["Volumes"]

Then I can start applying formatting, or adding extra information to the file
Set-Format -WorkSheet $sheet -Range "C:D" -NumberFormat "0,000"
Set-Column -Worksheet $sheet -Column 5 -Heading "PercentageFree" -Value {"=D$row/C$row"} -NumberFormat "0%" 

I talked about Set-column in another post. Sometimes though, the data isn’t a natural row or column and the only way to do things is by “Poking” individual cells, like this

    
$sheet.Cells["G2"].value = "Collected on"
$sheet.Cells["G3"].value = [datetime]::Today
$sheet.Cells["G3"].Style.Numberformat.Format = "mm-dd-yy"
$sheet.Cells.AutoFitColumns()
Close-ExcelPackage $xl –Show

Sharp-eyed readers will see that the date format appears to be “Least-significant-in-the-middle” which is only used by one country – and not the one where I live. It turns out Excel tokenizes some formats – this MSDN page explains and describes “number formats whose formatCode value is implied rather than explicitly saved in the file….. [some] can be interpreted differently, depending on the UI language”. In other words if you write “mm-dd-yy” or “m/d/yy h:mm” it will be translated into the local date or date time format. When Export-Excel encounters a date/time value it uses the second of these; and yes, the first one does use hyphens and the second does use slashes. My to-do list includes adding an argument completer for Set-Format so that it proposes these formats.

Since the columns change their widths during these steps I only auto-size them when I’ve finished setting their data and formats. So now I have the first page in the audit workbook for my computer

Of course there times when we don’t want a book per computer with each aspect on it’s own sheet, but we want book for each aspect with a page per computer.
If we want to copy a sheet from one workbook to another, we could read the data and write it back out like this

Import-Excel -Path "$env:COMPUTERNAME.xlsx" -WorksheetName "volumes" | 
     Export-Excel -Path "volumes.xlsx" -WorkSheetname $env:COMPUTERNAME

but this strips off all the formatting and loses the formulas  – however the Workbook object offers a better way, we can get the Excel package for an existing file with
$xl1 = Open-ExcelPackage -path "$env:COMPUTERNAME.xlsx"

and create a new file and get the Package object for it with 
$xl2 = Export-Excel -Path "volumes.xlsx" -PassThru

(if the file exists we can use Open-ExcelPackage). The worksheets collection has an add method which allows you to specify an existing sheet as the basis of the new one, so we can call that, remove the default sheet that export created, and close the files (saving and loading in Excel, or not, as required) 

$newSheet = $xl2.Workbook.Worksheets.Add($env:COMPUTERNAME, ($xl1.Workbook.Worksheets["Volumes"]))
$xl2.Workbook.Worksheets.Delete("Sheet1")
Close-ExcelPackage $xl2 -show
Close-ExcelPackage $xl1 –NoSave

The new workbook looks the same (formatting has been preserved -  although I have found it doesn’t like conditional formatting) but the file name and sheet name have switched places.

Recently I’ve found that I want the equivalent of selecting “Transpose” in Excel’s paste-special dialog- take an object with many properties and instead of exporting it so it runs over many columns in making a two-column list of Property name and value. 
For example
$x = Get-WmiObject win32_computersystem  | Select-Object -Property Caption,Domain,Manufacturer,
                            Model, TotalPhysicalMemory, NumberOfProcessors, NumberOfLogicalProcessors

$x.psobject.Properties | Select-Object -Property name,value |
    Export-Excel -Path "$env:COMPUTERNAME.xlsx" -WorkSheetname General -NoHeader -AutoSize –Show

c

When I do this i a real script I use the –passthru swtich and apply some formatting

$ws    = $excel.Workbook.Worksheets["General"]
$ws.Column(1).Width                     =  64
$ws.Column(1).Style.VerticalAlignment   = "Center"
$ws.Column(2).Width                     =  128
$ws.Column(2).Style.HorizontalAlignment = "Left"
$ws.Column(2).Style.WrapText            = $true

Of course I could use Set-Format instead but sometimes the natural way is to refer to use .Cells[]  , .Row() or .Column().

Advertisement