PowerShell en Excel: Benut de kracht van ImportExcel – techsyncer

Microsoft Excel is een van die alomtegenwoordige tools waar de meesten van ons niet aan kunnen ontsnappen, zelfs als we het zouden proberen. Veel IT-professionals gebruiken Excel als een kleine database om tonnen gegevens op te slaan in verschillende automatiseringsroutines. Wat is het beste scenario voor automatisering met Excel? PowerShell en Excel!

Excel-spreadsheets zijn altijd berucht moeilijk geweest om te scripten en automatiseren. In tegenstelling tot zijn minder uitgebreide (en eenvoudigere) CSV-tegenhanger zijn Excel-werkboeken niet zomaar eenvoudige tekstbestanden. Excel-werkboeken vereisten PowerShell om complexe Component Object Model (COM) objecten te manipuleren, en daarom moest Excel geïnstalleerd zijn. Niet meer.

Gelukkig heeft een oplettend lid van de PowerShell-gemeenschap, Doug Finke, een PowerShell-module gemaakt genaamd ImportExcel voor ons gewone stervelingen. De ImportExcel-module abstraheert al die complexiteit. Het maakt het mogelijk om eenvoudig Excel-werkboeken te beheren en over te schakelen naar PowerShell scripting!

In dit artikel gaan we verkennen wat je kunt doen met PowerShell en Excel met behulp van de ImportExcel-module en een paar populaire use-cases.

Vereisten

Wanneer je de ImportExcel-module uitvoert op een Windows-systeem, zijn er geen aparte afhankelijkheden nodig. Als je echter op macOS werkt, moet je het mono-libgdiplus-pakket installeren met brew install mono-libgdiplus. Alle voorbeelden in dit artikel worden gebouwd op macOS, maar alle voorbeelden zouden op alle platforms moeten werken.

Als je macOS gebruikt, zorg er dan voor dat je je PowerShell-sessie opnieuw start voordat je verder gaat.

Installatie van de ImportExcel-module

Begin met het downloaden en installeren van de module via de PowerShell Gallery door Install-Module ImportExcel -Scope CurrentUser uit te voeren. Na enkele momenten ben je klaar om te gaan.

Gebruik PowerShell en Excel om naar een werkblad te exporteren

Je bent misschien bekend met de standaard PowerShell-cmdlets Export-Csv en Import-Csv. Deze cmdlets stellen je in staat om PowerShell-objecten te lezen en exporteren naar CSV-bestanden. Helaas zijn er geen Export-Excel en Import-Excel cmdlets. Maar met de ImportExcel-module kun je eenvoudig je eigen functionaliteit bouwen.

Een van de meest voorkomende verzoeken van een systeembeheerder is het exporteren van PowerShell-objecten naar een Excel-werkblad. Met de Export-Excel cmdlet in de ImportExcel-module kun je dit eenvoudig realiseren.

Bijvoorbeeld, misschien moet je wat processen vinden die op je lokale computer draaien en deze in een Excel-werkboek krijgen.

De cmdlet Export-Excel accepteert elk object precies zoals Export-Csv dat doet. Je kunt elk type object naar deze cmdlet doorpompen.

Om processen op een systeem met PowerShell te vinden, gebruik je de cmdlet Get-Process, die elk draaiend proces en diverse informatie over elk proces retourneert. Om die informatie naar Excel te exporteren, gebruik je de cmdlet Export-Excel en geef je het bestandspad op naar het Excel-werkboek dat zal worden aangemaakt. Je ziet hieronder een voorbeeld van de opdracht en een schermafbeelding van het gegenereerde Excel-bestand.

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

Worksheet created with PowerShell and Excel

Gefeliciteerd! Je hebt nu alle informatie geëxporteerd, net als bij Export-Csv, maar in tegenstelling tot Export-Csv kunnen we deze gegevens veel mooier maken. Laten we ervoor zorgen dat de werkbladnaam Processes is, de gegevens in een tabel staan en de rijen automatisch zijn aangepast.

Door de AutoSize schakelparameter te gebruiken om alle rijen automatisch te dimensioneren, TableName om de naam van de tabel op te geven die alle gegevens zal bevatten en de parameter naam WorksheetName van Processes, kunt u in de onderstaande schermafbeelding zien wat er gebouwd kan worden.

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

De Export-Excel cmdlet heeft een heleboel parameters die u kunt gebruiken om Excel-werkboeken van allerlei soorten te maken. Voor een volledige uitleg over alles wat Export-Excel kan doen, voer Get-Help Export-Excel uit.

PowerShell gebruiken om naar Excel te importeren

Dus je hebt wat informatie geëxporteerd naar een bestand genaamd processen.xlsx in het vorige gedeelte. Misschien moet je nu dit bestand naar een andere computer verplaatsen en deze informatie importeren/lezen met PowerShell en Excel. Geen probleem. Je hebt Import-Excel tot je beschikking.

