Modules/Private/Reporting/StyleFunctions/Build-AZTIExcelComObject.ps1
|
<# .Synopsis Module for Excel COM Object Customizations .DESCRIPTION This script applies additional customizations to the Excel report using the Excel COM object. .Link https://github.com/thisismydemo/azure-scout/Modules/Private/Reporting/StyleFunctions/Build-AZTIExcelComObject.ps1 .COMPONENT This PowerShell Module is part of Azure Scout (AzureScout) .NOTES Version: 3.6.0 First Release Date: 15th Oct, 2024 Authors: Claudio Merola #> function Build-AZSCExcelComObject { param($File) Write-Debug ((get-date -Format 'yyyy-MM-dd_HH_mm_ss')+' - '+'Validating if Excel is installed (Extra Customizations).') # These customizations drive Excel itself over COM, so they need a local Excel install. On a # machine without one -- every hosted CI runner, every container, most servers -- creating the # COM object fails with 0x80040154 REGDB_E_CLASSNOTREG, and the old code surfaced that raw # exception through Write-Error. The report is already complete and saved by this point, so a # missing Excel is an expected environment condition, not a failure: detect it up front and # say so plainly instead of emitting a red error the operator has to go and diagnose. # (-Lite skips this whole path; that is why the GitHub Action defaults lite to true.) # (AB#5629) $ExcelComRegistered = $false try { $ExcelComRegistered = [bool][System.Type]::GetTypeFromProgID('Excel.Application') } catch { $ExcelComRegistered = $false } if (-not $ExcelComRegistered) { Write-Host '[AzureScout] Microsoft Excel is not installed on this machine, so the optional Excel chart customizations were skipped. The report is complete and saved; every worksheet, chart and pivot built by the report engine is present. Use -Lite to skip this step silently.' -ForegroundColor Yellow Write-Debug ((get-date -Format 'yyyy-MM-dd_HH_mm_ss')+' - '+'Excel COM (ProgID Excel.Application) is not registered -- skipping extra customizations.') return } try { $application = New-Object -ComObject Excel.Application Start-Sleep -Seconds 2 if ($application) { try { $ExApp = $application.Workbooks.Open($File) Start-Sleep -Seconds 3 While ([string]::IsNullOrEmpty($ExApp)) { Start-Sleep -Seconds 1 } Start-Sleep -Milliseconds 500 $WSheet = $ExApp.Worksheets | Where-Object { $_.Name -eq 'Overview' } } catch { Write-Debug ((get-date -Format 'yyyy-MM-dd_HH_mm_ss')+' - '+'Error Opening Excel File.') Write-Error $_ Get-Process excel -ErrorAction SilentlyContinue | Stop-Process -Force -ErrorAction SilentlyContinue return } $NoChangeChart = ('ChartP0', 'ChartP1', 'ChartP2', 'ChartP3', 'ChartP4', 'ChartP5', 'ChartP6', 'ChartP7', 'ChartP8', 'ChartP9', 'AZSC', 'RGs', 'TP00', 'TP0', 'TP1', 'TP2', 'TP3', 'TP4', 'TP5','TP6','TP7','TP8','TP9') $ChangeChart = ('AZSC', 'RGs', 'TP00', 'TP0', 'TP1', 'TP2', 'TP3', 'TP4', 'TP5', 'TP6', 'TP7','TP8','TP9') if ($WSheet.Shapes | Where-Object { $_.name -eq 'ChartP0' }) { ($WSheet.Shapes | Where-Object { $_.name -eq 'ChartP0' }).DrawingObject.Chart.ChartStyle = 294 } Start-Sleep -Milliseconds 50 if ($WSheet.Shapes | Where-Object { $_.name -eq 'ChartP1' }) { ($WSheet.Shapes | Where-Object { $_.name -eq 'ChartP1' }).DrawingObject.Chart.ChartStyle = 222 } Start-Sleep -Milliseconds 50 if ($WSheet.Shapes | Where-Object { $_.name -eq 'ChartP2' }) { ($WSheet.Shapes | Where-Object { $_.name -eq 'ChartP2' }).DrawingObject.Chart.ChartStyle = 294 } Start-Sleep -Milliseconds 50 if ($WSheet.Shapes | Where-Object { $_.name -eq 'ChartP3' }) { ($WSheet.Shapes | Where-Object { $_.name -eq 'ChartP3' }).DrawingObject.Chart.ChartStyle = 268 } Start-Sleep -Milliseconds 50 if ($WSheet.Shapes | Where-Object { $_.name -eq 'ChartP4' }) { ($WSheet.Shapes | Where-Object { $_.name -eq 'ChartP4' }).DrawingObject.Chart.ChartStyle = 294 } Start-Sleep -Milliseconds 50 if ($WSheet.Shapes | Where-Object { $_.name -eq 'ChartP5' }) { ($WSheet.Shapes | Where-Object { $_.name -eq 'ChartP5' }).DrawingObject.Chart.ChartStyle = 222 } Start-Sleep -Milliseconds 50 if ($WSheet.Shapes | Where-Object { $_.name -eq 'ChartP6' }) { ($WSheet.Shapes | Where-Object { $_.name -eq 'ChartP6' }).DrawingObject.Chart.ChartStyle = 294 } Start-Sleep -Milliseconds 50 if ($WSheet.Shapes | Where-Object { $_.name -eq 'ChartP7' }) { ($WSheet.Shapes | Where-Object { $_.name -eq 'ChartP7' }).DrawingObject.Chart.ChartStyle = 268 } Start-Sleep -Milliseconds 50 if ($WSheet.Shapes | Where-Object { $_.name -eq 'ChartP8' }) { ($WSheet.Shapes | Where-Object { $_.name -eq 'ChartP8' }).DrawingObject.Chart.ChartStyle = 294 } Start-Sleep -Milliseconds 50 if ($WSheet.Shapes | Where-Object { $_.name -eq 'ChartP9' }) { ($WSheet.Shapes | Where-Object { $_.name -eq 'ChartP9' }).DrawingObject.Chart.ChartStyle = 268 } Start-Sleep -Milliseconds 50 if ($WSheet.Shapes | Where-Object { $_.name -notin $NoChangeChart -and $_.name -like 'Chart*' }) { ($WSheet.Shapes | Where-Object { $_.name -notin $NoChangeChart -and $_.name -like 'Chart*' }).DrawingObject.Chart.ChartStyle = 315 } Start-Sleep -Milliseconds 50 Foreach ($Changer in $ChangeChart) { ($WSheet.Shapes | Where-Object { $_.name -eq $Changer }).DrawingObject.interior.color = 2500134 ($WSheet.Shapes | Where-Object { $_.name -eq $Changer }).DrawingObject.border.color = 16777215 ($WSheet.Shapes | Where-Object { $_.name -eq $Changer }).DrawingObject.border.ColorIndex = -4142 ($WSheet.Shapes | Where-Object { $_.name -eq $Changer }).DrawingObject.border.LineStyle = -4142 Start-Sleep -Milliseconds 50 } $Draw = ($WSheet.Shapes | Where-Object {$_.name -eq 'AZSC'}) $Draw.Adjustments(1) = 0.07 Start-Sleep -Milliseconds 50 $ExApp.Save() $ExApp.Close() $application.Quit() Get-Process excel -ErrorAction SilentlyContinue | Stop-Process -Force -ErrorAction SilentlyContinue Start-Sleep -Seconds 2 $Excel = New-Object OfficeOpenXml.ExcelPackage $File foreach ($Sheet in $excel.Workbook.Worksheets) { try{ if ($Sheet.name -in ('Overview','Policy', 'Advisor', 'Security Center', 'Subscriptions', 'Quota Usage', 'AdvisorScore', 'Outages', 'Support Tickets', 'Reservation Advisor')) { $Sheet.TabColor = [System.Drawing.Color]::FromName('DarkBlue') } else { $Sheet.TabColor = [System.Drawing.Color]::FromName('LightGray') } } catch { Write-Debug ((get-date -Format 'yyyy-MM-dd_HH_mm_ss')+' - '+'Error Setting Tab Colors.') Write-Error $_ } } $Excel.save() $Excel.Dispose() } } catch { Write-Debug ((get-date -Format 'yyyy-MM-dd_HH_mm_ss')+' - '+'Error Interacting with Excel COM Object.') Write-Error $_ Get-Process excel -ErrorAction SilentlyContinue | Stop-Process -Force -ErrorAction SilentlyContinue return } } |