Using the Import Excel module part 2: putting data into .XLSx files

This is third of a series of posts on Excel and PowerShell – the first on getting parts of an Excel file out as images wasn’t particularly tied to the ImportExcel Module, but the last one, this one and next one are.  I started with the Import Command – which seemed logical given the name of the module; the Export command is more complicated, because we may want to control the layout and formatting of the data, add titles, include pivot tables and draw charts;. so I have split it into two posts. At its simplest the command looks like this :

Get-Process | Export-Excel -Path .\demo.xlsx -Show

This gets a list of processes, and exports them to an Excel file; the -Show switch tells the command to try to open the file using Excel after saving it. I should be clear here that import and export don’t need Excel to be installed and one of the main uses is to get things into Excel format with all the extras like calculations, formatting and charts on a computer where you don’t want to install desktop apps; so –Show won’t work in those environments.  If no –WorksheetName parameter is give the command will use “Sheet1”.

Each process object has 67 properties and in the example above they would all become columns in the worksheet, we can make things more compact and efficient by using Select-Object in the command to filter down to just the things we need:

Get-Process | Select-Object -Property Name,WS,CPU,Description,StartTime |
Export-Excel -Path .\demo.xls -Show 

Failed exporting worksheet 'Sheet1' to 'demo.xls':
Exception calling ".ctor" with "1" argument(s):
"The process cannot access the file 'demo.xls' because it is being used by another process."

This often happens when you look at the file and go back to change the command and forget to close it – we can either close the file from Excel, or use the -KillExcel switch in Export‑Excel – from now on I’ll use data from a variable

$mydata = Get-Process | Select-Object -Property Name, WS, CPU, Description, Company, StartTime
$mydata | Export-Excel -KillExcel -Path .\demo.xlsx -Show

This works, but Export-Excel modifies the existing file and doesn’t remove the old data – it takes the properties of the first item that is piped into it and makes them column headings, and writes each item as a row in the spreadsheet with those properties. (If different items have different properties there is a function Update-FirstObjectProperties to ensure the first row has every property used in any row). If we are re-writing an existing sheet, and the new data doesn’t completely cover the old we may be left with “ghost” data. To ensure this doesn’t happen, we can use the ‑ClearSheet option

$mydata | Export-Excel -KillExcel -Path .\demo.xlsx -ClearSheet -Show


Sometimes you don’t want to clear the sheet but to add to the end of it, and one of the first changes I gave Doug for the module was to support a –Append switch, swiftly followed by a change to make sure that the command wasn’t trying to clear and append to the same sheet.

We could make this a nicer spreadsheet – we could make it clear the column headings look like headings, and even make them filters, we can also size the columns to fit…

$mydata | Export-Excel -Path .\demo.xlsx -KillExcel -WorkSheetname "Processes" -ClearSheet `
             -BoldTopRow -AutoSize -Title "My Processes" -TitleBold -TitleSize 20 -FreezePane 3 -AutoFilter -Show

The screen shot above shows the headings are now in bold and the columns have been auto sized to fit. A title has been added in bold, 20-point type; and the panes have been frozen above row 3. (There are options for freezing the top row or the left column or both, as well as the option used here –FreezePane row [column]) and filtering has been turned on.

Another way to present tabular data nicely is to use the -Table option

$mydata | Export-Excel -Path .\demo.xlsx -KillExcel -WorkSheetname "Processes" -ClearSheet –BoldTopRow    -AutoSize `
       -TableName table -TableStyle Medium6 -FreezeTopRow -show

“Medium6” is the default table style but there are plenty of others to choose from, and intellisense will suggest them

Sometimes it is helpful NOT to show the sheet immediately, and one of the first things I wanted to add to the module was the ability to pass on an object representing the current state of the workbook to a further command, which makes the following possible:

$xl = $mydata | Export-Excel -Path .\demo.xlsx -KillExcel -WorkSheetname "Processes" `
     -ClearSheet -AutoSize -AutoFilter -BoldTopRow –FreezeTopRow -PassThru

$ws = $xl.Workbook.Worksheets["Processes"]

Set-Format -WorkSheet $ws -Range "b:b" -NumberFormat "#,###"   -AutoFit
Set-Format -WorkSheet $ws -Range "C:C" -NumberFormat "#,##0.00" -AutoFit
Set-Format -WorkSheet $ws -Range "F:F" -NumberFormat "dd MMMM HH:mm:ss" -AutoFit


The first line creates a spreadsheet much like the ones above, and passes on the Excel Package object which provides the reference to the workbook and in turn to the worksheets inside it.
The example selected three columns from the worksheet and applied different formatting to each. The module even supports conditional formatting, for example we could add these lines into the sequence above

Add-ConditionalFormatting -WorkSheet $ws -Range "c2:c1000" -DataBarColor Blue
Add-ConditionalFormatting -WorkSheet $ws -Range "b2:B1000" -RuleType GreaterThan `
            -ConditionValue '104857600'  -ForeGroundColor "Red" -Bold

The first draws data bars so we can see at glance what is using CPU time and the second makes anything using over 100MB of memory stand out.

Finally, a call to Export-Excel will normally apply changes to the workbook and save the file, but there don’t need to any changes – if you pass it a package object and don’t specify passthrough it will save your work, so “Save and Open in Excel” is done like this once we have put the data in a formatted it the way we want.

Export-Excel -ExcelPackage $xl -WorkSheetname "Processes" -Show


In the next post I’ll look at charts and Pivots, and the quick way to get SQL data into Excel