Export do Open XML v PHP

Školení, která pořádám

Článek vyšel na serveru Root.cz.

Kancelářský balík firmy Microsoft umožňuje ve verzi 2007 pracovat s novým formátem souborů založeným na technologiích XML a ZIP. Vzhledem k tomu, že je dobře dokumentovaný a standardizovaný (zatím u organizace ECMA, v ISO schvalovací proces zatím probíhá), tak se hodí pro export dat z webových aplikací mnohem lépe než starší binární formáty. Konkurenční formát ODF se pro tento účel samozřejmě hodí také, zákazníci ale většinou chtějí export do Office.

Pravděpodobně nejužitečnější a obvykle také nejpožadovanější je export tabulkových dat do Excelu. Krátce po uvedení Office 2007 vznikla třída PHPExcel, která umožňuje právě tento druh exportu. Třída je od začátku konstruována tak, aby bylo možné výsledek uložit do různých formátů, jediný podporovaný je ale zatím Excel 2007. Díky této třídě není nutné do podrobností studovat sáhodlouhou normu a lze použít jednoduché API.

Příklad

Na jednoduchém příkladu si ukážeme, jak se s touto třídou pracuje. Naším cílem bude vytvořit export přehledu kurzů měn pro daný den ve všech směnárnách. Na listech budou uloženy jednotlivé měny, v řádcích směnárny a ve sloupcích jednotlivé kurzy. Data vybereme z MySQL databáze z tabulek currencies (měny), subjects (směnárny) a quotations (kurzy) a uložíme je do pole $quotations. Po vytvoření základního Excelovského objektu a nastavení jeho vlastností odebereme automaticky vytvořený list. Následně postupně pro každou měnu vytvoříme nový list, nastavíme mu titulek a hlavičky a pro všechny směnárny vypíšeme jejich kurzy. Nakonec sešit uložíme do souboru s koncovkou .xlsx.

<?php
include "PHPExcel.php";
include "PHPExcel/Writer/Excel2007.php";

$date = "2007-02-14";

$quotations = array();
$result = mysql_query("
    SELECT quotations.*, currencies.mark, subjects.name
    FROM quotations
    INNER JOIN currencies ON quotations.currency_id = currencies.id
    INNER JOIN subjects ON quotations.subject_id = subjects.id
    WHERE quotations.quotation_date = '$date'
    ORDER BY currencies.mark, subjects.name
");
while ($row = mysql_fetch_assoc($result)) {
    $quotations[$row["mark"]][] = $row;
}
mysql_free_result($result);

$excel = new PHPExcel;
$excel->getProperties()->setCreator("Jakub Vrána");
$excel->getProperties()->setLastModifiedBy("Jakub Vrána");
$excel->getProperties()->setTitle("Akcie $date");
$excel->removeSheetByIndex(0);

$columns = array("name" => "A", "d_quotation_buy" => "B", "d_quotation_sale" => "C", "v_quotation_buy" => "D", "v_quotation_sale" => "E");
foreach ($quotations as $mark => $rows) {
    $sheet = $excel->createSheet();
    $sheet->setTitle($mark);
    $sheet->getColumnDimension('A')->setWidth(30); // nastavení šířky prvního sloupce
    $sheet->setCellValue("A1", "Směnárna");
    $sheet->setCellValue("B1", "Devizy nákup");
    $sheet->setCellValue("C1", "Devizy prodej");
    $sheet->setCellValue("D1", "Valuty nákup");
    $sheet->setCellValue("E1", "Valuty prodej");
    foreach ($rows as $i => $row) {
        foreach ($columns as $key => $sl) {
            $sheet->setCellValue($sl . ($i + 2), $row[$key]);
        }
    }
    
    // doplnění průměru a formátování
    foreach ($columns as $sl) {
        $sheet->setCellValue($sl . ($i + 3), ($sl == "A" ? "Průměr" : "=AVERAGE($sl" . "2:$sl" . ($i + 2) . ")"));
        $sheet->getStyle($sl . 1)->getFont()->setBold(true);
        $sheet->getStyle($sl . ($i + 3))->getFont()->setBold(true);
    }
}

$writer = new PHPExcel_Writer_Excel2007($excel);
$writer->save("$date.xlsx");
?>

Všechny texty se ukládají v kódování UTF-8.

Obsah vytvořeného souboru

vytvořený souborVytvořený soubor je běžný archiv ve formátu ZIP, pouze má jinou koncovku. Počáteční bod, ze kterého se odvíjí vše ostatní, leží v souboru _rels/.rels. To je jednoduchý XML soubor, který se odkazuje na ostatní zdroje dat spolu s uvedením jejich typu. Druhým vstupním bodem je soubor [Content_Types].xml, kde je uveden výčet všech typů obsažených v souboru. V adresáři docProps/ vygenerovaného souboru jsou uloženy informace o dokumentu (principiálně mohou být kdekoliv, záleží na tom, co je uvedeno v souboru _rels/.rels) a v adresáři xl/ je vlastní pracovní sešit s jednotlivými listy. Ten opět obsahuje soubor _rels/.rels, dále seznam jednotlivých listů v souboru workbook.xml, použité styly v souboru styles.xml a textové řetězce v sharedStrings.xml. V Excelu jsou totiž textové řetězce uloženy odděleně, což šetří místo a usnadňuje lokalizaci, na druhou stranu to ale samozřejmě zesložiťuje proces načítání a ukládání dat (alternativně lze řetězce uložit i přímo do listu, ale z výše uvedených důvodů to sám Excel nedělá). Data jednotlivých listů jsou uložena v XML souborech v adresáři worksheets/.

Použitá organizace souborů je poměrně přehledná a jednotlivé části jsou od sebe dobře odděleny, což umožňuje jak poměrně jednoduché načítání, tak i zapisování dat. Pokud použijeme zmiňovanou knihovnu, tak se tím ale nemusíme vůbec zabývat.

Související odkazy

Jakub Vrána, Seznámení s oblastí, 26.2.2007, diskuse: 20 (nové: 0)

Diskuse

mark:

Jen bych dodal, že ten "konkurenční ODF" (OpenDocumentFormat) je formátem skutecne standardizovaným (nejen ECMA ale také ISO :-)), velmi dobře zdokumentovaným, podporovaný široce ze stran nejen evrospkých vládních institucí a jeho implementace je skutečně volná bez nutnosti žádat microsoft o příslušné licence.

