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: