PI Services

Le blog des collaborateurs de PI Services

Powershell : Générer des fichiers Excel

Introduction

En Powershell, lorsque l'on souhaite exporter des données, le format de fichier le plus couramment prisé et le CSV (comma separated value). Cependant, il arrive souvent que ce fichier soit retravaillé avec de la mise en forme notamment. A ce moment là, il faut donc utiliser Excel et faire quelques clics... Heureusement, Microsoft fournit des classes C# accessibles en Powershell pour toute la suite Office. Il va donc être question de réaliser ces exports de façon automatisée avec la mise en page que l'on souhaite.

Utilisation d'Excel via Interop

Nous verrons ici les principales fonctions pour généré un fichier Excel et le mettre en forme. La totalité des fonctionnalités proposées étant disponible ici : 
http://msdn.microsoft.com/fr-fr/library/microsoft.office.interop.excel(v=office.11).aspx

Tout d'abord il faut appeler Excel en créant un nouvel.
$objExcel = new-object -comobject excel.application
On remarque que cette commande crée un processus Excel.
Pour accéder à un classeur il suffit d'utiliser cette commande (pour un fichier existant) :
$finalWorkBook = $objExcel.WorkBooks.Open($ExcelFilePath)
"$ExcelFilePath" représente le chemin du fichier que l'on souhaite ouvrir
S'il s'agit d'un nouveau fichier :
$finalWorkBook = $objExcel.Workbooks.Add()

Ensuite il est possible d'accéder à chaque onglet en précisant l'index dans la commande ci-dessous :
$finalWorkSheet = $finalWorkBook.Worksheets.Item(1)
Mais on peut aussi le renommer :
$finalWorkBook.Worksheets.Item(1).Name = "MyPSTab"

On peut ensuite accéder aux cellules de l'onglet :
$finalWorkSheet.Cells.Item(4,5) = "MyCell01"
Le premier chiffre est le numéro de la ligne et le second le numéro de la colonne (ci-dessus, ligne 4 - colonne 5). On peut donc facilement réaliser des boucles sur ces index pour changer de cellule.

Pour récupérer la valeur d'une cellule dans le cas où on se sert d'un fichier Excel en entrée d'un script :
myValue = $finalWorkSheet.Cells.Item(1,1).Text

Voici quelques commandes de mise en forme :
Pour mettre un texte en gras :
$finalWorkSheet.Cells.Item(1,4).Font.Bold
Pour surligner une cellule :
$finalWorkSheet.Cells.Item(3,2).Interior.ColorIndex = 42
Le nombre passé en paramètre correpond à une couleur.
Enfin pour ajuster la taille des colonnes automatiquement :
Pour une colonne spécifique (1 est l'index de la colonne):
$finalWorkSheet.Columns.Item(1).Autofit()
Pour l'intégralité du tableau :
$UR = $finalWorkSheet.UsedRange
$UR.EntireColumn.AutoFit()

Lorsque l'on souhaite sauvegarder un classeur Excel il existe deux méthodes. Dans le cas où le fichier existe :
$finalWorkBook.Save()
Si ce n'est pas le cas alors on utilise la méthode SaveAs avec le chemin du fichier :
$finalWorkBook.SaveAs(C:\TestExcelPS.xlsx)

Enfin, on n'oublie pas de fermer le processus ouvert par la création de l'objet COM :
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)

Astuce : Si l'on souhaite voir le résultat pendant que le script s'exécute il suffit de rendre l'objet Excel visible en changeant la valeur de l'attribut éponyme :
$objExcel.Visible =$true

Script d'exemple

Ci-dessous un script réalisant un export des comptes désactivés d'un annuaire Active Directory. Ensuite, les comptes sont triés par unité d'organisation puis exporté dans un fichier Excel. A l'affichage chaque utilisateur est surligné d'une couleur différente en fonction de son unité d'organisation.

##################################################################

# Help                                                                                                        #

##################################################################

<#

    .SYNOPSIS

    Get Disabled Accounts      

    .DESCRIPTION

    Get Disabled Accounts, sort them by OU and Export by Excel or CSV

    .EXAMPLE

    01-Get-DisabledAccounts.ps1

    Description

    -----------

    Get Disabled Accounts, sort them by OU and Export by Excel or CSV

#>

##################################################################

# Main                                                                                                       #

##################################################################

# Récupération du chemin du fichier du script

$RootFolder = Split-Path -Path $MyInvocation.MyCommand.Definition

# Test de la présence du module Active Directory

if((Get-Module ActiveDirectory) -eq $null){

    try{

        Import-Module ActiveDirectory

    }catch{

        Write-Host "The execution computer doesn't have ActiveDirectory Powershell Module. The script can't continue." -ForegroundColor Red

        return

    }

}

# Création de l'objet application Excel sinon on réalise un export au format CSV

try{

    $objExcel = new-object -comobject excel.application

    Write-Host "Excel is installed on this Computer, disabled Users will be export in a fashioned excel file."

    $ExcelTest = $true

}catch{

    Write-Host "Excel is not installed on this Computer, disabled Users will be export in a plain old CSV file."

    $ExcelTest = $false

}

# Génération de la date du jour pour le nom du fichier d'export

$Date = Get-Date -Format ddMMyyyy

# Si Excel est disponible

if($ExcelTest){

    # Génération du chemin du fichier d'export

    Write-Host "Create Excel file"

    $ExcelPath = "$RootFolder\DisabledAccounts_$Date.xlsx"

   # Si le fichier Existe on l'ouvre

    if (Test-Path $ExcelPath) {

        $finalWorkBook = $objExcel.WorkBooks.Open($ExcelPath)

       # On choisi l'onglet sur lequel on travaille

        $finalWorkSheet = $finalWorkBook.Worksheets.Item(1)

        # Donne un nom au à l'onglet

        $finalWorkBook.Worksheets.Item(1).Name = "DisabledAccounts"

    }else{

        # Création d'un nouveau fichier

        $finalWorkBook = $objExcel.Workbooks.Add()

        $finalWorkSheet = $finalWorkBook.Worksheets.Item(1)

        $finalWorkBook.Worksheets.Item(1).Name = "DisabledAccounts"

    }

    #$objExcel.Visible =$true

    Write-Host "Create header"

   # Rempli la première ligne

    $finalWorkSheet.Cells.Item(1,1) = "SamAccountName"

    # Met le texte en gras

    $finalWorkSheet.Cells.Item(1,1).Font.Bold = $True

    $finalWorkSheet.Cells.Item(1,2) = "FirstName";

    $finalWorkSheet.Cells.Item(1,2).Font.Bold = $True

    $finalWorkSheet.Cells.Item(1,3) = "LastName"

    $finalWorkSheet.Cells.Item(1,3).Font.Bold = $True

    $finalWorkSheet.Cells.Item(1,4) = "LastLogonDate"

    $finalWorkSheet.Cells.Item(1,4).Font.Bold = $True

    $finalWorkSheet.Cells.Item(1,5) = "DistinguishedName"

    $finalWorkSheet.Cells.Item(1,5).Font.Bold = $True

}else{

    # Création du chemin du fichier CSV

    $CSVPath = "$RootFolder\DisabledAccounts_$Date.csv"

    # Création du header du fichier CSV

    $Header = "SamAccountName;FirstName;LastName;LastLogonDate;DistinguishedName"

    # Ecriture du header

    $Header | Out-File -FilePath $CSVPath

}

Write-Host "Rrieving data..." -ForegroundColor Green

# Récupération des utilisateurs désactivées

$ListUser = Get-ADUser -Filter {Enabled -eq $false} -Properties * | Select CanonicalName, CN, DistinguishedName, SamAccountName, GivenName, SurName, LastLogonDate, `

@{Name="OU";Expression={(($_.CanonicalName).Substring($_.CanonicalName.IndexOf("/")+1)).replace("/$($_.CN)","")}} `

| Sort-Object OU

Write-Host "Writing data..." -ForegroundColor Green

# On commence à la seconde ligne (la 1ère est consacrée au Header)

$FinalExcelRow = 2

# Choix d'une couleur pour surligner la ligne

$ColorIndex = 41

# Récupéraiton de l'OU du premier utilisateur

if($ListUser.Count -gt 0){

    $OU = $ListUser[0].OU

}

$i = 0

#On boucle sur chaque utilisateur

ForEach($User in $ListUser){

    #On affiche une barre de progression montrant le nombre d'utilisateur déjà traités

    $PercentComplete = [System.Math]::Round($($i*100/($ListUser.Count)),2)

    Write-Progress -Activity "Exporting data to Excel" -status "Effectué : $PercentComplete %" -percentcomplete $($i*100/($ListUser.Count))

    $i++

    if($ExcelTest){

        # Si l'OU est à changé on change aussi l'index de la couleur

        if($OU -ne $User.OU){

            $ColorIndex++

            if($ColorIndex -ge 56){

                $ColorIndex = 3

            }

        }

       # Récupéraiton de l'OU de l'utilisateur en cours

        $OU = $User.OU

        #On stocke les différentes valeurs

        $finalWorkSheet.Cells.Item($FinalExcelRow,1) = $User.SamAccountName

        #On attribut la couleur définit plus haut pour la case concerné

        $finalWorkSheet.Cells.Item($FinalExcelRow,1).Interior.ColorIndex = $ColorIndex

        $finalWorkSheet.Cells.Item($FinalExcelRow,2) = $User.GivenName

        $finalWorkSheet.Cells.Item($FinalExcelRow,2).Interior.ColorIndex = $ColorIndex

        $finalWorkSheet.Cells.Item($FinalExcelRow,3) = $User.SurName

        $finalWorkSheet.Cells.Item($FinalExcelRow,3).Interior.ColorIndex = $ColorIndex

        $finalWorkSheet.Cells.Item($FinalExcelRow,4) = $User.LastLogonDate

        $finalWorkSheet.Cells.Item($FinalExcelRow,4).Interior.ColorIndex = $ColorIndex

        $finalWorkSheet.Cells.Item($FinalExcelRow,5) = $User.DistinguishedName

        $finalWorkSheet.Cells.Item($FinalExcelRow,5).Interior.ColorIndex = $ColorIndex

        # On incrémente le numéro de la ligne en cours d'écriture

       $FinalExcelRow++

    }else{

        $Result = $User.SamAccountName+";"+$User.GivenName+";"+ `                            $User.SurName+ ";"+$User.LastLogonDate+";"+$User.DistinguishedName

        $Result | Out-File -FilePath $CSVPath -Append 

    } 

}

Write-Host "Saving data and closing Excel." -ForegroundColor Green

if($ExcelTest){

    # Sélectionne les cellules utilisées

    $UR = $finalWorkSheet.UsedRange

   # Auto ajustement de la taille de la colonne    

    $null = $UR.EntireColumn.AutoFit()

    if (Test-Path $ExcelPath) {

        # Si le fichier existe déjà, on le sauvegarde

        $finalWorkBook.Save()

    }else{

        # Sinon on lui donne un nom de fichier au moment de la sauvegarde

        $finalWorkBook.SaveAs($ExcelPath)

    }

    # On ferme le fichier

    $finalWorkBook.Close()

}

# Le processus Excel utilisé pour traiter l'opération est arrêté

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)