PSExcel

IMPORTANT: This project is no longer maintained. Please consider Doug's excellent ImportExcel example, which is frequently updated by Doug and the community.

This is a rudimentary PowerShell module for working with Excel via the EPPlus library, with no dependencies on Excel itself.

Thanks to Doug Finke for his ImportExcel example - hadn't seen EPPlus before this!
Thanks to Philip Thompson for his expansive module illustrating how to work with EPPlus in PowerShell
Thanks to the team and contributors behind EPPlus for a fantastic solution allowing .NET Excel interaction, without Excel.

Caveats:

This covers limited functionality; contributions to this function or additional functions would be welcome!
Minimal testing. Contributions welcome!
Naming conventions subject to change. Suggestions welcome!

Functionality

Export random PowerShell output to Excel spreadsheets
Import Excel spreadsheets to PowerShell as objects
No dependency on Excel being installed

Instructions

# One time setup
    # Download the repository
    # Unblock the zip
    # Extract the PSExcel folder to a module path (e.g. $env:USERPROFILE\Documents\WindowsPowerShell\Modules\)

    #Simple alternative, if you have PowerShell 5, or the PowerShellGet module:
        Install-Module PSExcel

# Import the module.
    Import-Module PSExcel    #Alternatively, Import-Module \\Path\To\PSExcel

# Get commands in the module
    Get-Command -Module PSExcel

# Get help for a command
    Get-Help Import-XLSX -Full

# Export data to an XLSX spreadsheet
    Get-ChildItem C:\ -File |
        Export-XLSX -Path C:\Files.xlsx

# Import data from an XLSX spreadsheet
    Import-XLSX -Path C:\Files.xlsx

Examples

Several examples are available on the accompanying blog post and the embedded Gist.

Some highlights:

Export and import data

#Create some demo data
    $DemoData = 1..10 | Foreach-Object{

        $EID = Get-Random -Minimum 1 -Maximum 1000
        $Date = (Get-Date).adddays(-$EID)

        New-Object -TypeName PSObject -Property @{
            Name = "jsmith$_"
            EmployeeID = $EID
            Date = $Date
        } | Select Name, EmployeeID, Date
    }

# Export it
    $DemoData | Export-XLSX -Path C:\temp\Demo.xlsx

# Import it back
    $Imported = Import-XLSX -Path C:\Temp\Demo.xlsx -Header samaccountname, EID, Date

Verify that it exported:

Check the data we imported back:

Fun with formatting

Freeze panes:

# Open the previously created Excel file...
    $Excel = New-Excel -Path C:\temp\Demo.xlsx

# Get a Worksheet
    $Worksheet = $Excel | Get-Worksheet -Name Worksheet1

# Freeze the top row
    $Worksheet | Set-FreezePane -Row 2

# Save and close!
    $Excel | Close-Excel -Save

Format the header:

# Re-open the file
    $Excel = New-Excel -Path C:\temp\Demo.xlsx

# Add bold, size 15 formatting to the header
    $Excel |
        Get-WorkSheet |
        Format-Cell -Header -Bold $True -Size 14

# Save and re-open the saved changes
    $Excel = $Excel | Save-Excel -Passthru

Format the first column:

#  Text was too large!  Set it to 11
    $Excel |
        Get-WorkSheet |
        Format-Cell -Header -Size 11

    $Excel |
        Get-WorkSheet |
        Format-Cell -StartColumn 1 -EndColumn 1 -Autofit -AutofitMinWidth -AutofitMaxWidth 7 -Color DarkRed

# Save and close
    $Excel | Save-Excel -Close

Create tables

Why format the columns yourself? Create a table (thanks to awiddersheim!):

# Add a table, autofit the data.  We use force to overwrite our previous demo.
    $DemoData | Export-XLSX -Path C:\Temp\Demo.xlsx -Table -Autofit -Force

Pivot tables and charts

This is straight from Doug Finke's fantastic ImportExcel module:

# Fun with pivot tables and charts! Props to Doug Finke
    Get-ChildItem $env:USERPROFILE -Recurse -File |
        Export-XLSX -Path C:\Temp\Files.xlsx -PivotRows Extension -PivotValues Length -ChartType Pie

Notes

Note that while some of these examples leverage PowerShell version 3 or later language, the module itself should work with PowerShell 2, and all Pester tests run against both PowerShell 2 and PowerShell 4.

RamblingCookieMonster/PSExcel

RamblingCookieMonster

Reviews

Repository Details