Jak stworzyć raport z pliku XLSX w Oracle APEX?

4 sierpnia 2015, Michał Wyszyński

W wielu firmach pliki XLSX (format plików tworzonych przez MS Excel 2003+) służą jako nośnik danych raportowych. Czasami nawet zastępują proste tabele w bazach danych. Jednak prawie zawsze przychodzi taki moment kiedy trzeba te dane załadować do nowego systemu lub udostępnić je w wygodny sposób za pomocą aplikacji webowej. W przypadku aplikacji opartych o Oracle APEX takie wymaganie pojawia się niezwykle często – wszak jedną z głównych idei APEXa jest migrowanie procesów biznesowych opartych o Excela do sieci.

Najczęstszym rozwiązaniem jest zapisanie danych z pliku XLSX w formacie CSV, a następnie załadowanie danych z pliku do tabeli przy pomocy procedury PL/SQL. Jest to sposób prosty i sprawdzony, ale ze względu na małą elastyczność nadaje się jedynie do ładowania plików o stałym i przewidywalnym formacie danych (liczbie kolumn i ich typie danych). W przypadku dodania nowej kolumny do pliku lub usunięcia istniejącej, jesteśmy zmuszeni zmodyfikować proces ładujący i/lub tabelę docelową. Jeśli chcemy dać użytkownikowi możliwość udostępniania danych w postaci raportu, to musimy jeszcze pamiętać o aktualizacji aplikacji APEX.

Nie zmienia to faktu, że nadal istnieje potrzeba importowania danych bezpośrednio z pliku XLSX i nasi klienci nie przestaną o to pytać. A skoro jest potrzeba, to musi znaleźć się rozwiązanie, prawda?

SELECT * FROM employees.xlsx ?

Idealną sytuacją byłaby możliwość wybrania, które dane z pliku XLSX chcemy załadować, a których nie chcemy. Albo żebyśmy mogli tylko podejrzeć co znajduje się w pliku załączonym przez użytkownika bez potrzeby parsowania go i przerzucania odczytanych danych do tabeli tymczasowej. Tutaj z pomocą przychodzi nam pakiet AS_READ_XLSX. Znalazłem go na Amis Technology Blog na którym to znajdują się przykłady jego podstawowego wykorzystania.

Pakiet AS_READ_XLSX umożliwia nam wyciąganie danych bezpośrednio z wrzuconego do bazy danych BLOBa. Możliwe jest też odpytywanie plików znajdujących się na filesystemie. Wykorzystuje przy tym standardową oracle’ową bibliotekę do obsługi XML. Testowałem – działa również na bazie w wersji XE.

Przykładowe zapytanie może wyglądać tak:

lub

 

Rezultatem takiego zapytania będzie spłaszczona drzewiasta struktura pliku XML:

xlsx_scr1

Jak widać znajdują się tutaj wszystkie potrzebne informacje: nr i nazwa zakładki, nr wiersza, nr kolumny, adres komórki, typ danych oraz same dane odpowiedniego typu bazodanowego. Coś już mamy, ale nie jest to jeszcze raport jakiego byśmy oczekiwali.

Raportowanie przez piwotowanie

Przy wykorzystaniu odrobiny SQL-owej ekwilibrystyki możemy sprawić by powyższe dane stały się czytelne. W tym celu wykorzystamy operator PIVOT.

Jeśli wszystko napisaliśmy dobrze to naszym oczom powinien ukazać się następujący widok:

xlsx_scr2

Mając raport w takiej postaci już tylko krok dzieli nas od załadowania danych do tabeli docelowej lub stworzenia raportu w APEX. I wszystko to bez wcześniejszego przetwarzania BLOBa 🙂

Raport w APEX