MSOffice s formátem ODF samozřejmě umí také pracovat (prostřednictvím doplňku), nemluvě o řadě dalších kancelářských aplikací které mají jeho podporu implementovanou nativně (OpenOffice, KOffice, Docs.Google.com, AbiWord, atd..).

To jen tak pro úplnost ..

ikona Jakub Vrána OpenID:

Já ODF samozřejmě plně respektuji, ale uvedené body splňuje přece i Open XML. ECMA je plnohodnotná standardizační organizace (nebo snad přestaneme používat JavaScript proto, že je zaregistrovaný „jen“ u ECMA?), dokumentace je taktéž vynikající (možná jen trochu moc dlouhá) a Microsoft o žádné licence k použití žádat nemusím.

Doplněk ODF pro MS Office je na tom podle dostupných informací (http://www.root.cz/clanky/ms-office-podporuje-odf-opravdu/) zatím dost žalostně, to ale samozřejmě není chyba toho formátu (byť je to jeho nedostatek pro reálné použití).

Drom:

Ano, ja bych javascript zrusil, to je takovej bastl...

ikona Jakub Hejda:

Možná je můj dotaz trochu mimo, ale...
Nevíte o nějaké PHP knihovně, která umí přečíst XLS soubor s českými znaky?

ikona spaze:

http://paggard.com/projects/xls.reader/
http://sourceforge.net/projects/phpexcelreader
http://www.excelparser.com/index.php
http://www.appelsiini.net/~tuupola/
http://www.softclub.org/

Osobne mam v praxi rozjetej ten ze sf.netu.

JAZBY:

Díky. Sice to bylo fakt mimo mísu, ale ulehčil jsi mi život. Nejlepší je asi ten sf.net

spol:

škoda, že jen pro PHP5:)

Jano:

Zdravím,
chcem Vás touto cestou požiadať o pomoc...
Snažím sa vyššie uvedený PHPExcel class implementovať, resp. prepojiť s databázou MySQL veľmi podobne ako je vyššie v príklade uvedené.
Narazil som však na problém, ktorý sa mi nedarí riešiť ani nastavením samotnej databázy na kódovanie utf8_general_ci, ani nastavením daného stĺpca v tabuľke na utf8_general_ci (utf8_slovak_ci) a dokonca ani v kombinácií s textom kódovaným pomocou funkcie utf8_encode() a následne uloženým do tabuľky.
Prosím Vás o radu, ďakujem za Vás čas a ochotu. Jano

ikona Jakub Vrána OpenID:

Viz http://php.vrana.cz/mysql-4-1-kodovani.php.

pojízdná kočka:

Vygenerované xlsx soubory mi nejdou otevřít v Open Office (verze 3.0.0. Windows). Mají i jinou velikost než ty na http://www.codeplex.com/PHPExcel/Wiki/View.aspx?title=Examples
Netuší někdo, v čem je chyba (v knihovně nebo officech)? díky

pojízdná kočka:

