PowerShell et Excel : Exploitez la puissance d’ImportExcel – techsyncer

Microsoft Excel est l’un de ces outils omniprésents dont la plupart d’entre nous ne peuvent s’échapper même si nous le voulions. De nombreux professionnels de l’IT utilisent Excel comme une petite base de données stockant des tonnes de données dans diverses routines d’automatisation. Quel est le meilleur scénario pour l’automatisation et Excel ? PowerShell et Excel!

Les feuilles de calcul Excel ont toujours été notoirement difficiles à scripter et à automatiser. Contrairement à son homologue fichier CSV, moins complet (et plus simple), les classeurs Excel ne sont pas de simples fichiers texte. Les classeurs Excel nécessitaient que PowerShell manipule des objets COM (Component Object Model) compliqués, donc vous deviez avoir Excel installé. Ce n’est plus le cas.

Heureusement, un membre perspicace de la communauté PowerShell, Doug Finke, a créé un module PowerShell appelé ImportExcel pour nous simples mortels. Le module ImportExcel abstrait toute cette complexité. Il rend possible de facilement gérer les classeurs Excel et de se mettre à la scriptation PowerShell!

Dans cet article, explorons ce que vous pouvez faire avec PowerShell et Excel en utilisant le module ImportExcel et quelques cas d’utilisation populaires.

Prérequis

Quand vous exécutez le module ImportExcel sur un système Windows, aucune dépendance distincte n’est nécessaire. Cependant, si vous travaillez sur macOS, vous devrez installer le paquet mono-libgdiplus en utilisant la commande brew install mono-libgdiplus. Tous les exemples dans cet article seront développés sur macOS, mais ils devraient fonctionner de manière interplateforme.

Si vous utilisez macOS, assurez-vous de redémarrer votre session PowerShell avant de continuer.

Installation du module ImportExcel

Commencez par télécharger et installer le module via la galerie PowerShell en exécutant la commande Install-Module ImportExcel -Scope CurrentUser. Après quelques instants, vous serez prêt à utiliser le module.

Utilisation de PowerShell et Excel pour exporter vers une feuille de calcul

Vous connaissez peut-être les cmdlets PowerShell standard tels que Export-Csv et Import-Csv. Ces cmdlets vous permettent de lire et d’exporter des objets PowerShell vers des fichiers CSV. Malheureusement, il n’y a pas de cmdlets Export-Excel et Import-Excel. Cependant, en utilisant le module ImportExcel, vous pouvez créer votre propre fonctionnalité.

Une des demandes les plus courantes d’un administrateur système est d’exporter des objets PowerShell vers une feuille de calcul Excel. En utilisant le cmdlet Export-Excel dans le module ImportExcel, vous pouvez facilement le réaliser.

Par exemple, peut-être avez-vous besoin de trouver des processus en cours d’exécution sur votre ordinateur local et de les intégrer dans un classeur Excel.

La cmdlet Export-Excel accepte n’importe quel objet exactement de la même manière que la cmdlet Export-Csv. Vous pouvez rediriger n’importe quel type d’objet vers cette cmdlet.

Pour trouver les processus en cours d’exécution sur un système avec PowerShell, utilisez la cmdlet Get-Process qui renvoie chaque processus en cours d’exécution et diverses informations sur chaque processus. Pour exporter ces informations vers Excel, utilisez la cmdlet Export-Excel en fournissant le chemin du fichier du classeur Excel qui sera créé. Vous pouvez voir un exemple de la commande et une capture d’écran du fichier Excel généré ci-dessous.

Get-Process | Export-Excel -Path './processes.xlsx'

Worksheet created with PowerShell and Excel

Félicitations ! Vous avez maintenant exporté toutes les informations comme avec Export-Csv, mais contrairement à Export-Csv, nous pouvons rendre ces données beaucoup plus attrayantes. Assurons-nous que le nom de la feuille de calcul s’appelle Processes, que les données sont dans un tableau et que les lignes sont automatiquement redimensionnées.

En utilisant le paramètre de commutation AutoSize pour ajuster automatiquement toutes les lignes, TableName pour spécifier le nom de la table qui inclura toutes les données et le paramètre WorksheetName de Processes, vous pouvez voir dans la capture d’écran ci-dessous ce qui peut être construit.

Get-Process | Export-Excel -Path './processes.xlsx' -AutoSize -TableName Processes -WorksheetName Proccesses

La cmdlet Export-Excel dispose d’une tonne de paramètres que vous pouvez utiliser pour créer des classeurs Excel de toutes sortes. Pour obtenir une liste complète de tout ce que Export-Excel peut faire, exécutez Get-Help Export-Excel.

Utilisation de PowerShell pour importer dans Excel

Vous avez donc exporté des informations vers un fichier appelé processes.xlsx dans la section précédente. Peut-être avez-vous maintenant besoin de déplacer ce fichier vers un autre ordinateur et d’importer/lire ces informations avec PowerShell et Excel. Pas de problème. Vous avez Import-Excel à votre disposition.

Dans son utilisation la plus basique, vous devez uniquement fournir le chemin vers le document/classeur Excel en utilisant le paramètre Path comme illustré ci-dessous. Vous verrez qu’il lit la première feuille de calcul, dans ce cas, la feuille de calcul Processes, et renvoie des objets PowerShell.

Import-Excel -Path './processes.xlsx'

Peut-être avez-vous plusieurs feuilles de calcul dans un classeur Excel ? Vous pouvez lire une feuille de calcul particulière en utilisant le paramètre WorksheetName.

Import-Excel -Path './processes.xlsx' -WorkSheetname SecondWorksheet

Avez-vous besoin de lire uniquement certaines colonnes de la feuille de calcul Excel ? Utilisez le paramètre HeaderName pour spécifier uniquement les paramètres que vous souhaitez lire.

Import-Excel -Path './processes.xlsx' -WorkSheetname Processes -HeaderName 'CPU','Handle'

Le cmdlet Import-Excel dispose d’autres paramètres que vous pouvez utiliser pour lire des classeurs Excel de toutes sortes. Pour obtenir toutes les informations sur ce que Import-Excel peut faire, exécutez Get-Help Import-Excel.

Utilisation de PowerShell pour obtenir (et définir) les valeurs des cellules Excel

Vous savez maintenant comment lire une feuille de calcul entière avec PowerShell et Excel, mais que faire si vous avez seulement besoin de la valeur d’une seule cellule? Techniquement, vous pouvez utiliser Import-Excel et filtrer la valeur dont vous avez besoin avec Where-Object, mais cela ne serait pas très efficace.

À la place, en utilisant le cmdlet Open-ExcelPackage, vous pouvez « convertir » un classeur Excel en un objet PowerShell qui peut ensuite être lu et manipulé. Pour trouver une valeur de cellule, ouvrez d’abord le classeur Excel pour le charger en mémoire.

$excel = Open-ExcelPackage -Path './processes.xlsx'

Le cmdlet Open-ExcelPackage est similaire à l’utilisation de New-Object -comobject excel.application si vous travaillez directement avec des objets COM.

Ensuite, choisissez la feuille de calcul à l’intérieur du classeur.

$worksheet = $excel.Workbook.Worksheets['Processes']

Ce processus est similaire à la méthode d’ouverture de classeurs avec des objets COM à l’aide de excel.workbooks.open.

Une fois que vous avez assigné la feuille de calcul à une variable, vous pouvez maintenant accéder aux lignes, colonnes et cellules individuelles. Peut-être avez-vous besoin de trouver toutes les valeurs des cellules dans la ligne A1. Il vous suffit de faire référence à la propriété Cells en fournissant un indice A1 comme indiqué ci-dessous.

$worksheet.Cells['A1'].Value

Vous pouvez également modifier la valeur des cellules dans une feuille de calcul en attribuant une valeur différente, par exemple $worksheet.Cells['A1'] = 'valeurdifférente'

Une fois en mémoire, il est important de libérer le package Excel en utilisant la cmdlet Close-ExcelPackage.

Close-ExcelPackage $excel

Conversion des feuilles de calcul en fichiers CSV avec PowerShell et Excel

Une fois que vous avez les contenus d’une feuille de calcul Excel représentés par des objets PowerShell, la « conversion » des feuilles de calcul Excel en CSV nécessite simplement l’envoi de ces objets à la cmdlet Export-Csv.

En utilisant le classeur processes.xlsx créé précédemment, lisez la première feuille de calcul pour obtenir toutes les données sous forme d’objets PowerShell, puis exportez ces objets au format CSV en utilisant la commande ci-dessous.

Import-Excel './processes.xlsx' | Export-Csv -Path './processes.csv' -NoTypeInformation

Si vous ouvrez maintenant le fichier CSV résultant, vous verrez les mêmes données à l’intérieur de la feuille de calcul Processes (dans cet exemple).

Conversion de plusieurs feuilles de calcul

Si vous avez un classeur Excel avec plusieurs feuilles de calcul, vous pouvez également créer un fichier CSV pour chaque feuille de calcul. Pour ce faire, vous pouvez trouver toutes les feuilles dans un classeur en utilisant la cmdlet Get-ExcelSheetInfo. Une fois que vous avez les noms des feuilles de calcul, vous pouvez ensuite transmettre ces noms au paramètre WorksheetName et utiliser également le nom de la feuille comme nom du fichier CSV.

Vous trouverez ci-dessous le code d’exemple nécessaire en utilisant PowerShell et Excel.

## Trouvez chaque feuille dans le classeur
$sheets = (Get-ExcelSheetInfo -Path './processes.xlsx').Name
## Lisez chaque feuille et créez un fichier CSV avec le même nom
foreach ($sheet in $sheets) {
	Import-Excel -WorksheetName $sheet -Path './processes.xlsx' | Export-Csv "./$sheet.csv" -NoTypeInformation
}

Conclusion

En utilisant PowerShell et Excel, vous pouvez importer, exporter et gérer des données dans des classeurs Excel exactement comme vous le feriez avec des CSV sans avoir à installer Excel!

Dans cet article, vous avez appris les bases de la lecture et de l’écriture de données dans un classeur Excel, mais cela ne représente qu’une petite partie des possibilités. En utilisant PowerShell et le module ImportExcel, vous pouvez créer des graphiques, des tableaux croisés dynamiques et tirer parti d’autres fonctionnalités puissantes d’Excel!