PowerShell und Excel: Nutzen Sie die Power von ImportExcel

Microsoft Excel ist eines dieser allgegenwärtigen Werkzeuge, denen die meisten von uns nicht entkommen können, selbst wenn wir es versuchen würden. Viele IT-Profis verwenden Excel als eine Art Datenbank, in der sie Unmengen von Daten in verschiedenen Automatisierungsroutinen speichern. Was ist das beste Szenario für Automatisierung mit Excel? PowerShell und Excel!

Excel-Tabellenkalkulationen waren schon immer berüchtigt schwer zu skripten und zu automatisieren. Im Gegensatz zu ihrem weniger umfangreichen (und einfacheren) Gegenstück, der CSV-Datei, sind Excel-Arbeitsmappen nicht nur einfache Textdateien. Excel-Arbeitsmappen erforderten PowerShell, um komplexe Component Object Model (COM)-Objekte zu manipulieren, daher musste Excel installiert sein. Aber nicht mehr.

Zum Glück hat ein aufmerksames Mitglied der PowerShell-Community, Doug Finke, ein PowerShell-Modul namens ImportExcel für uns Sterbliche erstellt. Das ImportExcel-Modul abstrahiert all diese Komplexität. Es ermöglicht es Ihnen, Excel-Arbeitsmappen problemlos zu verwalten und sich auf das PowerShell-Skripting zu konzentrieren!

In diesem Artikel werden wir erkunden, was Sie mit PowerShell und Excel mithilfe des ImportExcel-Moduls und einigen beliebten Anwendungsfällen tun können.

Voraussetzungen

Wenn Sie das ImportExcel-Modul auf einem Windows-System ausführen, sind keine separaten Abhängigkeiten erforderlich. Wenn Sie jedoch auf macOS arbeiten, müssen Sie das mono-libgdiplus-Paket mit brew install mono-libgdiplus installieren. Alle Beispiele in diesem Artikel werden mit macOS erstellt, aber alle Beispiele sollten plattformübergreifend funktionieren.

Wenn Sie macOS verwenden, stellen Sie sicher, dass Sie Ihre PowerShell-Sitzung vor dem Fortfahren neu starten.

Installation des ImportExcel-Moduls

Beginnen Sie damit, das Modul über die PowerShell Gallery herunterzuladen und zu installieren, indem Sie Install-Module ImportExcel -Scope CurrentUser ausführen. Nach einigen Momenten sind Sie bereit loszulegen.

Verwenden von PowerShell und Excel zum Exportieren in ein Arbeitsblatt

Sie sind möglicherweise mit den Standard-PowerShell-Cmdlets Export-Csv und Import-Csv vertraut. Mit diesen Cmdlets können Sie PowerShell-Objekte in CSV-Dateien lesen und exportieren. Leider gibt es keine Export-Excel und Import-Excel Cmdlets. Mit dem ImportExcel-Modul können Sie jedoch Ihre eigene Funktionalität erstellen.

Verwandt: Verwalten von CSV-Dateien in PowerShell mit Import-Csv

Eine der häufigsten Anforderungen eines Systemadministrators besteht darin, PowerShell-Objekte in ein Excel-Arbeitsblatt zu exportieren. Mit dem Export-Excel-Cmdlet im ImportExcel-Modul können Sie dies problemlos erreichen.

Zum Beispiel müssen Sie möglicherweise einige auf Ihrem lokalen Computer ausgeführte Prozesse finden und sie in einer Excel-Arbeitsmappe speichern.

Der Befehl Export-Excel akzeptiert jedes Objekt genauso wie der Befehl Export-Csv. Sie können jedes beliebige Objekt an diesen Befehl übergeben.

Um mit PowerShell Prozesse auf einem System zu finden, verwenden Sie den Befehl Get-Process, der jeden ausgeführten Prozess und verschiedene Informationen zu jedem Prozess zurückgibt. Um diese Informationen in Excel zu exportieren, verwenden Sie den Befehl Export-Excel und geben den Dateipfad zur Excel-Arbeitsmappe an, die erstellt werden soll. Im Folgenden sehen Sie ein Beispiel für den Befehl und einen Screenshot der generierten Excel-Datei.

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

Herzlichen Glückwunsch! Sie haben jetzt alle Informationen exportiert, ähnlich wie bei Export-Csv. Im Gegensatz zu Export-Csv können wir diese Daten jedoch viel ansprechender gestalten. Lassen Sie uns sicherstellen, dass der Arbeitsblattname Prozesse lautet, die Daten in einer Tabelle angezeigt werden und die Zeilen automatisch angepasst werden.

Durch Verwendung des AutoSize-Schalterparameters zum automatischen Anpassen aller Zeilen, TableName zur Angabe des Namens der Tabelle, die alle Daten enthält, und des WorksheetName-Parameter des Namens Processes können Sie auf dem untenstehenden Screenshot sehen, was erstellt werden kann.

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

Der Export-Excel-Befehl verfügt über eine Vielzahl von Parametern, mit denen Sie Excel-Arbeitsmappen aller Art erstellen können. Um einen vollständigen Überblick über alles zu erhalten, was der Export-Excel-Befehl kann, führen Sie Get-Help Export-Excel aus.

Verwenden von PowerShell zum Importieren in Excel

Sie haben also Informationen in eine Datei mit dem Namen processes.xlsx im vorherigen Abschnitt exportiert. Vielleicht müssen Sie diese Datei nun auf einen anderen Computer verschieben und diese Informationen mit PowerShell und Excel importieren/lesen. Kein Problem. Sie haben Import-Excel zur Verfügung.

Bei der grundlegenden Verwendung müssen Sie nur den Pfad zum Excel-Dokument/Arbeitsmappe mit dem Path-Parameter angeben, wie unten gezeigt. Sie werden feststellen, dass es das erste Arbeitsblatt liest, in diesem Fall das Arbeitsblatt Processes, und PowerShell-Objekte zurückgibt.

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

Vielleicht haben Sie mehrere Arbeitsblätter in einer Excel-Arbeitsmappe? Sie können ein bestimmtes Arbeitsblatt mit dem WorksheetName-Parameter lesen.

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

Müssen Sie nur bestimmte Spalten aus dem Excel-Arbeitsblatt lesen? Verwenden Sie den HeaderName-Parameter, um nur die gewünschten Parameter zu lesen.

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

Der Import-Excel-Befehl verfügt über weitere Parameter, mit denen Sie Excel-Arbeitsmappen aller Art lesen können. Um eine vollständige Übersicht über alles zu erhalten, was Import-Excel kann, führen Sie den Befehl Get-Help Import-Excel aus.

Verwenden von PowerShell zum Abrufen (und Festlegen) von Excel-Zellenwerten

Sie wissen nun, wie Sie mit PowerShell und Excel ein gesamtes Arbeitsblatt lesen können, aber was ist, wenn Sie nur einen einzelnen Zellenwert benötigen? Technisch gesehen könnten Sie Import-Excel verwenden und den benötigten Wert mit Where-Object filtern, aber das wäre nicht besonders effizient.

Stattdessen können Sie mit dem Open-ExcelPackage-Befehl eine Excel-Arbeitsmappe in ein PowerShell-Objekt „umwandeln“, das anschließend gelesen und manipuliert werden kann. Um einen Zellenwert zu finden, öffnen Sie zunächst die Excel-Arbeitsmappe, um sie in den Arbeitsspeicher zu laden.

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

Der Open-ExcelPackage-Befehl entspricht in etwa der Verwendung von New-Object -comobject excel.application, wenn Sie direkt mit COM-Objekten arbeiten.

Wählen Sie als nächstes das Arbeitsblatt in der Arbeitsmappe aus.

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

Dieser Vorgang ähnelt der COM-Objekt-Methode zum Öffnen von Arbeitsmappen mit excel.workbooks.open.

Sobald Sie das Arbeitsblatt einer Variablen zugewiesen haben, können Sie nun einzelne Zeilen, Spalten und Zellen durchsuchen. Vielleicht müssen Sie alle Zellenwerte in der Zeile A1 finden. Sie müssen einfach auf die Cells-Eigenschaft verweisen und den Index A1 angeben, wie unten gezeigt.

$worksheet.Cells['A1'].Value

Sie können den Wert von Zellen in einem Arbeitsblatt auch ändern, indem Sie ihnen einen anderen Wert zuweisen, z. B. $worksheet.Cells['A1'] = 'andererwert'

Nachdem die Excel-Paket im Speicher ist, ist es wichtig, das Excel-Paket mit dem Close-ExcelPackage-Befehl freizugeben.

Close-ExcelPackage $excel

Arbeitsblätter in CSV-Dateien mit PowerShell und Excel konvertieren

Wenn Sie den Inhalt eines Excel-Arbeitsblatts über PowerShell-Objekte darstellen, erfordert das „Konvertieren“ von Excel-Arbeitsblättern in CSV-Dateien einfach das Senden dieser Objekte an den Export-Csv-Befehl.

Verwandt:Export-Csv: Objekte in CSV-Dateien konvertieren

Verwenden Sie die zuvor erstellte Arbeitsmappe processes.xlsx, um das erste Arbeitsblatt zu lesen, das alle Daten in PowerShell-Objekte übernimmt, und exportieren Sie diese Objekte dann mit folgendem Befehl in eine CSV-Datei.

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

Wenn Sie nun die resultierende CSV-Datei öffnen, sehen Sie dieselben Daten im Arbeitsblatt Processes (in diesem Beispiel).

Mehrere Arbeitsblätter konvertieren

Wenn Sie eine Excel-Arbeitsmappe mit mehreren Arbeitsblättern haben, können Sie auch für jedes Arbeitsblatt eine CSV-Datei erstellen. Dazu können Sie mit dem Get-ExcelSheetInfo-Befehl alle Arbeitsblätter in einer Arbeitsmappe finden. Sobald Sie die Arbeitsblattnamen haben, können Sie diese Namen an den WorksheetName-Parameter übergeben und den Arbeitsblattnamen auch als Namen der CSV-Datei verwenden.

Unten finden Sie den Beispielcode, den Sie mit PowerShell und Excel benötigen.

## jede Tabelle in der Arbeitsmappe finden
$sheets = (Get-ExcelSheetInfo -Path './processes.xlsx').Name
## jede Tabelle lesen und eine CSV-Datei mit demselben Namen erstellen
foreach ($sheet in $sheets) {
	Import-Excel -WorksheetName $sheet -Path './processes.xlsx' | Export-Csv "./$sheet.csv" -NoTypeInformation
}

Conclusion

Mit PowerShell und Excel können Sie Daten in Excel-Arbeitsmappen importieren, exportieren und verwalten, genauso wie Sie es mit CSV-Dateien tun würden, ohne Excel installieren zu müssen!

In diesem Artikel haben Sie die Grundlagen des Lesens und Schreibens von Daten in einer Excel-Arbeitsmappe gelernt, aber das ist nur die Spitze des Eisbergs. Mit PowerShell und dem ImportExcel-Modul können Sie Diagramme, Pivot-Tabellen erstellen und andere leistungsstarke Funktionen von Excel nutzen!