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