Guido Oliveira
  • Home
  • Sobre
  • Contato
  • Home
  • Sobre
  • Contato
Menu
  • Home
  • Sobre
  • Contato

Criando relatórios customizados no Excel com o ImportExcel

Published by Guido Oliveira on 29/03/2020
Categories
  • Powershell
Tags
  • EPPlus
  • Estilos nomeados Excel
  • Excel named Styles
  • Excel report
  • ImportExcel
  • powershell
Facebook0
Twitter0
LinkedIn0
Google+0
Pinterest0

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!

Share
Guido Oliveira
Guido Oliveira

Related posts

09/02/2021

Removendo acentos de palavras usando Powershell


Read more
08/02/2021

Alterando campos de usuários em lote no Active Directory


Read more
01/02/2021

Como criar usuários em lote no Active Directory


Read more

6 Comments

  1. Joel Fernandes disse:
    29/03/2020 às 10:51 AM

    Guido, espero que esteja tudo bem
    Preciso de uma ajuda sua, mas não é em Excell, mas sim em powershell envolvendo SNMP Services no Windows Server Core 2016, será que pode me ajudar ?
    Agradeço

    Responder
    • Guido Oliveira disse:
      30/03/2020 às 3:34 PM

      Opa, qual a dúvida Joel?

      Responder
  2. Denis Basílio Denis Basílio disse:
    16/07/2020 às 10:07 AM

    Continua para cima.

    Responder
  3. 1080p disse:
    10/02/2021 às 7:36 AM

    Wat weet je die 2 blogs mooi met elkaar te verbinden, Murk! Cristen Brandyn Hafler

    Responder
  4. turkce disse:
    13/02/2021 às 8:04 AM

    Dead composed content , thanks for information . Winna Jereme Trovillion

    Responder
  5. turkce disse:
    13/02/2021 às 8:53 AM

    91.20 will be the answer for numerical reasoning #28. Answer not found on choices even the solution. Daisy Axel Birkle

    Responder

Deixe uma resposta Cancelar resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

Busca

Prêmios

Powershell MVP
Facebook
© 2019 Guido Oliveira. All Rights Reserved.
Menu
  • Home
  • Sobre
  • Contato