Zapis i odczyt arkuszy kalkulacyjnych Excela w PHP

Arkusz kalkulacyjny Excel jest jednym z najpopularniejszych narzędzi do tworzenia zestawień danych. Jest to program prosty w obsłudze i bardzo powszechny. W artykule opiszę w jaki sposób odczytywać i zapisywać pliki XLS w skryptach PHP. Jako przykład zastosowania przedstawię aplikację Autokomis, w której baza danych jest wypełniana na podstawie arkusza kalkulacyjnego utworzonego programem Excel.

Statyczna tabela XHTML

Najprostszym sposobem generowania arkuszy
kalkulacyjnych XLS w PHP jest zmiana nagłówka
HTTP strony zawierającej tabelę. Jeśli dokument
XHTML zawierający tabelę:

...


...

opatrzymy nagłówkiem HTTP:

Content-type: application/vnd.ms-excel

to strona taka zostanie otworzona przez program
Excel. Nagłówek HTTP można ustalić wywołując
funkcję {stala}header(){/stala}:

...


....

Dodatkowy element nagłówka Content-Disposition
ustala nazwę pliku.

Tabela generowana dynamicznie

Oczywiście tabela XHTML może być generowana
dynamicznie na podstawie bazy danych,
pliku tekstowego czy tablicy. Zadanie takie
wykonuje następujący skrypt:

...

\';
echo \"\";
echo \"\";
echo \'\';
}?
>

$dana[0]
$dana[1]

Odczyt pliku XLS

