Skip to main content

Zapisywanie danych do tabeli przestawnej Excela

By 20 października, 20202 marca, 2022Jak to działa w FME? / Tips & Tricks FME
Zapisywanie danych do tabeli przestawnej Excela

W kolejnym ćwiczeniu z serii „Jak to zrobić w FME” pokażemy, jak zapisać dane do tabeli przestawnej w pliku Excel. Umiejętność ta może się przydać, gdy na przykład musimy wygenerować raporty zawierające tabele przestawne i chcemy, aby FME tworzyło już gotowy produkt.

Ćwiczenie stworzyliśmy na podstawie danych od Safe Software, które można pobrać tu. Dane dotyczą opadów atmosferycznych w Vancouver na przestrzeni kilku lat. Chcemy uzyskać rozbudowany raport na ten temat.

1. Wczytanie danych wejściowych

Po otworzeniu FME Workbench wczytajmy dane wejściowe, dodając Readera. Jako format wybierzmy Microsoft Excel. Wskażmy lokalizację pliku wejściowego, który można pobrać powyżej.

2. Dodanie numeracji wierszy

W komórkach chcemy stworzyć formułę, która po zapisie do Excela policzy się sama. Potrzebujemy do tego numerów wierszy. Dodajmy je więc, używając transformera Counter. Następnie połączmy go z danymi źródłowymi i wejdźmy w ustawienia funkcji. W naszym wejściowym Excelu w pierwszym wierszu znajdują się nagłówki, a od drugiego wiersza – dane. W FME nagłówki są traktowane jako nazwy atrybutów, a zatem pierwszy wiersz z danymi w FME musi mieć numer 2. Ustawmy więc parametr Count Start na 2. Dzięki temu nasze dane będą numerowane od 2.

3. Stworzenie kolumny i formuły sumującej wyniki

W tym kroku dodamy nowy atrybut, który w Excelu będzie nową kolumną i stworzymy dla niej formułę. W FME w odpowiedni sposób stworzymy kolumnę oraz formułę, a kiedy otworzymy plik Excela i odświeżymy go, formuła policzy się w nowo powstałej kolumnie.

W danych wejściowych znajdują się informacje o opadach w każdym miesiącu, a my dodamy kolumnę, która policzy sumę dla każdego roku. Dodajmy funkcję AttributeManager, podłączmy ją do poprzedniego transformera i wejdźmy w jej parametry. W funkcji AttibuteManager dodamy nową kolumnę (atrybut) oraz formułę. W funkcji AttributeManager w kolumnie Output Attribute w pustym wierszu wpiszmy nazwę atrybutu – CUMULATIVE oraz poniżej, w kolejnym pustym wierszu nazwę formuły do tej kolumny – CUMULATIVE.formula. Dopisek .formula pokaże Excelowi, że w wartości kolumny jest formuła, którą należy policzyć.

Teraz spójrzmy na dane wejściowe – aby zsumować opady z całego roku, należy w każdej kolumnie zsumować wartości kolumn od C do N. Na tej podstawie stwórzmy formułę, która policzy sumaryczny opad danego rodzaju w całym roku. Użyjemy funkcji Excela SUM(), która służy do sumowania. W środku wpiszemy zakres i w każdym wierszu (użyjemy stworzonego atrybutu _row) sumujemy wartości od C do N.

Stworzoną kolumnę CUMULATIVE i formułę CUMULATIVE.formula możemy zobaczyć na załączonym zrzucie ekranu.

Drugą rzeczą, którą zrobimy w funkcji AttributeManager, będzie usunięcie atrybutu _row. Nie będzie on nam już potrzebny. Zaznaczmy atrybut klikając na niego. Za pomocą strzałek w lewym dolnym rogu przesuńmy go poniżej tworzonych atrybutów oraz zmieńmy akcję (kolumna Action po prawej stronie) na Remove. Funkcja AttributeManager jest wykonywana od góry, dlatego najpierw stworzy się nowy atrybut i jego formuła korzystająca z _row, a dopiero potem atrybut _row zostanie usunięty.

4. Zapisanie danych

Jak już wspomnieliśmy wyżej – chcemy otrzymać raport z tabelą przestawną i wizualizacjami. Aby taki wynik uzyskać, od razu w FME użyjemy opcji zapisywania do szablonów. Najpierw należy stworzyć szablon, który odzwierciedli, jak ma wyglądać nasz plik wejściowy, a potem zapiszemy do niego dane. Szablon, którego użyjemy, jest do pobrania tutaj. Zawiera on 4 arkusze. W trzech z nich znajdują się wizualizacje i tabele przestawne, a ostatni, Raw Data, zawiera dane źródłowe, z których czerpią pierwsze trzy. W pliku są już dane, ale zamienimy je na nasze.

Kluczem do takiego zapisu są przestrzenie nazw. Przestrzeń nazw to nazwany zakres komórek, zawierający pewne dane. Jeśli zaznaczymy wszystkie dane w arkuszu Raw Data, zobaczymy, że znajdują się właśnie w przestrzeni nazw. W lewym górnym rogu możemy podejrzeć, jak się ona nazywa.

Naszym zadaniem jest zapis danych do szablonu i do przestrzeni nazw WeatherTable, która jest widoczna na zrzucie ekranu. Dzięki temu podmienimy dane znajdujące się w szablonie na nasze dane wejściowe i skorzystamy z powstałych już raportów i wizualizacji.

Zacznijmy od dodania zapisu danych – Writera i wybierzmy format Microsoft Excel. Wskażmy też lokalizację pliku wyjściowego. Definicje modelu wyjściowego (Sheet Definition) ustawmy na automatyczną i wejdźmy w parametry Writera. Musimy wskazać, że chcemy, aby przy zapisie nowopowstałego pliku korzystać z szablonu. Ustawmy więc parametr Overwriting Existing File na Yes oraz wskażmy plik z szablonem (Template File). Następnie zaakceptujmy parametry.

Po zaakceptowaniu dodania Readera pojawi się okienko z definicją pliku. Tutaj musimy wskazać, że chcemy wgrywać do przestrzeni nazw znajdującej się w konkretnym arkuszu w szablonie. Uzupełnijmy parametr Sheet Name jako nazwa arkusza/nazwa przestrzeni nazw, czyli RawData/WeatherTable. Musimy jeszcze pamiętać o ustawieniu parametru Truncate Existing Sheet/Named Range na Yes, ponieważ chcemy zamienić dane w przestrzeni nazw ze starych, z szablonu, na nowe, które pochodzą z wejścia danych.

Po uzupełnieniu dwóch parametrów możemy zaakceptować i podłączyć nowopowstały Writer do danych. Po podłączeniu skrypt powinien wyglądać następująco:

W końcowym raporcie powinniśmy otrzymać takie wizualizacje:

Chcesz dowiedzieć się więcej na temat analizy danych przestrzennych za pomocą FME?

Napisz do nas