Spis treści:

Kategoria:PowerShell


Pobieranie tabeli z Excela w PowerShell

Najczęściej używane skrypty PowerShell w pracy programisty - ikona konsoli

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

, 2017-05-04

Komentarze:

mb (2020-05-15 23:20:04)
bardo ciekawe , można dzięki takim wpisom dostrzec wędkę..
Dodaj komentarz
Wyślij
Ostatnie komentarze
Świetny artyk€ł !
Ooo chyba użyli varchar-a ;)
Morzna by bylo trohe jasiniej?
To samo pytanie co wyżej. Mam za zadanie dodać kolumnę do istniejącej tabeli łącząc obie inne kolumny ze sobą, ale nie mam pojęcia jak za to się zabrać
działa :) tylko była literówka :)
Podziękował. Trochę późno, po 8 latach, ale dzięki za testy (rozumiem że dla SQL2012 robione). Tak się właśnie zastanawiałem ile złego czynię stosując czasem __(max).