Public/ReleaseNotes/ExportExcel/Export-Excel.ps1
|
function Export-Excel { <# .SYNOPSIS Converts set of ReleaseNotesDataItems to ExportData. .PARAMETER ExportData Export data prepared by ConvertTo-ExportData. .PARAMETER Path Filename for the exported data to be saved to. .PARAMETER UseConstantFileName Flag, whether to always use the same filename. If Path is a folder, uses constant string for the new file. .PARAMETER Show Flag, whether to open the exported file in associated application. .PARAMETER TimeZone All times from AzureDevOps API are in UTC. Parameter determines the time zone all date times will be converted to. Uses .net TimeZoneInfo class to resolve and calculate the date times. Possible values are: IANA style zone ids, f.e. "Europe/Bratislava" Windows style zone ids, f.e. "Central Europe Standard Time" Default value is "Central Europe Standard Time". .PARAMETER PassThru Flag, whether return the generated file. .PARAMETER ProgressPreference Determines how PowerShell responds to progress updates generated by a script, cmdlet, or provider, such as the progress bars generated by the Write-Progress cmdlet. #> [CmdletBinding(DefaultParameterSetName = 'List')] param( [PSTypeName('PSTypeNames.AzureDevOpsApi.ExportData')] [Parameter(Mandatory, Position = 1)] [Alias('Data')] $ExportData, $Path = '.\', [string] $TimeZone = 'Central Europe Standard Time', [switch] $UseConstantFileName, [Alias('Open')] [switch] $Show, [switch] $PassThru, [System.Management.Automation.ActionPreference] $ProgressPreference = $PSCmdlet.GetVariableValue('ProgressPreference') ) begin { # Show the progress $activity = "Exporting data" Write-Progress -Activity $activity # Import the required modules if (-not (Get-Module -Name ImportExcel)) { Import-Module ImportExcel } # Determine the time zone $targetTimeZone = Get-CustomTimeZone -Id $TimeZone # Determine the output file name $Path = Export-DetermineOutputFileName ` -ExportData $ExportData ` -Path $Path ` -FileExtension 'xlsx' ` -UseConstantFileName:$UseConstantFileName ` -TimeZone $targetTimeZone # Remove the target file, if needed if (Test-Path -Path $Path -PathType Leaf) { $null = Remove-Item -Path $Path -Force } # Open the export file $excelPackage = ImportExcel\Open-ExcelPackage -Create -Path $Path } process { # style properties for different cell types $styles = @{ Header = @{ Bold = $true BackgroundColor = 'Black' FontColor = 'White' } DateTime = @{ Format = 'yyyy-MM-dd HH:mm:ss' HorizontalAlignment = 'Left' TargetTimeZone = $targetTimeZone } Date = @{ Format = 'yyyy-MM-dd' HorizontalAlignment = 'Left' # dates are stored as date times... TargetTimeZone = $targetTimeZone } Link = @{ Underline = $true HorizontalAlignment = 'Left' } } # Export Release metadata Write-Progress -Activity $activity -CurrentOperation "Release" AzureDevOpsApi\Export-ExcelRelease -ExcelPackage $excelPackage -ExportData $ExportData -Styles $styles # Export filtered WorkItems subset; requested by Testers: # Only Bugs, Requirements and User Stories # Only those, which are Resolved or Closed # Only for Development Team Write-Progress -Activity $activity -CurrentOperation "Testing" AzureDevOpsApi\Export-ExcelWorkItems -ExcelPackage $excelPackage -ExportData $ExportData -Styles $styles ` -WorksheetName 'Testing' ` -Filter { # Only Bugs, Requirements and User Stories $isCorrectWIType = ( $_.WorkItemType -ilike 'Bug' ` -or $_.WorkItemType -ilike 'Requirement' ` -or $_.WorkItemType -ilike 'User Story' ` ) # Only those, which are Resolved or Closed $isInCorrectState = ( $_.State -ilike 'Resolved' ` -or $_.State -ilike 'Closed' ` ) # Only Development Team $isForDevelopmentTeam = ( $_.Discipline -ilike 'Development' ` -or ( !$_.Discipline ` -and ($_.Tags -split ';' | Test-StringMasks -Include @('DEV', 'Development')) ` ) ) # Only those, which are to be tested $isCorrectWIType -and $isInCorrectState -and $isForDevelopmentTeam } ` -ExcludeProperties @( 'TestedWorkItem*', 'Target*', 'Requires*', 'RemainingWork', 'CompletedWork', '*Estimate' ) # Export WorkItems subset filtered to Test Cases Write-Progress -Activity $activity -CurrentOperation "Test Cases" AzureDevOpsApi\Export-ExcelWorkItems -ExcelPackage $excelPackage -ExportData $ExportData -Styles $styles ` -WorksheetName 'Test Cases' ` -Filter { $_.WorkItemType -ilike 'Test Case' } ` -ExcludeProperties @( 'Created*', 'Resolved*', 'Closed*', 'Discipline', 'Target*', 'Requires*', 'RemainingWork', 'CompletedWork', '*Estimate', 'AssignedTo*' ) # Export WorkItems subset filtered to Bugs Write-Progress -Activity $activity -CurrentOperation "Bugs" AzureDevOpsApi\Export-ExcelWorkItems -ExcelPackage $excelPackage -ExportData $ExportData -Styles $styles ` -WorksheetName 'Bugs' ` -Filter { $_.WorkItemType -ilike 'Bug' } ` -ExcludeProperties @( 'TestedWorkItem*', 'Target*', 'Requires*', 'RemainingWork', 'CompletedWork', '*Estimate' ) # Export WorkItems subset filtered to Requirements Write-Progress -Activity $activity -CurrentOperation "Requirements" AzureDevOpsApi\Export-ExcelWorkItems -ExcelPackage $excelPackage -ExportData $ExportData -Styles $styles ` -WorksheetName 'Requirements' ` -Filter { $_.WorkItemType -ilike 'Requirement' } ` -ExcludeProperties @( 'Requires*', 'Discipline', 'RemainingWork', 'CompletedWork', 'RequiresTest' ) # Export WorkItems subset filtered to Features and Epics Write-Progress -Activity $activity -CurrentOperation "Features & Epics" AzureDevOpsApi\Export-ExcelWorkItems -ExcelPackage $excelPackage -ExportData $ExportData -Styles $styles ` -WorksheetName 'Features & Epics' ` -Filter { $_.WorkItemType -iin @('Feature','Epic') } ` -ExcludeProperties @( 'TestedWorkItem*', 'Created*', 'Resolved*', 'Closed*', 'Discipline', 'Target*', 'Requires*', 'RemainingWork', 'CompletedWork', '*Estimate', 'AssignedTo*' ) # Export WorkItems subset Write-Progress -Activity $activity -CurrentOperation "Work Items" AzureDevOpsApi\Export-ExcelWorkItems -ExcelPackage $excelPackage -ExportData $ExportData -Styles $styles # Export Relations subset Write-Progress -Activity $activity -CurrentOperation "Relations" AzureDevOpsApi\Export-ExcelRelations -ExcelPackage $excelPackage -ExportData $ExportData -Styles $styles # Export Console subset Write-Progress -Activity $activity -CurrentOperation "Console" AzureDevOpsApi\Export-ExcelConsole -ExcelPackage $excelPackage -ExportData $ExportData -Styles $styles } end { try { # Close the export file properly ImportExcel\Close-ExcelPackage -ExcelPackage $excelPackage } finally { # Ensure to remove the progress bar Write-Progress -Activity $activity -Completed } # PassThru the file, if requested if ($PassThru.IsPresent -and ($PassThru -eq $true)) { Get-ChildItem -Path $Path } # Open in associated application if requested if ($Show.IsPresent -and $Show -eq $true) { Start-Process -FilePath $Path } } } |