Klienci nie znają litości – często proszą o drobne zmiany, których wprowadzenie może nie być tak „drobną” sprawą. „Proszę dane generować do XLSX zamiast do CSV”, „proszę o możliwość eksportu do DOC zamiast RTF” i temu podobne prośby – choć docelowe pliki niewiele się różnią to sama budowa plików różni się znacząco.
Główna różnica to prostota i „otwartość” tychże formatów – oczywiście CSV i RTF są bardzo proste do tworzenia/edycji, podczas gdy XLSX czy DOCX to wyższa szkoła jazdy :)
W niniejszym wpisie chciałbym bliżej się przyjrzeć pierwszemu problemowi, bowiem ostatnio na takowy trafiłem: klient zażyczył sobie eksportowanie danych klientów do Excela zamiast CSV, jak to wcześniej wykonałem. Miałem następujące argumenty dlaczego powinniśmy eksportować dane do CSV:
- Pliki stworzone pod Excela mogą być otwierane przede wszystkim w płatnym oprogramowaniu na płatnym systemie operacyjnym – nie każdego stać na takie oprogramowanie; aby otworzyć XLSX w innym oprogramowaniu należy szukać konwerterów lub liczyć na szczęście, że oprogramowanie, które posiadamy ma wbudowany konwerter.
- CSV jest obsługiwany przez wiele programów i języków programowania (domyślnie) – pliki te łatwo importować do Excela, wystarczy bowiem kilka kliknięć i wszystkie potrzebne dane mamy w Excelu.
Oczywiście mowa tutaj o prostych danych, bez wykorzystywania potencjału Excela ponad możliwości plików CSV.
- No i oczywiście prostota rozwiązania, gdyż niepotrzebne było tutaj zaciąganie Excela.
Ostatecznie jednak musiałem dopisać możliwość eksportowania danych do Excela, dodatkowo zostawiając eksport do CSV :) A więc do roboty.
PHP i Excel
Trudno pogodzić powyższe technologie – Excel to komercyjny produkt o zamkniętym kodzie źródłowym należącym do firmy Microsoft, która opracowała konkurencyjny język programowania – .NET. Natomiast PHP jest językiem o otwartym kodzie źródłowym, który najlepiej sprawdza się na Linuksach.
Na szczęście jest rozwiązanie problemu – prostsze niż można by się spodziewać. Chwila przeszukiwania Internetu i trafiłem na świetną bibliotekę do obsługi plików, jakie generuje Microsoft Excel (głównie XLS oraz XLSX) – PHPExcel.
Wiele można by pisać o tej bibliotece, jednak wypiszę tylko najważniejsze i podstawowe cechy, po szczegóły i zaawansowane przypadki użycia odsyłam do dokumentacji technicznej:
- biblioteka jest darmowa i udostępniana na licencji LGPL,
- stabilna i rozwijana od 2007 roku,
- bardzo małe wymagania: PHP 5.2.0 + kilka modułów, które są wbudowane w praktycznie każdy płatny serwer (także VPS): php_zip, php_xml oraz php_gd2,
- posiada mnóstwo funkcji i możliwości, m. in. :
- tworzenie nowych i edycja istniejących dokumentów,
- możliwość ustawiania meta danych dla dokumentu,
- tworzenie wielu arkuszy w obrębie dokumentu,
- możliwość dodawania zaawansowanych reguł i formuł,
- możliwość dowolnego formatowania kolumn i wierszy, w tym scalania, formatowania tekstu, szerokości i wysokości komórek (także „auto-wymiary”),
- obsługa hiperłączy oraz wbudowanych typów danych,
- obsługa formatowania warunkowego,
- możliwość zabezpieczania dokumentu,
- możliwość ustawiania opcji drukowania czy właściwości dokumentu (orientacja, rozmiar papieru, marginesy i inne),
- obsługa różnych wersji Excela, zarówno starszych, jak i aktualnych, oraz pokrewnych formatów (Open Office Calc; .ods), CSV.
To nie wszystkie możliwości, a pełna ich lista pozytywnie zaskakuje i jedynym ograniczeniem przy zarządzaniu plików Excela z poziomu PHP pozostaje wyobraźnia programisty :)
Praktyczny przykład
Zapewne nikt nie zajrzał do tego wpisu by czytać wypracowania, dlatego czas zarzucić kilkoma linijkami kodu. Zacznijmy od wczytania naszej biblioteki:
1 2 3 4 | include './phpexcel/PHPExcel.php'; include './phpexcel/PHPExcel/Writer/Excel2007.php'; $php_excel = new PHPExcel(); |
Utworzyliśmy obiekt Excela w wersji 2007. Najpierw możemy dodać meta informacje o samym dokumencie:
1 2 3 | $php_excel->getProperties()->setCreator('Kamil Brenk'); $php_excel->getProperties()->setTitle('Wydatki 2010'); $php_excel->getProperties()->setDescription('Roczne zestawienie wydatków'); |
Dostępnych jest więcej meta danych, ale poszczególne metody można znaleźć w dokumentacji. Dla własnej wygody można użyć także method chaining, z którego będziemy korzystać:
1 2 3 4 | $php_excel->getProperties() ->setCreator('Kamil Brenk') ->setTitle('Wydatki 2010') ->setDescription('Roczne zestawienie wydatków'); |
Następnie musimy odwołać się do arkusza, na którym chcemy pracować – w tym przypadku na domyślnym arkuszu:
1 2 | $php_excel->setActiveSheetIndex(0); $active_sheet = $php_excel->getActiveSheet(); |
Biblioteka umożliwia tworzenie i edycję wielu arkuszy, więc musimy jasno wskazać na którym arkuszu chcemy pracować. Warto również przypisać od razu aktywny arkusz do zmiennej, o ile pracujemy tylko na jednym arkuszu.
Kolejną rzeczą jest dodawanie konkretnych danych, np. :
1 2 3 4 | $active_sheet ->setCellValueByColumnAndRow(1, 1, 'Imię') ->setCellValueByColumnAndRow(2, 1, 'Nazwisko') ->setCellValueByColumnAndRow(3, 1, 'Data urodzenia'); |
Będziemy pracować na trzech kolumnach, do których dodaliśmy nagłówki: imię, nazwisko i datę urodzenia. Możemy ten wiersz uzupełnić o jakieś style i także tutaj jest spora dowolność. Zacznijmy od ustawienia czcionki:
1 2 3 | $active_sheet->getStyle('A1:C1')->getFont() ->setBold(true) ->getColor()->setRGB('FFFF00'); |
Dajmy również dla urozmaicenia obramowanie:
1 2 3 4 5 6 7 8 9 | $active_sheet->getStyle('A1:C1')->getBorders() ->applyFromArray(array( 'bottom' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => 'FFFF00' ) ) )); |
Na koniec ustawmy jakieś tło (wypełnienie komórki):
1 2 3 | $active_sheet->getStyle('A1:C1')->getFill() ->setFillType(PHPExcel_Style_Fill::FILL_SOLID) ->getStartColor()->setRGB('000000'); |
Warto również ustawić szerokość kolumn, by dane lepiej się prezentowały:
1 2 3 | $active_sheet->getColumnDimension('A')->setWidth(25); $active_sheet->getColumnDimension('B')->setWidth(25); $active_sheet->getColumnDimension('C')->setWidth(15); |
Można również ustawić automatyczne dopasowanie szerokości do ilości tekstu:
1 2 3 | $active_sheet->getColumnDimension('A')-> setAutoSize(true); $active_sheet->getColumnDimension('B')-> setAutoSize(true); $active_sheet->getColumnDimension('C')-> setAutoSize(true); |
W ten sposób wyodrębniliśmy nazwy kolumn w lepiej widocznej formie (powiedzmy). Czas dodać więcej danych do naszego arkusza:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | $clients = array( array( 'firstname' => 'Adam', 'lastname' => 'Nowak', 'birthdate' => '1976-05-14' ), array( 'firstname' => 'Łukasz', 'lastname' => 'Kowalski', 'birthdate' => '1988-07-03' ), array( 'firstname' => 'Krzysztof', 'lastname' => 'Krzywousty', 'birthdate' => '1623-05-23' ) ); for ($i = 0, $clients_num = count($clients) - 1; $i <= $clients_num; $i++) { $row = $i + 2; $active_sheet ->setCellValueByColumnAndRow(0, $row, $clients[$i]['firstname']) ->setCellValueByColumnAndRow(1, $row, $clients[$i]['lastname']) ->setCellValueByColumnAndRow(2, $row, $clients[$i]['birthdate']); } |
I gotowe, wprowadziliśmy już dane. Możemy dalej rozwijać nasz arkusz, dodając milion innych rzeczy, ale nie będę wszystkiego opisywał – API biblioteki jest wystarczająco proste, wystarczy zajrzeć do dokumentacji.
Czas wygenerować nasz plik Excela:
1 2 | $obj_writer = new PHPExcel_Writer_Excel2007($php_excel); $obj_writer->save('example1.xlsx'); |
I to wszystko, mamy gotowy plik Excela (XLSX). Jest jednak dużo więcej opcji eksportu dokumentu, np. do pliku PDF (przy pomocy innej ciekawej biblioteki, TCPDF, która jest domyślnie dołączona do biblioteki PHPExcel):
1 2 | $obj_writer = new PHPExcel_Writer_PDF($php_excel); $obj_writer->save('example1.pdf'); |
Nie wiem skąd pojawiło się to czarne tło :-) Można też wygenerować jako dokument HTML:
1 2 | $obj_writer = new PHPExcel_Writer_HTML($php_excel); $obj_writer->save('example1.html'); |
Dane możemy eksportować również do pliku CSV czy Excela dla starszych wersji programu.
Warta wspomnienia jest również możliwość tworzenia plików w pamięci (do wyboru kilka rodzajów pamięci: pamięć wbudowana, dysk twardy, APC, Memcache czy Wincache), by później wygenerowany dokument zarządzać we własnym zakresie – przesłać do przeglądarki z odpowiednimi nagłówkami HTTP lub zapisać do bazy danych / na dysku.
Podsumowanie
Omawiana biblioteka przykuła mą uwagę dzięki ogromowi funkcjonalności, które oferuje – używając jej nie straszny będzie Ci żaden klient :) W dodatku całość jest napisana bardzo ładnie (OOP), wydajnie (wspiera buforowanie na wielu poziomach) i nie ma dużych wymagań.
Warto również wiedzieć, iż istnieją konkurencyjne biblioteki do omawianej przeze mnie, a na szczególną uwagę zasługuje Spreadsheet_Excel_Writer wchodząca w skład pakietu PEAR. Z tego co widzę posiada jednak mniej funkcji, m. in. nie umożliwia importu istniejących już arkuszy celem dalszej edycji.
I to by było na tyle – z biblioteką PHPExcel tworzenie plików Excela w PHP jest błahostką.
Przydatne linki
- PHPExcel – PHPExcel Documentation
- Spreadsheet_Excel_Writer – alternatywna biblioteka wchodząc w skład pakietu PEAR
- Odczytywanie plików Excel w PHP przy użyciu biblioteki PHPExcel
- Tworzenie arkuszy kalkulacyjnych za pomocą biblioteki PHPExcel
Jedno pytanie tylko mam: po jakiego grzyba coś pobierać, a potem zapisywać?
2
3
$php_excel->getProperties()->setTitle('Wydatki 2010');
$php_excel->getProperties()->setDescription('Roczne zestawienie wydatków');
Najpierw getter potem setter. Czy pisałeś to, żeby pokazać istniejące metody czy ta biblioteka tak dziwnie działa ?:>
Słyszałem o tej bibliotece, ale jeszcze nie używałem w praktyce. A w obecnym projekcie będe właśnie potrzebował czegoś takiego. Widzę, że API ma całkiem przyjemne (ciekawe jak z opcjami exportu i importu), tak więc dzięki za przypomnienie o bibliotece :)
Też korzystam (korzystałem) z PHPExcel, korzystałem bo dawno nie miałem potrzeby jej używać. Zauważyłem jedną niedogodność. Tworzyłem cennik w którym było kilkanaście tysięcy wierszy, kolorowane naprzemiennie, do tego funkcje wyliczające wartości brutto, przeliczające ceny w € i $. Niestety w takim zestawie skok użycia pamięci był bardzo duży, tak, że musiałem zrezygnować z części formatowania.
Tak czy inaczej, bardzo przydatne narzędzie.
Ja używam Spreadsheet_Excel_Writer i jeszcze nie znalazła się rzecz której nie udało mi się ogarnąć przy jej użyciu. No ale nie robiłem jakichś super zaawansowanych arkuszy. Wpis bardzo dobry, ale dodał byś link do dokumentacji „wystarczy zajrzeć do dokumentacji. [LINK]” .
Korzystając z okazji wrzucę swoje „tytułem wstępu” na temat PHP Excel: Odczytywanie plików Excel w PHP oraz Tworzenie arkuszy kalkulacyjnych. Autor biblioteki mocno się napracował, aby jego biblioteka była aż tak dobra.
@SebaZ:
Nie bez powodu wspomniałem o method chaining – wywołując getProperties() zwracany jest nam obiekt, na którym dalej możemy pracować. Biblioteka tak właśnie działa.
@singles:
Import i eksport też jest bardzo prosty do zrealizowania :)
@Vokiel:
Racja, dla większych dokumentów biblioteka ta potrafi zamulić, ale w sumie nic dziwnego. Na szczęście mamy wbudowane wsparcie dla serwerów Memcache, można odpalać w konsoli, w cronie i inne, co w większości przypadków powinno pomóc pracować nawet z wielkimi plikami Excela.
@skowron-line:
Dałem link do dokumentacji, jak i do obu bibliotek :) „po szczegóły i zaawansowane przypadki użycia odsyłam do [link]dokumentacji technicznej[/link]”. Ps. dodałem „Przydatne linki”.
@batman:
Nie widziałem wcześniej Twoich artykułów o Excelu, więc dzięki za uzupełnienie wpisu.
a ja mam pytanie :) czy wie ktoś czy jest możliwość exportowania bazy danych zapisanej w exelu do wordpressa jako osobne posty ??
np, mam bazę firm i do wordpressa chce ją wrzucić jako pojedyncze posty :) kto mi powie czy można będzie MENEM roku!! :D bo ja kobietka średnio się na tym znam :D
@szarki: nie widziałem gotowego rozwiązania, ale dałoby się taką operację zautomatyzować (choć to raczej sporo pracy i mając mniej niż 50 postów w Excelu nawet nie zaczynałbym).