PowerShell – Generating XLSX files without Excel

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

Well start reading here http://ramblingcookiemonster.github.io/PSExcel-Intro/

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.

C:\Users\YourUser\Documents\WindowsPowerShell\Modules\PSExcel

Open Powershell ISE and run the Demo Script.

CLS

# Download PSExcel from https://github.com/RamblingCookieMonster/PSExcel
# 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