Warren F bio photo

Warren F

Systems Engineer with a penchant for PowerShell, science, cooking, information security, family, cookies, and the Oxford comma.

Connect

@pscookiemonster LinkedIn Github Stackoverflow TechNet RSS Feed My old blog

My GitHub Repos

AppVReporting BuildHelpers Citrix.NetScaler Git-Presentation InfoBlox Invoke-Parallel PowerShell PSDepend PSDeploy PSDiskPart PSExcel PSHTMLTable PSRabbitMQ PSSlack PSSQLite PSStash RabbitMqTools SecretServer

Overview

Rambling

When I first started using PowerShell, I preferred sending data to Excel, where I could comfortably filter, sort, and work with data. One of my first functions was a modification of Export-XLSX found on the Internet. That same code sits in a few places in production today; looking at old code is scary.

Nowadays, I prefer working with objects in PowerShell itself, and learning to do this has been incredibly valuable. If I need to export data, chances are I will use MSSQL through Invoke-Sqlcmd2 and Invoke-SQLBulkCopy, or perhaps SQLite.

On Friday, Doug Finke tweeted about an Excel module:

Doug's tweet

Intriguing! I hadn’t heard of EPPlus. Turns out there’s a .NET library for reading and writing Excel files, without Excel. I’m easily distracted, and Doug only gave us a taste, so time to write a module! [Edit: Doug has expanded his solution to a full module with a number of bells and whistles]

I looked around, saw a few snippets, and one big, comprehensive module from Philip Thompson, but I was looking for something in between.

Why not COM?

Chances are you have worked with Excel through COM. Why wouldn’t we just write a module using this?

Ignoring the dependency on an installed copy of Microsoft Excel, it turns out this isn’t supported if you want to use it in an automated solution. You might even run into situations where it won’t run, even with a variety of tweaks.

Straight from Microsoft:

All current versions of Microsoft Office were designed, tested, and configured to run as end-user products on a client workstation. They assume an interactive desktop and user profile. They do not provide the level of reentrancy or security that is necessary to meet the needs of server-side components that are designed to run unattended.

Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

PSExcel

I spent a short while flipping through Doug and Philip’s work, and ended up with the PSExcel module, a rudimentary module for working with Excel, without the dependency of Excel or it’s troublesome COM interface.

What can we do with this? Let’s walk through a quick demo.

Export data to Excel

We’ll create some dummy data to use:

#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
    }

Dummy data

Now, let’s export it!

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

Let’s verify in Excel:

Dummy data

Import data from Excel

Importing data is just as easy. In this example, let’s switch out the headers:

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

Dummy data

It worked! Keep in mind that Excel might not store your data as expected. If you run into any odd cases, be sure to let me know, there might be a quick fix.

Generate an Excel object to work with

You might want to open an existing xlsx file to work with:

$Excel = New-Excel -Path C:\temp\Demo.xlsx

This is a very basic function, it just creates a OfficeOpenXml.ExcelPackage object. I like abstraction though; I don’t want to remember that I have to call New-Object OfficeOpenXml.ExcelPackage $Path, I just want to say New-Excel.

Get a workbook

We have an ExcelPackage to work with, now we can get the workbook from this. More abstraction; this case it’s literally just calling the Workbook property.

$Workbook = $Excel | Get-Workbook

Get a worksheet

We can pipe an ExcelPackage or a Workbook to Get-Worksheet, and can optionally filter on name:

$Worksheet = $Excel | Get-Worksheet
$Worksheet = $Workbook | Get-Worksheet -Name Worksheet1

Let’s take a peak at a worksheet object:

Worksheet

We can see some details, including the dimension of this worksheet.

Freeze some panes

Why bother getting an ExcelPackage, Workbook, or Worksheet? We can use these to manipulate the data and metadata behind the scenes. Maybe we want to freeze the first row:

$WorkSheet | Set-FreezePane -Row 2

The row and column parameters might seem confusing - they indicate the first cell that should not be frozen. So freezing the top row would be row 2, column 1. Freezing the top row and first two columns would be row 2, column 3.

We’ll have to save before we can verify this.

Save and close

Saving and closing uses the ExcelPackage object we first created:

$Excel | Close-Excel -Save

Let’s take a look at our spreadsheet, did it freeze the top row?

Frozen pane

The row is frozen as expected!

Format cells

Management likes pretty colors and formatting. Let’s add some emphasis on 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

Header change

They’re nitpicky. That header is way too big! And the first column should be dark red, and autofit with a maximum width of 7:

#  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 -AutofitMaxWidth 7 -Color DarkRed

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

Format change

Search cells

# Search a spreadsheet
    Search-CellValue -Path C:\test\Demo.xlsx { $_ -like 'jsmith10' -or $_ -eq 280 }

Search

This can return the location (default), the raw value, or an ExcelRange that you can manipulate with more flexibility than Format-Cell provides.

Create tables

Thanks to AWiddersheim for adding table support!

# 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

Create 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

Pivot

Demo Gist

Here’s the full demo code we just walked through:

Fun with GitHub, Pester, and Appveyor

In case it isn’t evident, I haven’t succumbed to test-driven development, as beneficial as it seems. I did add a few Pester tests to PSExcel, and have enabled continuous integration for this project through AppVeyor, so you’ll know whether the build is passing, and you can view the pester tests to see what specifically broke the build.

Build passing

If you want a simple way to enable version control, testing, and continuous integration for your projects, definitely check this out!

Next steps

I plan to continue with a few more tweaks to meet my specific needs, but probably won’t go as deep as Philip has.

That’s it! This should get you up and running with an Excel-free solution for creating and reading Excel files. Feel free to poke around, let me know if you run into any issues, have any suggestions, or would like to contribute!

PSExcel project on Github

Time to update some servers:

Frozen pane