Do odczytania pliku XLS można użyć klasy
{stala}Spreadsheet_Excel_Reader{/stala} zawartej w bibliotece
PHP-ExcelReader (strona domowa: http://sourceforge.net/projects/phpexcelreader/).
Po pobraniu biblioteki należy zmienić jedną
linijkę w pliku {stala}reader.php{/stala}. Instrukcję:

require_once \'Spreadsheet/Excel/Reader/OLERead.php\';

zmieniamy na:

require_once \'oleread.inc\';

Po wprowadzeniu powyższej zmiany klasa
{stala}Spreadsheet_Excel_Reader{/stala} jest gotowa do
użycia

.

Najpierw do skryptu dołączamy bibliotekę:

require_once \'Excel/reader.php\';

a następnie tworzymy obiekt {stala}$xls{/stala} klasy {stala}Spreadsheet_Excel_Reader{/stala}:

$xls = new Spreadsheet_Excel_Reader();

Ustalamy kodowanie znaków:

$xls->setOutputEncoding(\'cp1250\');

po czym odczytujemy plik {stala}slownik.xls{/stala}:

$xls->read(\'slownik.xls\');

Po wywołaniu metody {stala}read(){/stala} obiekt {stala}$xls{/stala}
będzie zawierał tablicę o nazwie sheets. Elementy
tej tablicy odpowiadają poszczególnym
arkuszom z odczytanego pliku. Pierwszy z arkuszy
jest dostępny jako:

$xls->sheets[0]

Każdy arkusz ma trzy składowe:

• numRows – liczba wierszy arkusza,
• numCols – liczba kolumn arkusza,
• cells – komórki arkusza.

Dostęp do składowych uzyskujemy następująco:

//liczba wierszy
$xls->sheets[0][\'numRows\']
//liczba kolumn
$xls->sheets[0][\'numCols\']
//komórki
$xls->sheets[0][\'cells\']
//komórka o adresie [3;7]
$xls->sheets[0][\'cells\'][3][7]
Skrypt:
for ($i = 1; $i <= $xls->sheets[0] [\'numRows\']; $i++) {
  for ($j = 1; $j <= $xls->sheets[0] [\'numCols\']; $j++) {
    echo $xls->sheets[0][\'cells\'i] [$j] . \' \';
  }
  echo \'
\';
}

wydrukuje pierwszy arkusz zawarty w odczytanym
pliku.

Odczyt kilku arkuszy

Rys. 1 przedstawia plik dane.xls zawierający dwa
arkusze. Arkusze te nazywają się samochody
oraz wyposażenie, co widać na zakładkach. Każdy
arkusz jest dostępny jako jeden element tablicy
sheets. Dane pierwszego arkusza wydrukujemy
odwołując się do {stala}$xls->sheets[0]{/stala}:

for ($i = 1; $i <= $xls->sheets[0] [\'numRows\']; $i++) {
  for ($j = 1; $j <= $xls->sheets[0][\'numCols\']; $j++) {
    echo $xls->sheets[0][\'cells\'][$i][$j];
  }
}

zaś drugiego – wykorzystując {stala}$xls->sheets[1]{/stala}:

for ($i = 1; $i <= $xls->sheets[1] [\'numRows\']; $i++) {
  for ($j = 1; $j <= $xls->sheets[1][\'numCols\']; $j++) {
    echo $xls->sheets[1][\'cells\'][$i][$j];
  }
}

Tworzenie pliku XLS

Do zapisywania danych w pliku XLS służy klasa
{stala}Spreadsheet_Excel_Writer{/stala} zawarta w bibliotece
PEAR. Po zainstalowaniu pakietów
{stala}PEAR::Spreadsheet_Excel_Writer{/stala} oraz {stala}PEAR::OLE{/stala} możemy przystąpić do napisania pierwszego
skryptu. Dołączamy bibliotekę:

require_once \"Spreadsheet/Excel/Writer.php\";

po czym tworzymy nowy obiekt {stala}$xls{/stala}:

$xls = new Spreadsheet_Excel_Writer(\'dane.xls\');

Zwróćmy uwagę na parametr konstruktora.
Jest to nazwa pliku, który zostanie utworzony.

Metodą {stala}addWorksheet(){/stala} dodajemy nowy
arkusz:

$sheet = $xls->addWorksheet(\'Samochody\');

Następnie tworzymy nowy obiekt $format.
Posłuży on do formatowania komórek arkusza.
Ustalamy format wytłuszczenie oraz wielkość
czcionki 12 punktów:

$format = $xls->addFormat();
$format->setBold();
$format->setSize(\'12\');

Teraz wprowadzamy do arkusza dane. Najpierw
wiersz nagłówkowy, formatowany przy
użyciu ustalonego stylu zapisanego w obiekcie
$format:

$sheet->write(0, 0, \'Marka\', $format);
$sheet->write(0, 1, \'Model\', $format);
Następnie wprowadzamy dane dwóch samochodów:
$sheet->write(1, 0, \'Renault\');
$sheet->write(1, 1, \'Clio\');
$sheet->write(2, 0, \'Citroen\'
$sheet->write(2, 1, \'Xsara\');

i ustalamy szerokości kolumn:

//szerokość kolumny 0
$sheet->setColumn(0, 0, 20);
//szerokość kolumn 1, 2, 3
$sheet->setColumn(1, 3, 40);

Na zakończenie zapisujemy wygenerowany
dokument:

$xls->close();

Wysyłanie arkusza do przeglądarki
Stosując klasę {stala}Spreadsheet_Excel_Writer{/stala} możemy
również wygenerowany dokument wysłać
do przeglądarki, bez konieczności zapisywania
na dysku. W tym celu wystarczy wywołać
konstruktor bez parametrów:

$xls = new Spreadsheet_Excel_Writer;

oraz metodę {stala}send(){/stala}:

$xls->send(\'auta.xls\');

Konwersja pliku tekstowego do formatu XLS

Dysponując klasą do generowania plików XLS,
możemy w prosty sposób przekształcić plik
tekstowy do formatu Excela. W tym celu najpierw
należy pokroić plik tekstowy, by otrzymać
liczbę wierszy ({stala}$wiersze{/stala}), liczbę kolumn
({stala}$kolumny{/stala}) oraz dwuwymiarową tablicę danych
({stala}$dane{/stala}):

$tmp = trim(file_get_contents(\'input.txt\'));
list($wiersze, $kolumny, $dane) = string2VArray($tmp, \',\');

Następnie tworzymy nowy obiekt {stala}$xls{/stala},
metodą {stala}addWorksheet(){/stala} dodajemy arkusz,
po czym wprowadzamy do niego dane z tablicy
{stala}$dane{/stala}
:

$xls = new Spreadsheet_Excel_Writer(\'output.xls\');
$sheet = $xls->addWorksheet(\'Lorem ipsum\');
for ($i = 0; $i < $wiersze; $i++) {
  for ($j = 0; $j < $kolumny; $j++) {
    $sheet->write($i, $j, $dane[$j][$i]);
  }
}

Konwersja pliku XLS do formatu tekstowego

Konwersję w odwrotną stronę, czyli zamianę
pliku XLS na plik tekstowy, wykonujemy przy
użyciu klasy {stala}Spreadsheet_Excel_Reader{/stala}. Ustalamy
nazwę pliku oraz indeks arkusza:

$nazwapliku = \'input.xls\';
$numer_arkusza = 0;

po czym tworzymy nowy obiekt {stala}$xls{/stala} i odczytujemy
podany plik:

require_once \'Excel/reader.php\';
$xls = new Spreadsheet_Excel_Reader();
$xls->setOutputEncoding(\'cp1250\');
$xls->read($nazwapliku);

W podwójnej pętli for przetwarzamy cały
arkusz. Znalezione dane dopisujemy do zmiennej

$wyn:
$wyn = \'\';
for ($i = 1; $i <= $xls->sheets [$numer_arkusza][\'numRows\']; $i++) {
  for ($j = 1; $j <= $xls->sheets [$numer_arkusza][\'numCols\']; $j++) {
    $wyn .= $xls->sheets[$numer_arkusza] [\'cells\'][$i][$j] . \",\";
  }
  $wyn = trim($wyn, \",\");
  $wyn .= \"\r\n\";
}

Na zakończenie zapisujemy zmienną {stala}$wyn{/stala}
do pliku:

$wyn = trim($wyn, \",\r\n\");
file_put_contents(\'output.txt\', $wyn);

Do czego mogą przydać się arkusze kalkulacyjne
webmasterowi? Na przykład do wypełniania
bazy danych rekordami. Zadanie takie
omówię na przykładzie aplikacji Autokomis.

Technika wprowadzania danych do bazy
przy użyciu arkusza kalkulacyjnego

Formularze są powszechnie stosowaną techniką
wypełniania aplikacji danymi. Dzięki zabezpieczeniom
zapewniającym poprawność
danych oraz autoryzacji użytkowników witryna
może być wzbogacana o nowe treści
przez osoby nie znające się na webmasteringu.

Jednak logowanie, wędrówka po formularzach
oraz edycja danych w polach tekstowych
formularza jest wolniejsza niż edycja
dokumentów w edytorach tekstów czy
w arkuszach kalkulacyjnych. Jeśli wydrukowany
dokument (np. listę ogłoszeń sprzedaży
samochodów) zechcemy wprowadzić do bazy
danych formularzami, to zazwyczaj będzie to
trwało dłużej, niż wprowadzenie tej samej ilości
danych do arkusza kalkulacyjnego.

Zatem w celu przyspieszenia całego procesu
można zastosować następującą procedurę:

• osoba odpowiedzialna za przepisywanie
  danych pracuje w arkuszu kalkulacyjnym,
  przygotowując dokument o ściśle określonym
  formacie (praca taka może zostać
  wykonana offline, nie wymaga żadnych
  uprawnień),
• gotowy plik XLS trafia do administratora
  aplikacji; po sprawdzeniu poprawności
  dokument jest importowany do bazy danych.

Format danych

Wykorzystanie arkuszy kalkulacyjnych do
wprowadzania danych rozpoczynamy od ustalenia
formatu. Najlepiej przygotować szablon
dokumentu, który zawiera opisane kolumny
przeznaczone na dane. Osoba odpowiedzialna
za wprowadzanie danych nie może wprowadzać
żadnych modyfikacji w strukturze.
Opisywana aplikacja stanowi katalog używanych
samochodów. Jeden wiersz arkusza
kalkulacyjnego opisuje jeden samochód. Kolejne
kolumny to: marka, model, rocznik, cena,
pojemność, przebieg, kolor, typ, paliwo, wyposażenie,
uwagi.

Fragment przykładowego arkusza o tym
formacie jest widoczny na rys. 2.
W zależności od konkretnych realiów możemy
pozwolić na zostawianie niektórych kolumn
niewypełnionych. Omawiany arkusz dopuszcza
pozostawienie pustych kolumn kolor,
typ oraz paliwo. Ponadto czasami pojedyncza
komórka może zawierać szereg informacji.
W omawianym przykładzie taka sytuacja
ma miejsce w kolumnie wyposażenie. Komórki
tej kolumny składają się ze skrótów oddzielonych
przecinkami, np.:

ABS, ASR, PP, RM, AL, 4X4
Liczba skrótów może być dowolna. Wszystkie
stosowane skróty są opisane w drugiej zakładce
arkusza.

Baza danych i aktywne rekordy

Opisywany przykład wykorzystuje bazę danych
autokomis przedstawioną na rys. 3. Baza danych
zawiera cztery tabele:

• marka – marki samochodów (np. Fiat, Ford),
• model – modele samochodów (np. Punto, Uno, Escort),
• wyposażenie – elementy wyposażenia (np. ABS, PP, K),
• auto – oferty sprzedaży (np. Fiat Punto, 1997, 3400 zł).

Tabele marka i model są połączone relacją
1:n. Ponadto tabele model i auto oraz auto
i wyposażenie są połączone relacjami n:m (tabele
haszujące to {stala}model_has_auto{/stala} oraz {stala}auto_has_wyposazenie{/stala}).

Po przygotowaniu modelu bazy należy
aplikacją Propel wygenerować klasy dostępu
do bazy danych oraz utworzyć pustą
bazę danych.

Wstawianie danych

Gdy gotowa jest pusta baza danych, klasy dostępu
oraz plik z danymi, przechodzimy do wstawiania danych z pliku XLS do bazy. Oczywiście
musimy przeczytać zawartość arkusza
kalkulacyjnego. Wykorzystamy do tego klasę
{stala}Spreadsheet_Excel_Reader{/stala}.

Ponieważ program Excel stosuje kodowanie
windows-1250, więc przestawiamy tryb komunikacji
z bazą danych:

$con = Propel::getConnection(\'autokomis\');
$sql = \'SET NAMES cp1250;\';
$stmt = $con->createStatement();
$rs = $stmt->executeQuery($sql);

Plik {stala}auta.xls{/stala} zawiera dwa arkusze. Pierwszy
z nich nazywa się samochody, drugi – wyposażenie.
Arkusz wyposażenie zawiera wyjaśnienia
wszystkich skrótów użytych przy opisie
aut. Najpierw zajmiemy się zakładką wyposażenie.
Odczytujemy plik:

$xls = new Spreadsheet_Excel_Reader();
$xls->read(\'dane/auta.xls\');

po czym zawartość drugiego arkusza wstawiamy
do bazy wykorzystując klasę Wyposazenie:

/*
* WYPOSAŻENIE
*/
for ($i = 1; $i <= $xls->sheets[1] [\'numRows\']; $i++) {
  $skrot = trim($xls->sheets[1][\'cells\'][$i][1]);
  $opis = trim($xls->sheets[1][\'cells\'][$i][2]);
  $wyposazenie = new Wyposazenie;
  $wyposazenie->setSkrot($skrot);
  $wyposazenie->setOpis($opis);
  $wyposazenie->save();
}

Następnie przechodzimy do arkusza zawierającego
samochody. Przetwarzamy arkusz
wiersz po wierszu (każdy wiersz opisuje
jedną ofertę sprzedaży), pomijając pierwszy
wiersz, gdyż zawiera on nagłówek:

/*
* AUTA
*/
for ($i = 2; $i <= $xls->sheets[0][\'numRows\']; $i++) {
  $marka = ucwords(trim($xls->sheets[0][\'cells\'][$i][1]));
  $model = ucwords(trim($xls->sheets[0][\'cells\'][$i][2]));
  $rocznik = trim($xls->sheets[0][\'cells\'][$i][3]);
  $cena = trim($xls->sheets[0][\'cells\'][$i][4]);
  $pojemnosc = trim($xls->sheets[0][\'cells\'][$i][5]);
  $przebieg = trim($xls->sheets[0][\'cells\'][$i][6]);
...
}

Po ustaleniu poszczególnych danych (zmienne:
$marka, $model, $rocznik, …) wypełniamy
kolejne tabele bazy. Rozpoczynamy od marki samochodu.
Sprawdzamy, czy dana marka jest już
w bazie, korzystając z klasy MarkaPeer. Jeśli takiej
w bazie nie ma, to wstawiamy stosowny rekord,
wykorzystując do tego klasę Marka:

/*
* MARKA
*/
$c = new Criteria;
$c->add(MarkaPeer::NAZWA, $marka);
$marki = MarkaPeer::doSelect($c);
if (count($marki) == 0) {
  $objMarka = new Marka;
  $objMarka->setNazwa($marka);
  $objMarka->save();
} elseif (count($marki) == 1) {
  $objMarka = $marki[0];
} else {
  die(\'-----error #1-----\');
}

Podobnie postępujemy z modelem, lecz
tym razem – z racji na relację 1:n łączącą
marki i modele – wykorzystujemy utworzony
przed chwilą obiekt {stala}$objMarka{/stala}:

/*
* MODEL
*/
$c = new Criteria;
$c->add(ModelPeer::NAZWA, $model);
$modele = ModelPeer::doSelect($c);
if (count($modele) == 0) {
  $objModel = new Model;
  $objModel->setNazwa($model);
  $objModel->setMarka($objMarka);
  $objModel->save();
} elseif (count($modele) == 1) {
  $objModel = $modele[0];
} else {
  die(\'-----error #2-----\');
}

Następnie do bazy danych dodajemy auto:

/*
* AUTO
*/
$objAuto = new Auto;
$objAuto->setRocznik($rocznik);
$objAuto->setCena($cena);
$objAuto->setPojemnosc($pojemnosc);
$objAuto->setPrzebieg($przebieg);
$objAuto->setKolor($kolor);
$objAuto->setTyp($typ);
$objAuto->setPaliwo($paliwo);
$objAuto->setUwagi($uwagi);
$objAuto->save();

oraz relację n:m łączącą auta i modele:

$mha = new ModelHasAuto;
$mha->setModel($objModel);
$mha->setAuto($objAuto);
$mha->save();

Na zakończenie wypełniamy dane o wyposażeniu
samochodu. Kroimy zmienną {stala}$wyposazenie{/stala}
znakiem przecinka. Elementy wyposażenia
przetwarzamy w pętli foreach.

Sprawdzamy
czy podany element wyposażenia jest
już w bazie (klasa {stala}WyposazeniePeer{/stala}), a jeśli
nie – wstawiamy nowy rekord. Proces kończymy
dodając rekord w tabeli {stala}auto_has_wyposazenie{/stala}
(klasa {stala}AutoHasWyposazenie{/stala}):

/*
* WYPOSAŻENIE
*/
$el = explode(\',\', $wyposazenie);
foreach ($el as $elementwyposazenia) {
  $tmp = trim($elementwyposazenia);
  $c = new Criteria;
  $c->add( WyposazeniePeer::SKROT, $tmp);
  $wyps = WyposazeniePeer::doSelect($c);
  if (count($wyps) == 0) {
    $objWyp = new Wyposazenie;
    $objWyp->setSkrot($tmp);
    $objWyp->save();
  } elseif (count($wyps) == 1) {
    $objWyp = $wyps[0];
  } else {
    die(\'-----error #3-----\');
  }
  $ahw = new AutoHasWyposazenie;
  $ahw->setAuto($objAuto);
  $ahw->setWyposazenie($objWyp);
  $ahw->save();
}

Aplikacja

Zawartość bazy danej jest udostępniana interfejsem
WWW, który zawiera następujące opcje:

• informacyjną stronę główną,
• listę wszystkich marek,
• listę wszystkich ofert,
• legendę skrótów użytych w opisie wyposażenia

Na kolejnym poziomie dostępne są przefiltrowane
dane:

• lista wszystkich modeli wybranej marki,
• lista wszystkich ofert dotyczących konkretnego
  modelu,
• oraz szczegółowe dane wybranej oferty.

Generowanie danych

Przykładowy plik z danymi zawiera kilkaset rekordów.
Nie miałem cierpliwości, by przepisać
tyle ogłoszeń z czasopism motoryzacyjnych.
W zamian wolałem przygotować skrypt, który
generuje arkusz kalkulacyjny z dowolną liczbą
losowych ogłoszeń. Oczywiście skrypt wykorzystuje
klasę {stala}Spreadsheet_Excel_Writer{/stala}.

Idea skryptu jest bardzo prosta. Z plików
tekstowych {stala}kolory.txt{/stala}, {stala}marki.txt{/stala}, {stala}paliwo.txt{/stala}, {stala}typ.txt{/stala}, {stala}uwagi.txt{/stala}, {stala}wyposazenie.txt{/stala} wczytujemy
wszystkie dane. Następnie w pętli tworzymy
losową kombinację wartości, a otrzymany
rekord zapisujemy jako kolejny wiersz arkusza
kalkulacyjnego.

Dane generowane w ten sposób zawierają
mnóstwo idiotyzmów (np. Trabant z silnikiem
3.8 TD, rocznik 2005 w cenie 89000 zł),
pozwalają jednak na sprawdzenie przebiegu
operacji wstawiania danych do bazy.