PowerShell Excel Automation with ImportExcel Module

Management wants the server health report in Excel — properly formatted, with a table header, color-coded status rows, and a chart. The server running the script has no Excel installed. The free PowerShell excel automation solution is Doug Finke’s ImportExcel module, which creates full .xlsx files using the EPPlus library with no Excel dependency. This post covers installation, basic export, table formatting, conditional formatting, chart creation, and multi-worksheet reports.

Install ImportExcel Module

Install the module from the PowerShell Gallery. It works on Windows PowerShell 5.1 and PowerShell 7, and requires no Excel installation:

# Install for current user (no admin required)
Install-Module ImportExcel -Scope CurrentUser -Force

# Verify installation
Get-Module ImportExcel -ListAvailable | Select-Object Name, Version

Import-Module ImportExcel
Write-Host "ImportExcel $(( Get-Module ImportExcel).Version) loaded"

Export Data to Excel with Export-Excel

Export-Excel is the core cmdlet. It accepts pipeline input just like Export-Csv but writes a proper .xlsx file:

$reportPath = "C:\Reports\ServerHealth_$(Get-Date -Format 'yyyyMMdd').xlsx"

# Gather server data
$servers = @('Server01', 'Server02', 'Server03')
$data = $servers | ForEach-Object {
    $os   = Get-CimInstance Win32_OperatingSystem -ComputerName $_ -ErrorAction SilentlyContinue
    $disk = Get-CimInstance Win32_LogicalDisk -Filter "DeviceID='C:'" -ComputerName $_
    [PSCustomObject]@{
        Server       = $_
        OS           = $os.Caption
        FreeDiskGB   = [Math]::Round($disk.FreeSpace / 1GB, 1)
        TotalDiskGB  = [Math]::Round($disk.Size / 1GB, 1)
        FreePct      = [Math]::Round($disk.FreeSpace / $disk.Size * 100, 0)
        LastBoot     = $os.LastBootUpTime
    }
}

$data | Export-Excel -Path $reportPath -AutoSize -AutoFilter -FreezeTopRow -WorksheetName "Servers"
Write-Host "Exported to $reportPath"

Apply Table Formatting

The -TableName and -TableStyle parameters wrap the data in a proper Excel table with alternating row colors and column headers:

$data | Export-Excel -Path $reportPath `
    -WorksheetName "Servers" `
    -TableName "ServerHealth" `
    -TableStyle Medium6 `
    -AutoSize -FreezeTopRow -AutoFilter

# TableStyle options: Light1-21, Medium1-28, Dark1-11

Add Conditional Formatting

Color-code rows based on values using Add-ConditionalFormatting after writing the data. This requires opening the package to add rules:

$excel = $data | Export-Excel -Path $reportPath -WorksheetName "Servers" `
    -TableName "ServerHealth" -TableStyle Medium6 -AutoSize -PassThru

$ws = $excel.Workbook.Worksheets["Servers"]

# Red fill on cells in FreePct column where value is < 15
Add-ConditionalFormatting -Worksheet $ws `
    -Range "E2:E$($data.Count + 1)" `
    -RuleType LessThan -ConditionValue 15 `
    -ForegroundColor Red -Bold

# Yellow fill where value is < 25
Add-ConditionalFormatting -Worksheet $ws `
    -Range "E2:E$($data.Count + 1)" `
    -RuleType Between -ConditionValue 15 -ConditionValue2 25 `
    -BackgroundColor Yellow

Close-ExcelPackage $excel -Save
Write-Host "Conditional formatting applied"

Create a Chart

Add a bar chart showing disk free percentage per server:

$chart = New-ExcelChartDefinition `
    -ChartType BarClustered `
    -XRange "Servers!A2:A$($data.Count + 1)" `
    -YRange "Servers!E2:E$($data.Count + 1)" `
    -Title "Disk Free %" `
    -SeriesHeader "Free %" `
    -Row 2 -RowOffsetPixels 0 -Column 7 -Width 400 -Height 250

$data | Export-Excel -Path $reportPath -WorksheetName "Servers" `
    -TableName "ServerHealth" -TableStyle Medium6 -AutoSize `
    -ExcelChartDefinition $chart

Write-Host "Chart added to $reportPath"

Multiple Worksheets

Build a multi-tab workbook by using -PassThru to hold the package open between worksheet writes, then close it once at the end:

$excel = $data | Export-Excel -Path $reportPath -WorksheetName "Servers" `
    -TableName "TblServers" -TableStyle Medium6 -AutoSize -PassThru

# Add a second sheet with disk data only
$diskData = $data | Select-Object Server, FreeDiskGB, TotalDiskGB, FreePct
$excel    = $diskData | Export-Excel -ExcelPackage $excel -WorksheetName "DiskSpace" `
    -TableName "TblDisk" -TableStyle Light2 -AutoSize -PassThru

# Add a summary sheet
$summary = [PSCustomObject]@{ ReportDate = Get-Date; ServerCount = $data.Count; LowDisk = ($data | Where-Object FreePct -lt 20).Count }
$excel   = $summary | Export-Excel -ExcelPackage $excel -WorksheetName "Summary" -AutoSize -PassThru

Close-ExcelPackage $excel -Save
Write-Host "Multi-sheet workbook saved: $reportPath"

Common Errors and Fixes

• Cell type mismatch causes Excel to show text as numbers. If a column contains mixed types (some values are strings, some integers), Excel may not recognize numeric columns as numbers. Use typed calculated properties — for example, @{N='FreePct'; E={[int]($disk.FreeSpace / $disk.Size * 100)}} — to ensure consistent types.
• Module requires .NET Framework compatibility. ImportExcel uses EPPlus, which on Windows PowerShell 5.1 requires .NET Framework 4.5+. On PowerShell 7, it uses .NET 6+. If you see assembly load errors, verify you are running a supported PowerShell version with $PSVersionTable.

Related Cmdlets / See Also

• Powershell Export Csv
• Powershell Server Health Check

Wrapping Up

ImportExcel transforms PowerShell into a full Excel report generator with no Office dependency. Start with Export-Excel -AutoSize -TableStyle Medium6 for instant professional formatting, add conditional formatting to highlight problem rows, and use -PassThru with Close-ExcelPackage to build multi-worksheet workbooks in a single pipeline.