Private/Get-CitrixMonthlyReport.ps1
|
<#
.SYNOPSIS Generates a monthly Citrix license usage report by aggregating relevant Excel files. .DESCRIPTION The `Get-CitrixMonthlyReport` function scans a specified folder for Citrix license reports, processes them, sorts the data by license usage, and exports a consolidated report. .PARAMETER ExcelFolder The folder containing the Excel files for Citrix license reports. .PARAMETER MonthlyFolder The folder where the final monthly report should be saved. .EXAMPLE Get-CitrixMonthlyReport -ExcelFolder "C:\Reports\Citrix" -MonthlyFolder "C:\Reports\Monthly" This example searches for Citrix license report files from the previous month in "C:\Reports\Citrix" and generates a consolidated monthly report in "C:\Reports\Monthly". #> Function Get-CitrixMonthlyReport { [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [string]$ExcelFolder, [Parameter(Mandatory = $true)] [string]$MonthlyFolder ) # Ensure ImportExcel module is installed if (-not (Get-Module -ListAvailable -Name ImportExcel)) { Write-Host "ImportExcel module not found. Installing now..." -ForegroundColor Yellow Install-Module -Name ImportExcel -Scope CurrentUser -Force -AllowClobber } # Import the module Import-Module -Name ImportExcel -ErrorAction Stop # Generate date-based identifiers $monthly = (Get-Date).AddMonths(-1).ToString("MMM-yyyy") $y = (Get-Date).AddMonths(-1).ToString("yyyy-MM") # Get all matching Excel files $files = Get-ChildItem -Path $ExcelFolder -Recurse | Where-Object { $_.Name -like "*licreport_$y*.xlsx" } if ($files.Count -eq 0) { Write-Host "No matching reports found for $y" -ForegroundColor Yellow return } $fileName = "MonthlyLicReport_$monthly.xlsx" $outputFile = Join-Path -Path $MonthlyFolder -ChildPath $fileName # Process files efficiently $allData = $files | ForEach-Object { Import-Excel $_.FullName | Select-Object -First 1 } if ($allData.Count -eq 0) { Write-Host "No data found in the reports." -ForegroundColor Yellow return } # Use temporary file for sorting $tempFile = [System.IO.Path]::GetTempFileName() + ".xlsx" # Export unsorted data to temp file $allData | Export-Excel -Path $tempFile -AutoSize -StartRow 1 # Import, sort, and export final file Import-Excel -Path $tempFile | Sort-Object -Property Licensesinuse -Descending | Export-Excel -Path $outputFile -AutoSize -StartRow 1 -TableName "Table1" -WorkSheetName "Processes" # Open the Excel package to apply formatting $excelPackage = Open-ExcelPackage -Path $outputFile $ws = $excelPackage.Workbook.Worksheets["Processes"] if ($ws -ne $null) { Set-Format -WorkSheet $ws -Range "D:D" -NumberFormat "0.00%" -AutoFit Close-ExcelPackage $excelPackage } else { Write-Host "Warning: Worksheet 'Processes' not found. Skipping formatting." -ForegroundColor Yellow } # Cleanup Remove-Item -Path $tempFile -Force Write-Host "Report successfully created: $outputFile" -ForegroundColor Green } |