Create an XLS file from the MySQL database using a PHP script

advertisements

I want to generate XLS files with a PHP script from a MySQL database. The big problem here is that when I open the new exported .xls file, I see the values OK (i.e. it is correctly formatted) but the colour of the field changed to white. However, I need the colour unchanged, as is by default in Excel.

Here is the PHP script that I use to extract data from my database.

 <?php
            include 'connect.php';
            $result = mysql_query('SELECT * FROM projects2');

            ?>

        <center><h1>Lista valorilor din tabela</h1>
        <h2><a href="lec_datepm.php?exporta_lista_clienti=1" title="Exporta lista clienti in Excell" target="_blank">Exporta lista Clienti</a></h2></center>

        <?php
            include_once 'tabel_clientipm.php';
        ?>

The PHP file used to generate the XLS file is:

<?php
    include 'connect.php';
    $result = mysql_query('SELECT * FROM projects2');

    if (isset($_GET['exporta_lista_clienti'])) {
    $filename = 'raportnou.xls';

    header("Content-type: application/ms-excel");
    header("Content-Disposition: attachment; filename=$filename");

    include_once 'tabel_clientipm.php';

    exit();
    }
?>

I added the tabel_clientipm.php:

<center>
<table border="1">
    <tr>
        <th>surname</th>
        <th>name</th>
        <th>age</th>

    </tr>
    <?php
        while ($client = mysql_fetch_assoc($result)) {
    ?>
    <tr>
        <td><?php echo $client['surname'];?></td>
        <td><?php echo $client['name'];?></td>
        <td><?php echo $client['age'];?></td>

    </tr>
    <?php
        }
    ?>
</table>
</center>


If you want to create real .xls file NOT csv or html hidden in .xls extension use PHPExcel or

It supports the following formats.

  • BIFF 8 (.xls) Excel 95 and above
  • Office Open XML (.xlsx) Excel 2007 and above

If PHPExcel is slow for you check these alternatives (provided by an author of PHPExcel. All of them are faster than PHPExcel