EventsToExcel

Here’s an example working on the automation of Excel worksheet filling.
For this example, data comes from the eventlog, but obviously any data input could be used… We pick the last x entries for each non-empty logfile, and create a worksheet for each one.

<#
.SYNOPSIS
    EventsToExcel - Export Event Log in an Excel Workbook
.DESCRIPTION
    EventsToExcel - Export Event Log in an Excel Workbook
    Export localhost event log entries in a worksheet for each log file
.PARAMETER Newest
    Defines the last x entries of each log file to retreive
    Default is "10".
.PARAMETER Display
    Defines if the Excel Workbook is open or not during its filling
    Default is "No".
.PARAMETER Exclude
    Defines if a non-empty logfile is to exclude in the report
    Default is "$null".
.PARAMETER Include
    Defines the only logfile to include in the report
    Default is "$null".
.NOTES
    File Name	: EventsToExcel.ps1
    Author	: Fabrice ZERROUKI - fabricezerrouki@hotmail.com
.EXAMPLE
    PS D:\>EventsToExcel.ps1 -Newest 100
    Creates an Excel workbook with a worksheet filled with the last 100 entries for each logfile
.EXAMPLE
    PS D:\>EventsToExcel.ps1 -Newest 5 -Display Yes
    Creates an Excel workbook with a worksheet filled with the last 5 entries for each logfile
	Opens Excel during the data filling
.EXAMPLE
    PS D:\>EventsToExcel.ps1 -Newest All -Include Application -Display Yes
    Creates an Excel workbook with a worksheet filled with all the entries of the Application logfile
	Opens Excel during the data filling
.EXAMPLE
    PS D:\>EventsToExcel.ps1 -Exclude System
    Creates an Excel workbook with a worksheet filled with the last 10 entries for each non-empty logfile, except the System logfile
#>
Param(
    $Newest="10",
    [ValidateSet("Yes", "No")]
    $Display="No",
	$Exclude,
	$Include
    )
	
function Release-Ref ($ref) { 
([System.Runtime.InteropServices.Marshal]::ReleaseComObject( 
[System.__ComObject]$ref) -gt 0) 
[System.GC]::Collect() 
[System.GC]::WaitForPendingFinalizers() 
}
$logs=Get-EventLog -List | Where {$_.Entries -ne $null} | Sort-Object Log -descending | ForEach {$_.Log}
if($Exclude) {$logs=@($logs |? {$_ -ne $Exclude})}
if($Include) {$logs=@($Include)}
$i=1

$xl=New-Object -Com Excel.Application
if($Display -eq "Yes") {$xl.Visible=$True} else {$xl.Visible=$False}
$wb=$xl.Workbooks.Add()
$xl.DisplayAlerts=$False
$wb.WorkSheets.Item(1).Delete()
$wb.WorkSheets.Item(1).Delete()

ForEach ($log in $logs) {
Write-Progress -Id 1 -Activity "Collecting `"$env:computername`" Event Logs data." -Status "Progress:" -PercentComplete ($i/($logs.count)*100)
$j=1
$i++
$wb.Sheets.Add() | Out-Null
$ws=$wb.ActiveSheet
$cells=$ws.Cells
# Getting the data
if($Newest -eq "All") {$events=Get-EventLog -LogName $log -ErrorAction SilentlyContinue | Select Timegenerated, EventID, EntryType, Source, Message}
else {$events=Get-EventLog -LogName $log -Newest $Newest -ErrorAction SilentlyContinue | Select Timegenerated, EventID, EntryType, Source, Message}
# Inserting a title in the worksheet
$cells.item(1,1)="Eventlog Export"
$cells.item(1,1).font.bold=$True
$cells.item(1,1).font.size=18
$cells.item(1,5)="$log"
$cells.item(1,5).font.size=18
# Defining the position to begin to fill data
$row=3
$col=1
# Inserting the column headings
"Timegenerated","EventID","EntryType","Source","Message" | ForEach {
    $cells.item($row,$col)=$_
    $cells.item($row,$col).font.bold=$True
    $col++
}

# Filling the worksheet with the data
ForEach ($event in $events) {
    Write-Progress -Id 2 -Activity "Filling the Excel Worksheet for the `"$log`" logfile. Please wait..." -Status "Progress:" -PercentComplete ($j/($events.length)*100)
    $j++
    $row++
    $col=1
    $cells.item($Row,$col)=$event.Timegenerated
    $cells.item($Row,$col).HorizontalAlignment=-4131
    $cells.item($Row,$col).VerticalAlignment=-4160
    $col++
    $cells.item($Row,$col)=$event.EventID
    $cells.item($Row,$col).HorizontalAlignment=-4131
    $cells.item($Row,$col).VerticalAlignment=-4160
    $col++
    $cells.item($Row,$col)=$event.EntryType
    $cells.item($Row,$col).HorizontalAlignment=-4131
    $cells.item($Row,$col).VerticalAlignment=-4160
    $col++
    $cells.item($Row,$col)=$event.Source
    $cells.item($Row,$col).HorizontalAlignment=-4131
    $cells.item($Row,$col).VerticalAlignment=-4160
    $col++
    $cells.item($Row,$col)=$event.Message
    $cells.item($Row,$col).HorizontalAlignment=-4131
    $cells.item($Row,$col).VerticalAlignment=-4160
    $cells.item($Row,$col).rowheight=60
	$ws.UsedRange.EntireColumn.Autofit() | Out-Null
} # EndOf ForEach ($event in $events)
$ws.UsedRange.EntireColumn.Autofit() | Out-Null
$ws.Name="$log"
} # EndOf ForEach ($log in $logs)
$last=$logs.count + 1
$wb.WorkSheets.Item($last).Delete()
#Saving the worksheet
$OutputPath="$pwd" + "\" + "$env:computername" + "-EventsToExcel-Last_" + "$Newest" + "_Events.xlsx"
$wb.SaveAs($OutputPath) | Out-Null
$wb.Close($false)

$xl.Quit()
Release-Ref($ws) | Out-Null
Get-Process EXCEL | Stop-Process