Criando relatórios customizados no Excel com o ImportExcel

Olá pessoal,

Nos projetos que participo, frequentemente preciso gerar relatorios extraidos do Azure, Active Directory, Windows Server, etc. A forma preferida dos clientes ainda é o Excel e o MVP Doug Finke criou um modulo para facilitar isso chamado ImportExcel. Este modulo usa a biblioteca EPPlus e permite criar pastas de trabalho do Excel sem a necessidade de ter o Excel instalado na maquina que for executar a rotina.

Essa biblioteca não tem por padrão os estilos estilos nomeados(Named Styles) do Excel, porém permite a criação deles:

Video:

Script:

Import-Module -Name ImportExcel

$Workbook = Get-Service | Select-Object -Property Name, DisplayName, StartType, Status | Export-Excel -Path .\Services.xlsx -Style @{HorizontalAlignment = "Center" } -PassThru
$Worksheet = $Workbook.Workbook.Worksheets["Sheet1"]

$Styles = @($Workbook.Workbook.Styles.NamedStyles.Name)
if (-not($Styles.Contains('Good'))) {
    [OfficeOpenXml.Style.XmlAccess.ExcelNamedStyleXml]$GoodStyle = $Workbook.workBook.Styles.CreateNamedStyle("Good")
    $GoodStyle.Style.Font.Name = "Calibri"
    $GoodStyle.Style.Font.Family = 2
    $GoodStyle.Style.Font.Size = 11
    $GoodStyle.Style.Font.Color.SetColor([System.Drawing.ColorTranslator]::FromHtml('#006100'))
    $GoodStyle.Style.Fill.PatternType = [OfficeOpenXml.Style.ExcelFillStyle]::Solid
    $GoodStyle.Style.Fill.BackgroundColor.SetColor([System.Drawing.ColorTranslator]::FromHtml('#C6EFCE'))
}
if (-not($Styles.Contains('Bad'))) {
    [OfficeOpenXml.Style.XmlAccess.ExcelNamedStyleXml]$BadStyle = $Workbook.workBook.Styles.CreateNamedStyle("Bad")
    $BadStyle.Style.Font.Name = "Calibri"
    $BadStyle.Style.Font.Family = 2
    $BadStyle.Style.Font.Size = 11
    $BadStyle.Style.Font.Color.SetColor([System.Drawing.ColorTranslator]::FromHtml('#9C0006'))
    $BadStyle.Style.Fill.PatternType = [OfficeOpenXml.Style.ExcelFillStyle]::Solid
    $BadStyle.Style.Fill.BackgroundColor.SetColor([System.Drawing.ColorTranslator]::FromHtml('#FFC7CE'))
}
if (-not($Styles.Contains('Neutral'))) {
    [OfficeOpenXml.Style.XmlAccess.ExcelNamedStyleXml]$NeutralStyle = $Workbook.workBook.Styles.CreateNamedStyle("Neutral")
    $NeutralStyle.Style.Font.Name = "Calibri"
    $NeutralStyle.Style.Font.Family = 2
    $NeutralStyle.Style.Font.Size = 11
    $NeutralStyle.Style.Font.Color.SetColor([System.Drawing.ColorTranslator]::FromHtml('#9C5700'))
    $NeutralStyle.Style.Fill.PatternType = [OfficeOpenXml.Style.ExcelFillStyle]::Solid
    $NeutralStyle.Style.Fill.BackgroundColor.SetColor([System.Drawing.ColorTranslator]::FromHtml('#FFEB9C'))
}

Set-ExcelRange -Worksheet $Worksheet -Range "A1:D1" -Bold -FontSize '12' -BackGroundColor 'LIGHTSKYBLUE'

for ($i = 2; $i -le $Worksheet.Dimension.Rows; $i++) {
    switch ($Worksheet.Cells["C$i"].Value) {
        'Automatic' {
            if ($Worksheet.Cells["D$i"].Value -eq 'Running') {
                $Worksheet.Cells["A$i"].StyleName = 'Good'
                $Worksheet.Cells["B$i"].StyleName = 'Good'
                $Worksheet.Cells["C$i"].StyleName = 'Good'
                $Worksheet.Cells["D$i"].StyleName = 'Good'
            }
            else {
                $Worksheet.Cells["A$i"].StyleName = 'Bad'
                $Worksheet.Cells["B$i"].StyleName = 'Bad'
                $Worksheet.Cells["C$i"].StyleName = 'Bad'
                $Worksheet.Cells["D$i"].StyleName = 'Bad'
            } 
        }
        'Disabled' {
            if ($Worksheet.Cells["D$i"].Value -eq 'Running') {
                $Worksheet.Cells["A$i"].StyleName = 'Neutral'
                $Worksheet.Cells["B$i"].StyleName = 'Neutral'
                $Worksheet.Cells["C$i"].StyleName = 'Neutral'
                $Worksheet.Cells["D$i"].StyleName = 'Neutral'
            }
            else {
                $Worksheet.Cells["A$i"].StyleName = 'Good'
                $Worksheet.Cells["B$i"].StyleName = 'Good'
                $Worksheet.Cells["C$i"].StyleName = 'Good'
                $Worksheet.Cells["D$i"].StyleName = 'Good'
            } 
        }
        'Manual' {
            $Worksheet.Cells["A$i"].StyleName = 'Neutral'
            $Worksheet.Cells["B$i"].StyleName = 'Neutral'
            $Worksheet.Cells["C$i"].StyleName = 'Neutral'
            $Worksheet.Cells["D$i"].StyleName = 'Neutral'
        }
        Default { }
    }
}

$Table = $Worksheet.Cells
$Table.Style.Border.Top.Style = [OfficeOpenXml.Style.ExcelBorderStyle]::Thin
$Table.Style.Border.Left.Style = [OfficeOpenXml.Style.ExcelBorderStyle]::Thin
$Table.Style.Border.Right.Style = [OfficeOpenXml.Style.ExcelBorderStyle]::Thin
$Table.Style.Border.Bottom.Style = [OfficeOpenXml.Style.ExcelBorderStyle]::Thin

$Worksheet.Cells[$Worksheet.Dimension.Address].AutoFitColumns()

Close-ExcelPackage -ExcelPackage $Workbook -Show

Dúvidas? Sugestões? Comente!

Até a próxima!