Pobieranie tabeli z Excela w PowerShell

Tabele w Excelu

Powershell posiada mnóstwo komponentów do importu i eksportu z różnych źródeł danych. To przecież nie tylko zwykłe tekstowe pliki, ale także pliki html (ConvertTo-Html), pliki JSON (ConvertTo-Json, ConvertFrom-Json), pliki CSV (ConvertFrom-Csv, ConvertTo-Csv, Export-Csv, Import-Csv). Wymagania biznesowe często prowadzą jednak w innym kierunku - w kierunku plików bardziej skomplikowanych w strukturze i mniej nadających się do wymiany danych. Analiza frekwencji potrzeb doprowadziła mnie do wniosku, że bardzio potrzebny okazuje się komponent do importu z pliku Excela - *.xlsx. Problem jednak w tym, że w pliku xlsx może być wiele różnych arkuszy, na każdym z arkuszy kilka bloków z danymi. Trudno wymyślić komponent, który pozbiera wszystkie te dane i wyświetli w jakiś łatwy do dalszej analizy i przetwarzania sposób.

Dane biznesowe prezentowane są zwykle w postaci tabelarycznej, a naturalną ich reprezentacją w Excelu są - a jakże - tabele.

Komponent do importu pliku xlsx

Komponent będzie się nazywał, podobnie jak ma to miejsce w przypadku pliku CSV, Import-ExcelTable (poprzez analogię do wymienionego juz Import-Csv). O ile plik CSV zawiera tylko jedną tabelę (tj. jedną porcję danych), o tyle plik *.xlsx może tych niezależnych porcji mieć więcej. Jak już wspomniałem, mogą to być dane na dowolnym arkuszu i w dowolnej tabeli na tym arkuszu. W najprostszej wersji komponentu wszystkie te parametry będą wymagane. W wersji uproszczonej można założyć, że o ile nie podamy konkretnego arkusza, wybrany zostanie pierwszy z brzegu. Podobnie tabele - jeżeli jakieś są, wybrana zostanie pierwsza w kolejności.

Implementacja komponentu

Komponent będzie korzystał z obiektów COM instalowanych razem z pakietem Office - Excel.Application. Komponent musi być zainstalowany, a jego wersja zostanie odpowiednio wybrana - w zależności od wskazań w rejestrze systemowym.

Popatrzmy na przykładową implementację funkcji, która jest w stanie odczytać dane z tabeli Excela:

function Import-ExcelTable
{
    Param
    (
        [Parameter(Mandatory=$true)]
        [string]$Path,
        [Parameter(Mandatory=$true)]
        [string]$Sheet,
        [Parameter(Mandatory=$true)]
        [string]$Table
    )
    try {
        $excel = New-Object -ComObject Excel.Application
        $file = (ls $Path).FullName
        Write-Verbose "Opening file $file..."
        $workbook = $excel.Workbooks.Open($file)
        $xlSheet = $excel.Sheets.Item($Sheet)
        $xlTable = $xlSheet.ListObjects.Item($Table)
        $captions = $xlTable.HeaderRowRange.Value2

        $data = $xlTable.DataBodyRange.Value2
        for ($i=1; $i -le $data.GetLength(0); $i++) {
            $obj = @{}
            1..$captions.Count | % { $obj[[string]$captions[1,$_]] = [string]$data[$i,$_] }
            [PSCustomObject]$obj
        }
    } finally {
        $workbook.Close()
        $excel.Quit()
        [Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null
    }
}

To wszystko, aby w prosty sposób wyświetlić dowolną tabelę.

Przykład użycia

Komponent ma zaledwie trzy parametry, wszystkie wymagane, dlatego nie ma się co spodziewać kilku wersji. Popatrzmy na najprostszą:

Import-ExcelTable Test.xlsx Arkusz1 Tabela1

Wynik zostanie wyświetlony w postaci obiektowej, np. tak:

Liczba mandatów Identyfikator Nazwisko Imię  
--------------- ------------- -------- ----  
3               C12523        Byk      Janusz
2               D55242        Lew      Michał
5               A34552        Anakonda Roman

Samą instrukcję można oczywiście zapisać w postaci pełniejszej, wprost wskazujac wszystkie parametry:

$table = Import-ExcelTable -Path 'Plik.xlsx' -Sheet 'Arkusz1' -Table 'Tabela1'

Cała moc PowerShella polega jednak na łączeniu poszczególnych komponentów i stosowaniu operacji na konkretnych obiektach. Komponent do Excela może być tylko częścią jakiegoś większego skryptu:

Import-ExcelTable Test.xlsx Arkusz1 Tabela1 `
  | Select Identyfikator,'Liczba mandatów' `
  | Sort 'Liczba mandatów' -Descending

Wynikiem powyższego skryptu będzie taka oto tabelka:

Identyfikator Liczba mandatów
------------- ---------------
A34552        5              
C12523        3              
D55242        2

Ten wynik może być dalej przetwarzany, zapisywany w plikach, konwertowany, łączony z innymi zbiorami. To już jest jednak temat zdecydowanie wykraczający poza niniejszy artykuł.

Kategoria:PowerShell