In zijn meest elementaire gebruik, hoef je alleen maar het pad naar het Excel-document/werkboek op te geven met behulp van de Path parameter zoals hieronder getoond. Je ziet dat het het eerste werkblad leest, in dit geval het Processes werkblad, en PowerShell objecten retourneert.

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

Misschien heb je meerdere werkbladen in een Excel-werkboek? U kunt een bepaald werkblad lezen met behulp van de WorksheetName parameter.

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

Moet u alleen bepaalde kolommen uit het Excel-werkblad lezen? Gebruik de HeaderName parameter om alleen die parameters op te geven die u wilt lezen.

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

De Import-Excel cmdlet heeft andere parameters die u kunt gebruiken om Excel-werkboeken van allerlei soorten te lezen. Voor een volledig overzicht van alles wat Import-Excel kan doen, voert u Get-Help Import-Excel uit.

Gebruik van PowerShell om Excel-celwaarden te krijgen (en in te stellen)

U weet nu hoe u met PowerShell en Excel een volledig werkblad kunt lezen, maar wat als u slechts één celwaarde nodig heeft? U zou technisch gezien kunnen de Import-Excel gebruiken en de waarde die u nodig heeft filteren met Where-Object, maar dat zou niet erg efficiënt zijn.

In plaats daarvan kunt u met de Open-ExcelPackage cmdlet een Excel-werkboek “converteren” naar een PowerShell-object dat vervolgens kan worden gelezen en gemanipuleerd. Om een celwaarde te vinden, opent u eerst het Excel-werkboek om het in het geheugen te laden.

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

De Open-ExcelPackage is vergelijkbaar met het gebruiken van New-Object -comobject excel.application als u rechtstreeks met COM-objecten werkt.

Selecteer vervolgens het werkblad in het werkboek.

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

Dit proces lijkt op de manier waarop werkboeken worden geopend met excel.workbooks.open met COM-objecten.

Zodra u het werkblad aan een variabele hebt toegewezen, kunt u nu naar individuele rijen, kolommen en cellen gaan. Misschien moet u alle celwaarden in de rij A1 vinden. U hoeft alleen maar te verwijzen naar de Cells-eigenschap met een index van A1 zoals hieronder getoond.

$worksheet.Cells['A1'].Value

Je kunt ook de waarde van cellen in een werkblad wijzigen door een andere waarde toe te wijzen, bijv. $worksheet.Cells['A1'] = 'verschillendewaarde'

Eenmaal in het geheugen is het belangrijk om het Excel-pakket vrij te geven met behulp van de Close-ExcelPackage cmdlet.

Close-ExcelPackage $excel

Werkmappen converteren naar CSV-bestanden met PowerShell en Excel

Als je de inhoud van een Excel-werkblad vertegenwoordigd hebt via PowerShell-objecten, vereist het “converteren” van Excel-werkbladen naar CSV eenvoudigweg het verzenden van die objecten naar de Export-Csv cmdlet.

Gebruikmakend van het werkboek processes.xlsx dat eerder is aangemaakt, lees het eerste werkblad in dat alle gegevens naar PowerShell-objecten haalt, en exporteer vervolgens die objecten naar CSV met behulp van onderstaand commando.

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

Als je nu het resulterende CSV-bestand opent, zie je dezelfde gegevens in het Processes werkblad (in dit voorbeeld).

Meerdere Werkbladen Converteren

Als je een Excel-werkboek hebt met meerdere werkbladen, kun je ook een CSV-bestand maken voor elk werkblad. Om dit te doen, kun je alle bladen in een werkboek vinden met behulp van de Get-ExcelSheetInfo cmdlet. Nadat je de namen van de werkbladen hebt, kun je die namen doorgeven aan de WorksheetName-parameter en ook de bladnaam gebruiken als de naam van het CSV-bestand.

Hieronder vind je het benodigde voorbeeldcode met PowerShell en Excel.

## zoek elk blad in het werkboek
$sheets = (Get-ExcelSheetInfo -Path './processes.xlsx').Name
## lees elk blad en maak een CSV-bestand met dezelfde naam
foreach ($sheet in $sheets) {
	Import-Excel -WorksheetName $sheet -Path './processes.xlsx' | Export-Csv "./$sheet.csv" -NoTypeInformation
}

Conclusie

Met PowerShell en Excel kunt u gegevens importeren, exporteren en beheren in Excel-werkboeken precies zoals u dat zou doen met CSV-bestanden zonder Excel te hoeven installeren!

In dit artikel heb je de basisprincipes geleerd van het lezen en schrijven van gegevens in een Excel-werkboek, maar dit is slechts het topje van de ijsberg. Met PowerShell en de ImportExcel-module kunt u grafieken, draaitabellen maken en andere krachtige functies van Excel benutten!