Jak dostat data z Powershellu do Excelu

Pro zápis dat, resp. jejich přenos do Excelu, za účelem např. dalších složitějších analýz a reportování, existuje v Powershellu dvě základní možnosti. Jednak můžeme data vyexportovat do textového souboru CSV a následně je v Excelu otevřít, resp. naimportovat, nebo můžeme Excel přímo otevřít a data zapisovat přímo do excelovského sešitu.

Zápis dat pomocí textového souboru CSV

Zápis dat do textového souboru pomocí cmdletu Export-Csv, který se následně otevře (resp. naimportuje) do Excelu.
Tento způsob zápisu je velice jednoduchý a taky rychlý. Hodí se především k zápisu jednoduchých seznamů a to i dlouhých, právě z důvodu rychlosti. Zápis dat o několika tisících záznamů bude při tomto způsobu trvat v řádu několika vteřin.

Get-Service | Select-Object Name, DisplayName, Status, ServiceType | Export-Csv -Path 'c:\temp\services.csv' -Delimiter ';' -Encoding UTF8

Přímý zápis dat do Excelu

Zápis dat vytvořením nového COM objektu Excel.Aplication a přímým zadáváním hodnotu do jednotlivých buněk excelovského sešitu. Tento způsob je komplikovanější pro vytvoření a navíc je poměrně pomalý, zápis několika tisíc řádku může trvat i desítky minut. Nabízí ovšem veškeré možnosti pro práci s excelovskou tabulkou, hlavně pak možnosti formátování. Takže je možné vytvářet formátované reporty přímo, bez další ruční práce přímo v Excelu.

Nejprve si zajistíme vytvoření COM objektu Excelu, tedy v podstatě spustíme Excel, zviditelníme si ho a otevřeme si v něm nový pracovní sešit, neboli workbook. V něm si pak zvolíme list, neboli worksheet, který si následně přejmenujeme.

$Excel = new-object -ComObject Excel.Application
$Excel.Visible = $true

$workbook = $Excel.workbooks.Add()
$worksheet = $workbook.Worksheets.Item(1)
$worksheet.name = "Importovaná data"

Ve zvoleném sešitu, pak můžeme začít zapisovat do jednotlivých buněk.

$Worksheet.Cells.Item(1,1) = "Name"
$Worksheet.Cells.Item(1,2) = "Display_Name"
$Worksheet.Cells.Item(1,3) = "Status"

Jednotlivé buňky pak můžeme formátovat, např. pro nadpis zvětšíme font a použijeme tlusté písmo.

For ($c=1;$c -le 3; $c++) {
$Worksheet.Cells.Item(1,$c).Font.Size = 12
$Worksheet.Cells.Item(1,$c).Font.Bold = $True
}

Abychom mohli zapsat nějaká data, musíme si nějaká data vhodná k zápisu pořídit. Načteme si tedy pro ukázku seznam služeb na počítači.

$Services = Get-Service | Select-Object Name, DisplayName, Status

Inicializujeme si proměnou pro řádkování.

$r = 2

Pro každou jednotlivou službu ve výpisu služeb provedeme zápis do buněk v excelovském listu.

Foreach ($Service In $Services) {
$Worksheet.Cells.Item($r,1) = $($Service.Name)
$Worksheet.Cells.Item($r,2) = $($Service.DisplayName)
$Worksheet.Cells.Item($r,3) = $($Service.Status)

V případě, že je služba ve stavu Stopped, změníme formátování buněk na šedý podklad.

If ($service.Status -eq "Stopped") {
For ($c=1;$c -le 3; $c++) {
$Worksheet.Cells.Item($r,$c).Interior.ColorIndex = 48
}
}
$r++
}

Nakonec excelovský sešit uložíme do souboru a uzavřeme. Poté můžeme Excel ukončit.

$workbook.SaveAs("c:\temp\excel.xlsx")
$workbook.Close()
$Excel.Quit()

Na uvedeném příkladu je vidět, že použití Excelu, umožňuje nejen připravit formátovaný report, ale zároveň umožní s excelovským sešitem interaktivně pracovat a potažmo z něj i data získávat.

Komentáře nejsou povoleny.