Na potrzeby przykładu załóżmy, że chcemy zrobić raport oparty o pierwszą zakładkę załadowanego pliku XLSX. Dla uproszczenia przyjmijmy, że pierwszy wiersz zawiera nagłówki kolumn, a sam arkusz jest zwyczajną tabelą.

  1. Tworzymy prosty formularz służący do filtrowania naszego raportu. Będzie on zawierał jedną kontrolkę – listę wartości z listą załadowanych plików.
    apex_report_1
    apex_report_2
  2. Ponieważ chcielibyśmy uniezależnić się od ilości i nazw kolumn w pliku XLSX, to musimy posiłkować się kodem PL/SQL. Pomoże on nam dynamicznie zbudować kwerendę, a konkretnie listę kolumn w klauzuli PIVOT.

     
  3. Wykorzystując powyższy kod, tworzymy raport. Musi być to raport klasyczny z dwóch powodów: po pierwsze raport interaktywny nie umożliwia wykorzystania PL/SQL do wygenerowania kwerendy, a po drugie musimy mieć możliwość wybrania opcji „Use Generic Column Names (parse query at runtime only)” ponieważ przed uruchomieniem zapytania nie będziemy znali ani liczby ani nazw kolumn.
    apex_report_3
    apex_report_4
  4. Jeszcze tylko dodamy guzik do zrobienia submit’a i możemy przetestować raport.
    apex_report_5
  5. Gotowy raport powinien wyglądać mniej więcej tak:
    apex_report_6

 Ograniczenia

Podstawowe ograniczenia tej metody są związane z jej główną zaletą, czyli czytaniem danych bezpośrednio z pliku XLSX. Ponieważ operujemy na załadowanym pliku, a nie na danych zapisanych w tabelach, to nie możemy korzystać z wielu dobrodziejstw bazy danych takich jak indeksy. Utrudnione są też operacje na typach danych, ponieważ wykorzystanie funkcji PIVOT wymusza na nas ujednolicenie typu zwracanych danych – w powyższym wypadku jest to CHAR. Dodatkowo wszystkie operacje są wykonywane w pamięci, więc może się zdarzyć, że próbując otworzyć zbyt duży plik wyczerpiemy całą pulę dostępnej pamięci. Dodatkowo mechanizm ten będzie słabo się skalował – im większy plik będziemy próbowali odczytać tym dłużej będzie trzeba czekać na wyniki. Nie bez znaczenia będzie też struktura danych wewnątrz pliku. Jeśli dane w arkuszu będą porozrzucane zamiast znajdować się w jednej, zwartej tabeli, to prezentacja ich na raporcie będzie nie lada wyzwaniem

Podsumowanie

Metoda ta sprawdzi się przy udostępnianiu niewielkich plików w formie zwięzłych raportów tabelarycznych, których liczba wierszy nie będzie przekraczać kilku setek. Kolejnym przypadkiem użycia może być podgląd danych w pliku przed ich ostatecznym załadowaniem do tabeli w bazie danych. Sam pakiet może zostać wykorzystany do wczytywania danych zawartych w pliku XLSX do tabel w bazie danych. Jednak w przypadku większych plików należałoby rozważyć wcześniejsze załadowanie danych do tabeli pośredniej i dopiero na niej wykonać przekształcenie (PIVOT) w celu dalszych operacji na wczytanych danych.

Załączam przykładową aplikację APEX. Jej funkcjonalność jest bardzo ograniczona, ale pozwala podejrzeć jak można zrealizować proces załączania plików, podgląd „surowych” danych znajdujących się w pliku, a także przykładowy raport z dynamicznym piwotem z możliwością wyboru arkusza, który chcemy oglądać. Do aplikacji dołączony jest skrypt instalujący niezbędne obiekty bazodanowe łącznie z pakietem AS_READ_XLSX. Sam pakiet można pobrać tutaj. Dodałem do niego jedną zmianę, którą wymusił na mnie APEX. Sprawdzając poprawność kwerendy podanej jako źródło raportu, APEX wstawia NULL do wszystkich zmiennych (bind variables). Skutkowało to zawieszaniem się sesji bazodanowej za każdym razem gdy chciałem utworzyć raport.

Na cale demonstracji założyłem bardzo podstawową strukturę danych znajdujących się w pliku: tabela z danymi musi zaczynać się od komórki A1, a pierwszy wiersz musi zawierać nazwy kolumn. Tutaj można pobrać plik, z którego korzystałem w powyższych przykładach.

Tagi: , , , , , , , , , , , , ,

Zapraszamy do kontaktu!

Pretius jest firmą tworząca oprogramowanie wspierające biznes.
Tworzymy aplikacje webowe wykorzystując: Java, Oracle DB, Oracle Apex, AngularJS.
Skontaktuj się z nami, aby porozmawiać o tym jak możemy pomóc w realizacji Twojego projektu!