Jak vytvořit Excelové sešity pomocí PHP

S příchodem kancelářského balíku Microsoft Office 2007 jeho výrobce představil i nové formáty pro ukládání dokumentů souhrnně nazývané jako OpenXML. Nové přípony souborů DOCX, XLSX, nebo PPTX značí, že data v nich uložená již nejsou v „neprůstřelném" binárním formátu, ale lze se k nim jednoduše dostat přes XML strukturu.

Věřím, že to všichni asi víte, ale pro jistotu připomínám, že po přejmenování dokumentů z Wordu nebo tabulek z Excelu na soubor s koncovkou ZIP si můžete jednoduše prohlédnout, z čeho se jednotlivé dokumenty skládají. Ano, OpenXML jsou v podstatě balíčky ZIP obsahující různé obrázky a řadu souborů XML.

A právě tato relativně jednoduchá konstrukce nových typů dokumentů umožnila vznik PHP knihovny PHPExcel, kterou najdete na Microsoft obdobě SourceForge - portálu CodePlex. S pomocí PHPExcel vytvoříte velice snadno tabulku pro Excel přímo z PHP. Zapomeňte tedy na exporty do CSV, se kterými má mnoho začátečníků problém.

Export dat z PHP přímo do Excelu

Na jednoduchém příkladu si ukážeme export dat z tabulky v databázi do tabulky v Excelu a její uložení do souboru XLSX. Do složky s novým projektem si nakopírujte knihovnu PHPExcel - rozbalte tam složku Classes. Poté si připravte soubor index.php, který rovněž nakopírujete do kořenové složky projektu. Čili struktura by měla být následující:

Veškerou funkcionalitu v našem příkladu zařídíme souborem index.php, do kterého knihovnu PHPExcel includujeme. Poté vytvoříme objekt $excel, kterému nastavíme vlastnosti budoucího souboru - jméno tvůrce, jméno uživatele, který provedl poslední úpravu a název sešitu. Poté odstraníme automaticky vytvořený list Excelu, který později nahradíme svým vlastním.

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

$excel = new PHPExcel;
$excel->getProperties()->setCreator('Jan Polzer');
$excel->getProperties()->setLastModifiedBy('Jan Polzer');
$excel->getProperties()->setTitle('Seznam registrovaných uživatelů');
$excel->removeSheetByIndex(0);

Pokračujeme vytvořením seznamu sloupečků v Excelu a vytvořením nového listu. Tomu nastavíme název a rozměry sloupečků. V příkladu použiji pouze dva sloupce, a to se jménem uživatele a jeho e-mailovou adresou. Samotné vložení obsahu do tabulky zajišťuje funkce setCellValue(), kde jako první parametr uvádíme označení buňky a jako druhý pak její hodnotu.

$cols = array('uživatel' => 'A', 'e-mail' => 'B');
$list = $excel->createSheet();
$list->setTitle('Uživatelé');
$list->getColumnDimension('A')->setWidth(30);
$list->getColumnDimension('B')->setWidth(20);
$list->setCellValue('A1', 'Jméno');
$list->setCellValue('B1', 'E-mail');

Následuje připojení k databázovému serveru pomocí funkce mysql_connect() a výběr databáze funkcí mysql_select_db().

if (!$conn = mysql_connect('localhost', 'uzivatel', 'heslo')):
  print 'Připojení k databázi selhalo.';
  exit;
endif;

if (!mysql_select_db('jmeno_databaze', $conn)):
  print 'Výběr databáze selhal.';
  exit;
endif;

Připravíme si SQL příkaz, kterým získáme data z tabulky uživatelů v databázi. Funkcí mysql_query() tato data načteme. Jak vidíte, funkci je třeba předat parametry v podobě SQL příkazu a odkazu na připojení k databázi. Neměli byste zapomenout na případné ošetření chyb a jejich zobrazení.

$sql = 'SELECT * FROM users';
$data = mysql_query($sql, $conn);

if (!$data):
  print 'Chyba v SQL dotazu.\n' . mysql_error();
  exit;
endif;

Nyní zbývá získaná data z databáze vložit do rozpracovaného listu v Excelu. Připravte si počitadlo řádků. Na prvním řádku jsme nechali vypsat názvy sloupečků, data tedy budeme vkládat od druhého řádku. Jednoduchou smyčkou si projedeme všechny řádky z výsledku dotazu do databáze a již známou funkcí setCellValue() uložíme data z jednotlivých řádků do listu v Excelu. Funkce má stále stejné parametry. Jen pomocí počitadla automaticky měníme číslo řádku ve sloupcích A a B. Druhý parametr již není přímo řetězec, ale příslušná položka z řádku získaného dotazem do databáze.

{syntaxhighlighter brush: php} $rowcounter = 2; while ($row = mysql_fetch_assoc($data)){ $list->setCellValue('A'.$rowcounter, $row['name']); $list->setCellValue('B'.$rowcounter, $row['mail']); $rowcounter++; } {/syntaxhighlighter}

Nakonec už zbývá pouze vytvořit soubor, kam data pro Excel uložíme. To nám zajistí následující dva řádky. V prvním předáváme jako parametr výše připravovaný excelový objekt, ve druhém pak uvádíme cestu k souboru, do kterého budou data uložena. Tečka zajistí uložení do aktuálního adresáře, čili do stejného umístění, odkud běží index.php.

$writer = new PHPExcel_Writer_Excel2007($excel);
$writer->save('./uzivatele.xlsx');

Výsledná podoba souboru index.php tedy bude následující:

{syntaxhighlighter brush: php} <?php include "PHPExcel.php"; include "PHPExcel/Writer/Excel2007.php"; $excel = new PHPExcel; $excel->getProperties()->setCreator('Jan Polzer'); $excel->getProperties()->setLastModifiedBy('Jan Polzer'); $excel->getProperties()->setTitle('Seznam registrovaných uživatelů'); $excel->removeSheetByIndex(0); $cols = array('uživatel' => 'A', 'e-mail' => 'B'); $list = $excel->createSheet(); $list->setTitle('Uživatelé'); $list->getColumnDimension('A')->setWidth(30); $list->getColumnDimension('B')->setWidth(20); $list->setCellValue('A1', 'Jméno'); $list->setCellValue('B1', 'E-mail'); if (!$conn = mysql_connect('localhost', 'uzivatel', 'heslo')): print 'Připojení k databázi selhalo.'; exit; endif; if (!mysql_select_db('jmeno_databaze', $conn)): print 'Výběr databáze selhal.'; exit; endif; $sql = 'SELECT * FROM users'; $data = mysql_query($sql, $conn); if (!$data): print 'Chyba v SQL dotazu.\n' . mysql_error(); exit; endif; $rowcounter = 2; while ($row = mysql_fetch_assoc($data)){ $list->setCellValue('A'.$rowcounter, $row['name']); $list->setCellValue('B'.$rowcounter, $row['mail']); $rowcounter++; } $writer = new PHPExcel_Writer_Excel2007($excel); $writer->save('./uzivatele.xlsx'); print 'Seznam uživatelů byl úspěšně vyexportován.'; ?> {/syntaxhighlighter}

Na závěr ještě ukázka souboru otevřeného v Excelu:

Podobný příklad jsme na Maxiorlovi ještě neměli. Berte jej jako experiment, kterým bych chtěl do budoucna možná rozšířit sérii článků věnovaných vývoji webu a redakčnímu systému Drupal. Uvítám proto vaše náměty a komentáře, zda o podobné tipy pro práci s PHP máte zájem. Pište.