Export do XLS v PHP

Export z MySQL do XLS ověřeně funguje v PEAR knihovně [1] , kterou nainstalujeme takto:

pear download Spreadsheet_Excel_Writer
pear download OLE
pear install channel://pear.php.net/OLE-1.0.0RC1
pear install channel://pear.php.net/Spreadsheet_Excel_Writer-0.9.1

Protože ale tato verze knihovny má v sobě bug, který mrví některé řádky s UTF-8 češtinou, musíme ještě stáhnout a nainstalit patch:

cd /usr/share/php/Spreadsheet/Excel/Writer
wget http://bugs.gentoo.org/attachment.cgi?id=136412
mv attachment.cgi\?id\=136412 Workbook.php

A pak už můžeme vychutnávat krásy XLS exportu:

   <?php
   require_once 'Spreadsheet/Excel/Writer.php';    
   // Creating a workbook
   $workbook = new Spreadsheet_Excel_Writer();    
   $workbook->setVersion(8);         
   $worksheet =& $workbook->addWorksheet('My first worksheet');
   $worksheet->setInputEncoding("UTF-8");
   $format_bold =& $workbook->addFormat();
   $format_bold->setBold();    
   $link = mysql_connect('localhost', 'root', 'password') 
           or die('Could not connect: ' . mysql_error());
   mysql_select_db('my_database') or die('Could not select database');
   mysql_query("SET CHARACTER SET 'utf8'") or die('Could not set charset');
   mysql_query("SET collation_connection=utf8_czech_ci") or die('Could not set collation');
   $result = mysql_query('SELECT * FROM aaajeto') or die('Query failed: ' . mysql_error());
   $resultCnt=mysql_num_rows($result);
   $first=true;
   $row=$col=0;
   while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
       $colCnt=count($line);
       $col=0;
       foreach ($line as $key => $value) {
           $valLen=strlen($value);
           if ($first) {
               $max[$key]=$valLen; // inicializace hodnoty max[neco]
               $worksheet->write(0, $col, $key, $format_bold); // výpis hlavičky            
               if ($col>=$colCnt-1) $first=false;
           }
           else {
               if ($max[$key]<$valLen) $max[$key]=$valLen;
               $worksheet->write($row, $col, $value); // výpis hlavičky                        
           }
           if ($row>=$resultCnt-1) 
               $worksheet->setColumn($col,$col,$max[$key]);        
           $col++;
       }
       $row++;
   }
   mysql_free_result($result);
   mysql_close($link); 
   $workbook->send('databaza-mysql.xls');
   $workbook->close();
   ?>