Private/Get-CitrixDailyReport.ps1
|
<#
.SYNOPSIS Converts a daily Citrix license CSV report to an Excel file, sorts the data, and applies formatting. .DESCRIPTION The `Get-CitrixDailyReport` function reads a CSV report, removes invalid characters, ensures unique column names, and then exports the cleaned data into an Excel file with sorting applied. .PARAMETER csvfile The full path to the CSV file containing the daily Citrix license report. .PARAMETER Folder The folder where the final Excel report should be saved. .EXAMPLE Get-CitrixDailyReport -csvfile "C:\Reports\Daily\CitrixReport.csv" -Folder "C:\Reports\Processed" #> Function Get-CitrixDailyReport { [CmdletBinding()] param ( [Parameter(Mandatory = $true, ValueFromPipeline = $true)] [string]$csvfile, [Parameter(Mandatory = $true, ValueFromPipeline = $true)] [string]$Folder ) # Ensure ImportExcel module is installed if (-not (Get-Module -ListAvailable -Name ImportExcel)) { Write-Host "`n[INFO] ImportExcel module not found. Installing now..." -ForegroundColor Yellow try { Install-Module -Name ImportExcel -Scope CurrentUser -Force -AllowClobber } catch { Write-Host "`n[ERROR] Failed to install ImportExcel module. Please install it manually." -ForegroundColor Red return } } Import-Module -Name ImportExcel -ErrorAction Stop # Get the file name without extension $Name = [System.IO.Path]::GetFileNameWithoutExtension($csvfile) # Define output file paths $xlsxfile = Join-Path -Path $Folder -ChildPath "$Name.xlsx" $rguid = ([guid]::NewGuid()).Guid $tempFile = Join-Path -Path $env:TEMP -ChildPath "$rguid.xlsx" # Remove existing output file if present if (Test-Path -Path $xlsxfile) { Remove-Item -Path $xlsxfile -Force } try { Write-Host "`n[INFO] Processing Citrix daily report..." -ForegroundColor Cyan # Convert cleaned CSV data to objects $cleanData = Import-Csv $csvfile # Export cleaned data to temporary Excel file $cleanData | Export-Excel -Path $tempFile -TableName "CitrixLicenseReport" -AutoSize -TitleBold # Read back, sort, and export final output file Import-Excel -Path $tempFile | Sort-Object -Property Licensesinuse -Descending | Export-Excel -Path $xlsxfile -AutoSize -StartRow 1 -TableName "CitrixReport" Write-Host "`n[SUCCESS] File created successfully at location: $xlsxfile" -ForegroundColor Green } catch { Write-Host "`n[ERROR] Failed to process Citrix daily report: $($_.Exception.Message)" -ForegroundColor Red } finally { # Cleanup temporary file if (Test-Path -Path $tempFile) { Remove-Item -Path $tempFile -Force } } } |