Generating XLSX files without Excel

Ever got the need to create excel files without the need of having Excel installed.

Well start reading here

The Powershell module is based on EPPLus Library.

Using this nice PSExcel Module you can do a lot.

First thing is to download the Module and install it in this location.



Open Powershell ISE and run the Demo Script.


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

# 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

#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

# Open that Excel file...
    $Excel = New-Excel -Path C:\Temp\Demo.xlsx

# Get a workbook
    $Workbook = $Excel | Get-Workbook

# Get a worksheet - can pipe ExcelPackage or Workbook.
# Filtering on Name is optional
    $Excel | Get-Worksheet
    $WorkSheet = $Workbook | Get-Worksheet -Name Worksheet1

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

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

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

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

# 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

# Fun with pivot tables and charts! Props to Doug Finke
# Get files in your profile, create a chart breaking down size by file extension
    Get-ChildItem $env:USERPROFILE -Recurse -File |
        Export-XLSX -Path C:\Temp\Files.xlsx -PivotRows Extension -PivotValues Length -ChartType Pie

Result is this nice PowerPivot Excel Sheet


Great addition for PS Smile


One Response to Generating XLSX files without Excel

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: