Comment manipuler des fichiers Excel avec PowerShell ?

I. Présentation

Microsoft Excel est un logiciel très populaire en entreprise, et on l'utilise très souvent comme une petite base de données afin de stocker des données diverses et variées. Au sein même d'un service informatique, c'est un outil très utile, au même titre que peut l'être PowerShell, alors pourquoi ne pas combiner l'usage de ces deux outils ?

Lorsque l'on utilise PowerShell, il n'est pas rare de manipuler des fichiers de données au format CSV, notamment pour exporter ou importer des données afin de les exploiter dans un script. Dans le cas d'Excel, on peut également réaliser plusieurs actions : importer un fichier Excel, exporter des données dans un fichier Excel, écrire dans une cellule spécifique d'un fichier Excel, etc... Le tout avec PowerShell.

Ainsi, on peut imaginer un script qui va alimenter un fichier Excel, et ce dernier sera facilement consultable depuis un poste de travail, grâce à l'affichage sous forme de tableur. Vous l'aurez compris, dans ce tutoriel, nous allons apprendre à manipuler des fichiers Excel avec PowerShell. L'usage d'Excel à partir de PowerShell est un sujet vaste tant les possibilités sont nombreuses, donc ce tutoriel sera une première introduction pour vous permettre de démarrer sur de bonnes bases !

II. PowerShell et Excel : les prérequis

Depuis 2015, Douglas Finke développe un module nommé "ImportExcel" qui permet de manipuler des fichiers Excel avec PowerShell. Ce module populaire est disponible sur la PowerShell Gallery et compte plus de 1,4 million de téléchargements ! Grâce à ce module, il est possible d'effectuer de nombreuses opérations : importer les données d'un fichier Excel, exporter au format Excel, ajouter des onglets, des tableaux, des graphiques, mais aussi déclarer une formule, voire même changer la couleur de fond d'une cellule ou créer des règles de mise en forme conditionnelle.

Voici le lien vers la page du module :

• PowerShell Gallery - ImportExcel

Pour interagir avec Excel à partir de PowerShell sur Windows, c'est le seul prérequis ! En effet, il n'est pas nécessaire d'installer Microsoft Excel sur la machine où vous souhaitez manipuler des fichiers Excel avec PowerShell, ce qui est un avantage intéressant sur les serveurs.

III. Installer le module ImportExcel de PowerShell

L'installation de ce module s'effectue très simplement, à l'aide de la commande Install-Module que l'on utilise habituellement pour installer un module. Ce qui donne :

Install-Module -Name ImportExcel

Pour rappel, il est également possible d'installer le module uniquement pour l'utilisateur actuellement connecté sur la machine :

Install-Module -Name ImportExcel -Scope CurrentUser

Quelques secondes plus tard, le tour est joué : vous êtes prêt à passer à la suite !

Sachez que vous pouvez lister l'ensemble des commandes disponibles dans ce module via la commande suivante :

Get-Command -Module ImportExcel

IV. Exporter des données dans Excel avec PowerShell

L'export de données dans un fichier Excel, reprend un peu le même principe que l'export CSV avec PowerShell. Très facilement, nous pouvons exporter le résultat d'une commande dans un fichier Excel. Prenons un exemple où nous allons exporter la liste des comptes d'un annuaire Active Directory dans un fichier Excel.

La commande ci-dessous récupère la liste de tous les utilisateurs de l'Active Directory et exporte cette liste dans un fichier nommé "AD-utilisateurs.xlsx" qui sera généré dans le répertoire courant.

Get-ADUser -Filter * | Export-Excel -Path './AD-utilisateurs.xlsx'

Quelques secondes plus tard, on obtient le résultat suivant :

Dans cet exemple, les colonnes ne sont pas dimensionnées automatiquement, ce qui n'est très pratique pour lire. La bonne nouvelle, c'est que le cmdlet Export-Excel dispose de nombreux paramètres, notamment :

• -AutoSize pour redimensionner les colonnes automatiquement selon la largeur du contenu
• -WorksheetName pour nommer l'onglet dans le tableau Excel
• -StartColumn pour écrire les données à partir de la Xème colonnes
• -StartRow pour écrire les données à partir de la Xème lignes
• -Append pour ajouter les données à la suite des données existantes dans le fichier
• -ClearSheet pour supprimer les données avant d'ajouter les nouvelles données (uniquement dans l'onglet/la feuille, si un onglet est précisé)
• Etc.

Get-ADUser -Filter * | Export-Excel -Path './AD-utilisateurs.xlsx' -AutoSize -WorksheetName "Domaine it-connect.local"

Cette fois-ci, les colonnes sont correctement dimensionnées, et l'onglet est bien nommé comme je l'ai demandé :

Voici un autre exemple qui va permettre de laisser les 3 premières lignes et les 3 premières colonnes vierges :

Get-ADUser -Filter * | Export-Excel -Path './AD-utilisateurs.xlsx' -AutoSize -WorksheetName "Domaine it-connect.local" -StartColumn 4 -StartRow 4

La preuve en image :

Dans le but d'exporter uniquement certaines propriétés, on peut ajouter la commande Select-Object afin de sélectionner les propriétés que l'on souhaite exporter. Voici un exemple pour exporter dans le fichier Excel uniquement deux champs : SamAccountName et Enabled.

Get-ADUser -Filter * | Select-Object SamAccountName,Enabled | Export-Excel -Path './AD-utilisateursBis.xlsx' -AutoSize -WorksheetName "Domaine it-connect.local"

À tout moment, vous pouvez obtenir de l'aide sur l'utilisation de cette commande, ou d'une autre commande, via :

Get-Help Export-Excel

Si vous n'avez pas d'Active Directory sous la main, vous pouvez exporter d'autres données, comme la liste des processus actifs sur votre machine ou l'état des services.

Get-Service | Export-Excel -Path './Services.xlsx' -AutoSize -WorksheetName "Etat des services"

V. Importer un fichier Excel avec PowerShell

Dans cette partie, nous allons voir que l'on peut aussi importer un fichier Excel dans PowerShell afin de lire son contenu. Cette fois-ci, c'est le cmdlet "Import-Excel" qui va être notre alliée pour importer le contenu d'un classeur Excel. Voici un exemple pour importer le fichier que l'on a importé précédemment :

Import-Excel -Path '.\AD-utilisateursBis.xlsx'

Ce qui donne :

Si l'on reprend l'exemple du premier fichier Excel généré, avec de nombreuses colonnes, mais que l'on souhaite en importer uniquement certaines, c'est également possible via le paramètre -HeaderName. On peut également aller encore plus loin en précisant le nom de l'onglet du fichier Excel dans lequel il faut récupérer les données, via le paramètre -WorksheetName. Voici un exemple pour récupérer uniquement les colonnes DistinguishedName, Enabled et SamAccountName de l'onglet "Domaine it-connect.local" de notre fichier. On commence à la ligne 2 (-StartRow 2) pour ne pas récupérer la ligne d'en-tête. Ce qui donne :

Import-Excel -Path '.\AD-utilisateurs.xlsx' -HeaderName DistinguishedName,Enabled,SamAccountName -WorksheetName "Domaine it-connect.local" -StartRow 2

Le résultat obtenu dans la console PowerShell, et que l'on pourrait tout à fait stocker dans une variable, est plutôt satisfaisant :

Il faut bien respecter l'ordre des colonnes que l'on sélectionne avec -HeaderName, telles qu'elles sont positionnées dans le fichier Excel, sinon les noms de colonnes ne vont pas correspondre aux valeurs. Pour organiser les colonnes dans l'ordre que l'on souhaite dès l'import des données, il faut le faire en deux temps, de cette façon :

Import-Excel -Path '.\AD-utilisateurs.xlsx' -WorksheetName "Domaine it-connect.local" | Select-Object DistinguishedName,SamAccountName,Enabled

On voit bien, dans cet exemple, que j'ai pu inverser l'ordre des colonnes Enabled et SamAccountName par rapport au premier exemple.

Parfois, nous ne connaissons pas forcément la contenance du fichier Excel, ou en tout cas le nom exact des onglets. La commande Get-ExcelFileSummary permet d'obtenir des informations sur un fichier Excel, notamment le nom de chaque onglet, avec le nombre de colonnes utilisées, ainsi que le nombre de lignes, et la plage que cela représente. Voici un exemple :

Get-ExcelFileSummary -Path '.\AD-utilisateurs.xlsx'

Le résultat est parlant :

D'ailleurs, on peut aller plus loin et obtenir des informations sur tous les fichiers Excel situés dans un dossier ou une arborescence. Sur le site officiel, il y a un exemple donné (voir ci-dessous) qui permet d'obtenir un résumé de tous les fichiers Excel en se positionnant sur le dossier courant (avec récursivité).

dir . -r *.xlsx | Get-ExcelFileSummary | ft

Ainsi, on obtient des informations de différents fichiers Excel :

Note : le paramètre -Password est disponible avec Import-Excel et Export-Excel afin de gérer le mot de passe qui peut éventuellement protéger le fichier Excel.

VI. Écrire dans un fichier Excel avec PowerShell

Vous attendiez probablement cette partie : l'ajout de données dans un fichier Excel à l'aide de PowerShell. Sachez que c'est possible, et que l'on peut même modifier des données existantes. Ainsi, on va aller plus loin que simplement exporter et importer des données au format Excel, même si c'est déjà bien !

A. Ouvrir le fichier Excel avec Open-ExcelPackage

La première étape consiste à ouvrir le fichier Excel avec la commande Open-ExcelPackage afin de le convertir (en quelque sorte) en un objet PowerShell. Une fois qu'il sera dans ce nouveau format, il sera possible de lire et de le modifier.

Commençons par ouvrir le fichier Excel "AD-utilisateurs.xlsx" que nous avons créé précédemment. L'objet sera stocké dans la variable $Excel dans mon exemple, mais vous pouvez utiliser un autre nom.

$Excel = Open-ExcelPackage -Path '.\AD-utilisateurs.xlsx'

Actuellement, la variable $Excel ne contient pas le contenu de notre fichier Excel directement. Si on affiche son contenu, ce sont plutôt les propriétés du fichier Excel que l'on obtient.

B. Lire le contenu d'une cellule

Pour lire le contenu d'une cellule, il faut parcourir le fichier Excel, via les différentes propriétés de l'objet. On commencera par préciser "Workbook.Worksheets['nom de la feuille']" pour se positionner dans le bon onglet de notre classeur Excel. Ensuite, via "Cells", on peut préciser la cellule que l'on veut cibler et obtenir sa valeur facilement. Voici un exemple pour la valeur de la cellule A1 :

$Excel.Workbook.Worksheets['Domaine it-connect.local'].Cells['A1'].Value

La valeur est bien retournée, et elle pourrait être stockée dans une variable :

Pour obtenir les valeurs d'une plage de cellules, par exemple la plage A1:A8, on utilisera plutôt SelectedRange :

$Excel.Workbook.Worksheets['Domaine it-connect.local'].SelectedRange['A1:A8'].Value

On peut aussi stocker le "chemin" vers l'onglet du fichier Excel dans une variable, comme ceci :

$Onglet = $Excel.Workbook.Worksheets['Domaine it-connect.local']
$Onglet.Cells['A1'].Value

C. Éditer le contenu d'une cellule

Si l'on souhaite modifier la valeur d'une cellule, on peut s'appuyer sur ce que nous avons vu précédemment. Par exemple, on sait que la cellule A1 (qui correspond à l'en-tête de la colonne A) a pour valeur "DistinguishedName" donc on peut changer cette valeur par "DN" pour avoir un nom plus court. Il suffit d'utiliser cette commande :

$Excel.Workbook.Worksheets['Domaine it-connect.local'].Cells['A1'].Value = "DN"

Avec cette commande, le tour est joué ! Bien sûr, on aurait pu réutiliser la variable $Onglet déclarée précédemment.

D. Fermer le classeur Excel

Il est indispensable de fermer le classeur Excel au moment où l'on a terminé les modifications. En fait, cette option s'effectue via le cmdlet Close-ExcelPackage et elle permettra d'enregistrer les modifications apportées au fichier Excel, s'il y en a eu. Tout le temps que cette commande n'est pas exécutée, les modifications ne sont pas visibles dans Excel.

Il suffit d'appeler notre variable $Excel :

Close-ExcelPackage $Excel

VII. Créer un fichier Excel vierge en PowerShell

Lorsque l'on utilise Open-ExcelPackage en appelant un fichier qui n'existe pas et en ajoutant le paramètre -Create, on peut créer un fichier Excel vierge ! Voici une commande qui permet de créer le fichier "C:\partage\test.xlsx".

$ExcelNew = Open-ExcelPackage -Path "C:\partage\test.xlsx" -Create

Pour le moment, ce fichier n'est pas visible à cet emplacement, car il n'est pas enregistré. Disons qu'il est en mémoire. Nous devons lui ajouter à minima un onglet, par exemple nommé "IT-Connect" grâce au cmdlet Add-Worksheet. D'ailleurs, ce cmdlet s'applique aussi sur les fichiers existants et que l'on manipule via Open-ExcelPackage.

Add-Worksheet -ExcelPackage $ExcelNew -WorksheetName "IT-Connect"

À partir du moment où il y a au moins un onglet de déclaré, on peut fermer le fichier ce qui aura pour effet de le créer.

Close-ExcelPackage $ExcelNew

Sans quitter notre console PowerShell, on peut récupérer des informations sur la structure de notre fichier Excel via cette commande :

Get-ExcelFileSummary -Path '.\test.xlsx'

Voici un récapitulatif en image :

VIII. Créer un tableau croisé dynamique Excel

Pour terminer en beauté cet article, nous allons voir qu'il est possible d'exporter la liste des ordinateurs intégrés au domaine Active Directory pour faire un tableau croisé dynamique dans un document Excel, associé à un graphique qui montrera la répartition entre chaque version de Windows !

Tout d'abord, commençons par exporter la liste des ordinateurs du domaine, en sélectionnant le nom et le système d'exploitation, dans un fichier Excel nommé "AD-Inventaire.xlsx" au sein d'un onglet nommé "Inventaire". Ce qui donne :

Get-ADComputer -Filter * -Properties OperatingSystem | Select-Object Name,OperatingSystem | Export-Excel -Path './AD-Inventaire.xlsx' -AutoSize -WorksheetName "Inventaire" -clearsheet

Ensuite, on va ouvrir ce fichier Excel via Open-ExcelPackage comme nous l'avons vu précédemment :

$Filename = "C:\Partage\AD-Inventaire.xlsx"
$Excel = Open-ExcelPackage -Path $Filename

Puis, on va définir notre tableau croisé dynamique ainsi que le graphique grâce au cmdlet New-PivotTableDefinition. Comme ceci :

$Graph1 = New-PivotTableDefinition -PivotTableName "Graph-Inventaire" -SourceWorkSheet "Inventaire" -PivotRows "OperatingSystem" -PivotData @{OperatingSystem='Count'} -IncludePivotChart -ChartType Pie3D -ShowPercent

Quelques explications sur les différents paramètres utilisés :

• -PivotTableName : nom de l'onglet dans le classeur Excel
• -SourceWorkSheet : onglet source pour récupérer les informations, en l'occurrence "Inventaire" que l'on a créé au moment de la création du fichier Excel
• -PivotRows : champs à définir comme lignes dans le tableau croisé dynamique
• -PivotData : les données du tableau, ici on veut compter les occurrences pour la propriété "OperatingSystem" de notre tableau dont on prend la fonction "count" mais d'autres valeurs sont possibles (Average, Count, CountNums, Max, Min, Product, None, StdDev, StdDevP, Sum, Var, VarP)
• -IncludePivotChart : inclure un graphique correspondant au tableau
• -ChartType : le type de graphique, ici "Pie3D" pour avoir un camembert 3D (désolé j'aime trop le fromage pour ne pas utiliser celui-ci... non en vrai il est adapté)
• -ShowPercent : afficher les différents pourcentages sur le graphique

Il nous reste à mettre à jour le fichier Excel en intégrant le tableau croisé dynamique via le paramètre -PivotTableDefinition :

Export-Excel -ExcelPackage $Excel -PivotTableDefinition $Graph1 -Activate

Et voilà le résultat final dans un onglet nommé "Graph-Inventaire" de notre fichier Excel ! C'est top, non ? 🙂

Afin d'explorer plus en détail les possibilités de ce module et des différentes commandes, il sera indispensable de consulter l'aide du module via la console PowerShell. Pour obtenir les détails sur une commande, vous pouvez utiliser (en remplaçant le cmdlet pour lequel vous souhaitez obtenir de l'aide) les commandes suivantes :

• Aide détaillée :

Get-Help New-PivotTableDefinition -Detailed

• Exemples d'utilisation :

Get-Help New-PivotTableDefinition -Examples

Maintenant, c'est à vous de jouer et n'oubliez pas de me dire ce que vous allez faire avec Excel via PowerShell !

Florian BURNEL Co-founder of IT-Connect

Ingénieur système et réseau, cofondateur d'IT-Connect et Microsoft MVP "Cloud and Datacenter Management". Je souhaite partager mon expérience et mes découvertes au travers de mes articles. Généraliste avec une attirance particulière pour les solutions Microsoft et le scripting. Bonne lecture.

See Full Bio