Create XLS file from MySQL database using PHP script

Question

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 I may ask, where is your table generation code – Satya Dec 21 '12 at 03:18 — Satya, Dec 21 '12 at 03:18
Show code of `tabel_clientipm.php` – Shiplu Mokaddim Dec 21 '12 at 03:23 — Shiplu Mokaddim, Dec 21 '12 at 03:23

score 1 · Accepted Answer · edited May 23 '17 at 12:28

1

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

edited May 23 '17 at 12:28

Community

1
1

answered Dec 21 '12 at 03:23

Shiplu Mokaddim

56,364
17
141
187

Be aware though.. PHPExcel can be horribly slow! – Lock Dec 21 '12 at 03:48
Office Open XML format shouldn't be slow. I have added a link for you. – Shiplu Mokaddim Dec 21 '12 at 03:58

score 0 · Answer 2 · answered Dec 21 '12 at 03:19

0

If you export as a csv, you can probably import the data as colorless.

answered Dec 21 '12 at 03:19

Lighthart

3,648
6
28
47

Yes, but If i choose to export as .CSV will create a good file, without white(as is by default - transparent) but not correctly formatted like the xls file is! – Cristian Stan Dec 21 '12 at 03:21

Create XLS file from MySQL database using PHP script

2 Answers2