Generowanie arkuszy MS Excela w PHP

4 August 2011 Comments

W repozytorium PEAR znajdziemy klasę służącą do generowania plików MS Excel działającą wszędzie tam gdzie działa PHP (nie wymaga COMa czy innych elementów MS Office/MS Windows). By generować pliki excela będziemy potrzebować pakietów: OLE i Spreadsheet_Excel_Writer. Do oglądania plików XLS możemy wykorzystać również OpenOffice lub darmową przeglądarkę dostępną na stronie Microsoftu (Excel Viewer).

pear install OLE
pear install Spreadsheet_Excel_Writer

A oto pierwszy przykładowy kod:

<?php
include_once 'Spreadsheet/Excel/Writer.php';

// Tworzymy "plik"
$workbook = new Spreadsheet_Excel_Writer();

// plik wysyłamy do przeglądarki (generujemy nagłówki)
$workbook->send('plik.xls');

// Tworzmy arkusz
$worksheet =& $workbook->addWorksheet('Mój Arkusz');

// Podajemy jakieś dane
$worksheet->write(0, 0, 'Imię');
$worksheet->write(0, 1, 'Wiek');
$worksheet->write(1, 0, 'Szymon Majewski');
$worksheet->write(1, 1, '30');
$worksheet->write(2, 0, 'Adam Małysz');
$worksheet->write(2, 1, '31');
$worksheet->write(3, 0, 'Andrzej Lepper');
$worksheet->write(3, 1, '32');

// kończymy operacje
$workbook->close();

Powyższy kod wygeneruje nam plik excela i wyśle bezpośrednio do nas. Można też zapisać plik po stronie serwera:

<?php
require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer('plik.xls');
$worksheet =& $workbook->addWorksheet('Arkusz 1');

// Podajemy jakieś dane
$worksheet->write(0, 0, 'Imię');
$worksheet->write(0, 1, 'Wiek');
$worksheet->write(1, 0, 'Szymon Majewski');
$worksheet->write(1, 1, '30');
$worksheet->write(2, 0, 'Adam Małysz');
$worksheet->write(2, 1, '31');
$worksheet->write(3, 0, 'Andrzej Lepper');
$worksheet->write(3, 1, '32');

$workbook->close();

Co spowoduje zapisanie danych do pliku "plik.xls" na serwerze. Teraz trochę objaśnień. Na początku tworzymy nowy obiekt klasy Spreadsheet_Excel_Writer podając jako argument nazwę pliku. Następnie tworzymy nowy arkusz dla stworzonego obiektu - $worksheet =& $workbook->addWorksheet('NAZWA ARKUSZA');. Następnie do danego arkusza dodajemy dane: $worksheet->write(Y, X, 'TREŚĆ'); gdzi Y to numer wiersza, X - numer kolumny. Metoda write przyjmuje jeszcze kolejny argument - obiekt formatu:

<?php
require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer('plik.xls');
$worksheet = $workbook->addWorksheet('Arkusz 1');

// tworzymy obiekt formatu
$format_bold = $workbook->addFormat();
$format_bold->setBold();

// Podajemy jakieś dane
$worksheet->write(0, 0, 'Imię', $format_bold);
$worksheet->write(0, 1, 'Wiek', $format_bold);
$worksheet->write(1, 0, 'Szymon Majewski');
$worksheet->write(1, 1, '30');
$worksheet->write(2, 0, 'Adam Małysz');
$worksheet->write(2, 1, '31');
$worksheet->write(3, 0, 'Andrzej Lepper');
$worksheet->write(3, 1, '32');

$workbook->close();

Co da nam pogrubiony tekst dla nazw naszych dwóch kolumn. Klasa oferuje znacznie więcej możliwości formatowania. W dokumentacji mamy:

Format::setAlign -- Wyrównanie komórki
Format::setVAlign -- Wyrównanie w pionie komórki
Format::setHAlign -- Wyrównanie w poziomie komórki
Format::setMerge -- Alias do setAlign('merge')
Format::setBold -- Pogrubienie
Format::setBottom -- Długość dolnej granicy komórki
Format::setTop -- Długość górnej granicy komórki
Format::setLeft -- Lewej...
Format::setRight -- Prawej...
Format::setBorder -- Ustawia obramowania komórek na określony styl
Format::setBorderColor -- Ustawia kolor obramowania
Format::setBottomColor -- Kolor dolnego obramowania
Format::setTopColor -- górnego...
Format::setLeftColor -- lewego...
Format::setRightColor -- prawego...
Format::setFgColor -- Kolor wypełnienia
Format::setBgColor -- Kolor tła
Format::setColor -- Kolor treści
Format::setPattern -- Ustawia sposób wypełnienia komórki
Format::setUnderline -- Podkreślenie tekstu
Format::setItalic -- Kursywa
Format::setSize -- Rozmiar czcionki
Format::setTextWrap -- Zawijanie tekstu
Format::setTextRotation -- Ustawia orientację tekstu
Format::setNumFormat -- Ustawia typ liczbowy dla danych z komórki
Format::setFontFamily -- Ustawia zestaw czcionek

Przykład:

<?php
require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer('plik.xls');
$worksheet = $workbook->addWorksheet('Arkusz 1');

$format_moj = $workbook->addFormat();
$format_moj->setBold();
$format_moj->setColor('yellow');
$format_moj->setPattern(1);
$format_moj->setFgColor('blue');

// Podajemy jakieś dane
$worksheet->write(0, 0, 'Imię', $format_moj);
$worksheet->write(0, 1, 'Wiek', $format_moj);
$worksheet->write(1, 0, 'Szymon Majewski');
$worksheet->write(1, 1, '30');
$worksheet->write(2, 0, 'Adam Małysz');
$worksheet->write(2, 1, '31');
$worksheet->write(3, 0, 'Andrzej Lepper');
$worksheet->write(3, 1, '32');

$workbook->close();

Polskie znaki w arkuszach: Dane muszą być kodowane w cp1250. Jeżeli pobierane są z bazy to wystarczy przed pobraniem wykonać:

mysql_query("set names cp1250");
 mysql_query("set character set cp1250");

A teraz przykład zrzutu danych z bazy danych do arkusza:

<?php
$db = new PDO('mysql:host=localhost;dbname=BAZA', 'LOGIN', 'HASLO');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer('plik.xls');
$worksheet = $workbook->addWorksheet('Arkusz 1');
$worksheet->setInputEncoding('utf-8');

$format_moj = $workbook->addFormat();
$format_moj->setBold();
$format_moj->setColor('yellow');
$format_moj->setPattern(1);
$format_moj->setFgColor('blue');

$worksheet->write(0, 0, 'Tytuł', $format_moj);
$worksheet->write(0, 1, 'Treść', $format_moj);

// wrzucamy dużo danych z bazy
$x = $db->query("SELECT * FROM tabela");
foreach($x as $res)
	{
	$i++;
	$worksheet->write($i, 0, $res['title']);
	$worksheet->write($i, 1, $res['content']);
	}
unset($x);
unset($db);
$workbook->close();

Maksymalna długość znaków dla komórki wynosi 255, nadmiar będzie ucinany.

RkBlog

PHP w Akcji, 4 August 2011