Public/Compare-DSCEAcsv.ps1
|
<# .SYNOPSIS Compare pre-defined .xlsx file with DSCEA csv output to check baseline items. .DESCRIPTION Read baseline check excel file. Get the DSCEA csv file. Which could refer DSCEA official webstie. Compare items in .xlsx file with outputs. Fill compare results up to excel file. .PARAMETER xlsxfile Input .xlsx file which pre-defined format. .PARAMETER csvfile Input .csv file which generated by DSCEA. .EXAMPLE PS C:\>Import-Module PSExcel PS C:\>Compare-DSCEAcsv Run Directly will prompt provide excel file and csvfile. In most cases, PSExcel module must be imported explicitly. .OUTPUTS The compare result write to predefined excel file's corresponded columns. #> function Compare-DSCEAcsv { param ( [Parameter(Mandatory = $true, HelpMessage = 'Path to baseline report xlsx file.')] [ValidateNotNullOrEmpty()] [string] $xlsxfile, [Parameter(Mandatory = $true, HelpMessage = 'Path to DSCEA output csv file.')] [ValidateNotNullOrEmpty()] [string] $csvfile ) $csvVar = Import-Csv -Path $csvfile $ExcelVar = New-Excel -Path $xlsxfile foreach ($ExcelSheet in $ExcelVar.Workbook.Worksheets) { $ExcelSheet.Tables | ForEach-Object { $Coordinates = $_.address.address $ColumnStart = ($($Coordinates -split ":")[0] -replace "[0-9]", "").ToUpperInvariant() $ColumnEnd = ($($Coordinates -split ":")[1] -replace "[0-9]", "").ToUpperInvariant() [int]$RowStart = $($Coordinates -split ":")[0] -replace "[a-zA-Z]", "" [int]$RowEnd = $($Coordinates -split ":")[1] -replace "[a-zA-Z]", "" $Rows = $RowEnd - $RowStart + 1 $ColumnStart = Get-ExcelColumnInt $ColumnStart $ColumnEnd = Get-ExcelColumnInt $ColumnEnd $Columns = $ColumnEnd - $ColumnStart + 1 for ($i = $ColumnStart; $i -le $Columns; $i++) { if ($ExcelSheet.GetValue($RowStart, $i) -eq "DSCInstanceName") { $TestItemCol = $i } if ($ExcelSheet.GetValue($RowStart, $i) -eq "Compliance") { $ComplianceCol = $i } } if ($TestItemCol -ne $null -and $ComplianceCol -ne $null) { for ($i = $RowStart + 1; $i -lt $Rows; $i++) { if ($ExcelSheet.GetValue($i, $TestItemCol) -ne $null) { $csvVar | ForEach-Object { if ($_.InstanceName -match [regex]::escape($ExcelSheet.GetValue($i, $TestItemCol))) { $ExcelSheet.SetValue($i, $ComplianceCol, $_.InDesiredState) } } } } } } } $ExcelVar | Save-Excel -Close } |