...popřípadě může být ještě mezi monitorem a židlí ;-) ale nevim - prostě jsem jen spustila ty ukázkové skripty a nijak do nich nezasahovala. např. ten 05featuredemo se sice jeví jako nějaký zapakovaný dokument, ale je o cca 300 bajtů menší než ten původní/distribuovaný.
doplním, že mluvím o verzi 1.6.5. jinak, já mám PHP 5.2 (z distribuce EasyPHP se standardními rozšířeními)

Jan Kahoun:

Zdravím,
chteěl bych jen upozornit, že tato třída potřebuje dost paměti při zápisu a čtení "většího" počtu dat. cca. 1 GB paměti si vezme při zápisu 10 000 záznamů (což ještě není tak velké číslo) a bohužel zde není možnost vytvořit soubor postupným vkládáním dat do souboru... ale jak jsem zjistil, tak to neumí žádná podobná třída, takže jako jediné možné řešení pro uložení většího množství dat, se jeví vytvoření tabulky v html, což ovšem vede k velkým souborům, pokud uvažujeme i ukládáni více jak 1 000 000 záznamů, může to být velký problém.

pojízdná kočka:

milion záznamů? pardon, jestli řeknu něco nemístného, ale.. ono už u nových verzí těchhle spreadsheetů neplatí omezení na 65535 řádků?

Jan Kahoun:

Mrknete se napriklad sem: http://www.office4you.cz/index.php?Itemid=…&task=view
Jak zjistite novy format XLSX umoznuje ulozit vice jak 65535 radku na jeden list. Dulezite je, ze tech listu tam muze byt "neomezene". Omezenim je pouze dostupna pamet ;-)

honza suda:

Zdravim,
dostal jsem se do uzkych pri vytvareni .xlsx souboru (s jinou tridou, PHPExcel mi nefunguje), popsal jsem to zde
http://forum.builder.cz/read.php?20,3077771
kdyby nekdo mel v hlave napad co s tim muze byt, bylo by to super
diky
h.)

Jitka Trnková:

Dobrý den,
na webu www.agenturavcelicka.cz mi běží soutěž a potřebovala bych údaje z odpovědních soutěžních formulářů dostat do Excelu, aniž bych u toho denně hodiny seděla :-(, tzn. nějak automaticky ukládat.
Odpovědní soutěžní formuláře, které se vyplňuji na webu, mi chodí na e-mail v tomto formátu:

Odpověď na soutěž z webu ze dne: 29.05.2011
--------------------------------------------------------------------------

Jméno: J
Email: T
Telefon: 607000000

Odpovědi:
Agentura Včelička zajišťuje tyto služby: prodej-jazykovych-ucebnic
V našem ceníku uvádíme ceny v přepočtu na: 1-normostrana

Souhlasím s pravidly: ano

-------------------------------------------------------------------------

POTŘEBOVALA BYCH AUTOMATICKÉ UKLÁDÁNÍ DO EXCELU, A TO NÁSLEDUJÍCÍ DATA Z FORMULÁŘE: JMÉNO A PŘÍJMENÍ, TELEFON, OOTÁZKA A ODPOVĚĎ 1, OTÁZKA A ODPOVĚĎ 2.

Můj správce webu to prý neumí a já jsem úplný laik... :-(
Za kolik byste mohl toto pro mne udělat?

Díky.
info@agenturavcelicka.cz

ikona Jakub Vrána OpenID:

Děkuji za nabídku práce, nemám o ni zájem.

Petr.jx:

Dobrý den Jakube, Váš skript jsem vyzkoušel, ale stalo se mi, že se do excelu neukládá respekt. nezobrazuje diakritika, nejen z datbáze ale i z obyčejného řetězce z proměnné. Nevíte jak to ošetřit? děkuji moc za Vaši radu.

ikona Jakub Vrána OpenID:

Je potřeba používat kódování UTF-8.

Palo:

Dobrý den Jakub, chcem sa opýtat na citanie alebo zobrazovanie danych suborov .xlsx subory v xls mi idu preniest do php ale xlsx nie.

Vložit komentář

Používejte diakritiku. Vstup se chápe jako čistý text, ale URL budou převedeny na odkazy a PHP kód uzavřený do <?php ?> bude zvýrazněn. Pokud máte dotaz, který nesouvisí s článkem, zkuste raději diskusi o PHP, zde se odpovědi pravděpodobně nedočkáte.

Jméno: URL:

avatar © 2005-2018 Jakub Vrána. Publikované texty můžete přetiskovat pouze se svolením autora. Ukázky kódu smíte používat s uvedením autora a URL tohoto webu bez dalších omezení Creative Commons. Můžeme si tykat. Skripty předpokládají nastavení: magic_quotes_gpc=Off, magic_quotes_runtime=Off, error_reporting=E_ALL & ~E_NOTICE a očekávají předchozí zavolání mysql_set_charset. Skripty by měly být funkční v PHP >= 4.3 a PHP >= 5.0.