Doug Finke has created an awesome PowerShell module called ImportExcel which comes with all the commands you need to import and export data from and to Microsoft Excel. It does not require Office to be installed.
We can’t cover all the richness this module delivers, but in this tip, we’d like to provide you with the basics to get it up and running, and in follow-up tips we’ll deal about some formatting tricks.
To use the Excel commands, simply download and install the free module:
PS> Install-Module -Name ImportExcel -Scope CurrentUser -Force
When you do this for the first time, you may have to consent to downloading a “NuGet” open source DLL. Once the command finished, you now have access to a ton of new Excel commands, of which the most important is Export-Excel.
You now can pipe data directly to an Excel file, and provided Microsoft Office is installed, you can even open and show the results in Excel (Office is not required to create the .xlsx file).
Here is a simple example:
$Path = "$env:temp\report.xlsx" Get-Process | Where-Object MainWindowTitle | Export-Excel -Path $path -ClearSheet -WorksheetName Processes -Show
It’s really as simple as this. Creating Excel files has never been easier. Here are a couple of points you want to keep in mind, though:
- Use Select-Object to select the properties you want to export before you pipe the data to Export-Excel
- Use -ClearSheet to clear previous data. If you omit this parameter, new data will be appended to existing data in the .xlsx file.
- You may want to consider deleting an old .xlsx file manually before creating a new one with the same name. Else, there is the chance that Export-Excel takes into account existing settings from the old file.
