Zapisywanie danych z Pentaho Kettle do Excel

Najlepszym sposobem na sprawną pracę w Excelu jest... unikanie Excela. Jest to szczególnie widoczne przy pracy z dużymi wolumenami danych. Wszystkie modyfikacje danych i obliczenia wykonasz sprawniej i szybciej w Pentaho a rezultat zapiszesz w formacie Excel. Poniżej znajdziesz odpowiedź jak stworzyć formuły, dodać komentarze, użyć szablonów, utworzyć aktualne wykresy oraz inne przydatne informacje.

Przygotuj Pentaho na pracę z Excelem

Jeśli używasz Pentaho w domyślnej konfiguracji, całkiem prawdopodobne jest że przy otwarciu większego pliku pojawi się błąd "java.lang.OutOfMemoryError: GC overhead limit exceeded". Pentaho napisany jest w Java która bardzo lubi pamięć. Dodatkowo pliki Excela są "ciężkie" - ten format wymaga dużo zasobów by przechować dane. Rozwiązaniem jest przydzielenie Penatho większej ilości pamięci. W katalogu "pdi-ce-x.x.x.x-xxx\data-integration" znajdź plik spoon.bat, otwórz go w notatniku i zmodyfikuj linię:

if "%PENTAHO_DI_JAVA_OPTIONS%"=="" set PENTAHO_DI_JAVA_OPTIONS="-Xms1024m" "-Xmx2048m"

Zmieniając parametr "-Xmx". Ustaw go np. na "-Xmx4g". Ponownie uruchom Penatho po zmianie.

Dodawanie komentarzy do celi Excela

Mamy dwa krok które zapisują dane do formatu Excela - to "Microsoft Excel output" i "Excel Writer". "Excel Writer" oferuje dużo większe możliwości. Oto jak możesz dodać komentarze do komórek Excela:

W polu "cell comment" i "cell comment author" wybierasz źródło skąd pochodzi komentarz i autor komentarza (przygotuj te dane wcześniej w transformacji).

Tworzenie linków URL

W Pentaho możesz utowrzyć odnośnik http który będzie wyświetlany jako link w Microsoft Excel

Pole "Field title" wskazuje na źródło tytułu linku, nazwę linku, a pole "Hyperlink" zawiera szczegóły linku - adres URL.

Dodawanie arkuszy do istniejącego pliku

Oto jak możesz dodać nowe arkusze do pliku. Piszemy do pliku "report.xls" wykorzystując szablon "report_empty.xls". Pamiętaj, że kiedy używasz szablonów, zarówno plik do którego zapisujesz dane jak i szablon muszą być tego samego formatu. Nie możesz w pliku "xls" używać szablonu "xlsx" i odwrotnie.

W polu "Sheet name" widzisz zmienną "${sheetname}". Zmienna ta powstała w innej transformacji a całość połączona zadaniem. Jeśli masz tylko jeden skoroszyt, ta operacja jest zbędna.

Tworzenie dashboard'u w Excelu

Używając Penatho możemy dostarczyć danych do wykresów które mamy w naszych plikach Excel. Utwórz plik Excela zawierający wykres. Dane do wykresu pochodzą z następnego skoroszytu do którego zapiszesz dane z Pentaho.

Tworzenie unikalnego nagłówka i stopki

Jeśli chcesz utworzyć 'custom' nagłówek i stopkę w arkuszu pliku Excela, stwórz odpowiedni szablon - np. taki jak ten na obrazku poniżej.

"Trik" polega na rozpoczęciu pisania do skoroszytu od wiersza A2 i przesunięciu istniejących już wierszy w dół. W ten sposób zachowamy zarówno nagłówek jak i stopkę z szablonu.

Formatowanie warunkowe

By użyć Formatowania Warunkowego, stwórz szablon Excela zawierający takie formatowanie.

 

Następnie w panelu szczegółów zapisu danych określ skąd pochodzi formatowanie cel:

Tworzenie formuł Excela w Pentaho

Sposobem na utworzenie formuł Excela w Pentaho jest wygenorowanie ich w krokach wcześniejszych niż zapis do pliku. Przykładowy przepływ:

Pierwszy krot, to test, generuje X linii Excela. Następny krok dodaje numer sekwencyjny który będzie potrzebny do utworzenia formuły; w formule mamy zazwyczaj odwołanie się do numeru wiersza w pliku. Krok "generate formula string" to "Modified JavaScript value" który posiada kod (jako przyład; sumowanie):

var running_total = "SUM(B2:B"+line_nr+")";

W panelu ze szczegółami zapisu danych określ że dane pole będzie zawierać formułę Excela: