Hlavní navigace

Export do Open XML v PHP

Jakub Vrána

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. Je dobře dokumentovaný a standardizovaný (probíhá ISO schvalovací proces), a proto se hodí pro export dat z webových aplikací mnohem lépe než starší binární formáty.

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

OpenXML

Vytvoř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.

Našli jste v článku chybu?

26. 2. 2007 9:32

Dobrý den, export do OpenXML je obecně u běžných uživatelů (firemních) velmi žádán. Proto se jím na Rootu zabýváme. Ať se nám to líbí nebo ne, mnoho vývojářů se musí zabývat produkty Microsoftu a většinu lidí (včetně mě) zajímají užitečná řešení obecně a ne jen Linux.

27. 2. 2007 16:34

pexxi (neregistrovaný)
Zas, to je trosku aj problem OO, ze nerozoznava subory podla hlavicky, ale podla pripony. V tomto je, potom, Excel vyzretejsi...
Lupa.cz: Kdo pochopí vtip, může jít do ČT vyvíjet weby

Kdo pochopí vtip, může jít do ČT vyvíjet weby

Vitalia.cz: Mondelez stahuje rizikovou čokoládu Milka

Mondelez stahuje rizikovou čokoládu Milka

120na80.cz: 5 nejčastějších mýtů o kondomech

5 nejčastějších mýtů o kondomech

Vitalia.cz: Proč vás každý zubař posílá na dentální hygienu

Proč vás každý zubař posílá na dentální hygienu

Vitalia.cz: Vychytané vály a válečky na vánoční cukroví

Vychytané vály a válečky na vánoční cukroví

Vitalia.cz: Paštiky plné masa ho zatím neuživí

Paštiky plné masa ho zatím neuživí

120na80.cz: Co všechno ovlivňuje ženskou plodnost?

Co všechno ovlivňuje ženskou plodnost?

120na80.cz: Horní cesty dýchací. Zkuste fytofarmaka

Horní cesty dýchací. Zkuste fytofarmaka

Podnikatel.cz: Podnikatelům dorazí varování od BSA

Podnikatelům dorazí varování od BSA

Měšec.cz: U levneELEKTRO.cz už reklamaci nevyřídíte

U levneELEKTRO.cz už reklamaci nevyřídíte

Podnikatel.cz: Udávání kvůli EET začalo

Udávání kvůli EET začalo

Lupa.cz: Proč firmy málo chrání data? Chovají se logicky

Proč firmy málo chrání data? Chovají se logicky

Vitalia.cz: Láska na vozíku: Přitažliví jsme pro tzv. pečovatelky

Láska na vozíku: Přitažliví jsme pro tzv. pečovatelky

Měšec.cz: Air Bank zruší TOP3 garanci a zdražuje kurzy

Air Bank zruší TOP3 garanci a zdražuje kurzy

DigiZone.cz: Flix TV: dva set-top boxy za korunu

Flix TV: dva set-top boxy za korunu

Vitalia.cz: Taky věříte na pravidlo 5 sekund?

Taky věříte na pravidlo 5 sekund?

Vitalia.cz: Když přijdete o oko, přijdete na rok o řidičák

Když přijdete o oko, přijdete na rok o řidičák

Lupa.cz: Teletext je „internetem hipsterů“

Teletext je „internetem hipsterů“

Lupa.cz: Propustili je z Avastu, už po nich sahá ESET

Propustili je z Avastu, už po nich sahá ESET

Vitalia.cz: Spor o mortadelu: podle Lidlu falšovaná nebyla

Spor o mortadelu: podle Lidlu falšovaná nebyla