My PHP script for exporting MySQL schemas to .xls dumps HTML artifacts

Question

As the title states - I have written a small script which would export an SQL generated table with data onto an Excel readable .xls file. However, when I export the data, for some reason, the footer and header also gets dumped into the .xls file with images and all.

Here is my script for exporting MySQL data:

if(isset($_POST['download']))
    { 
        $q = "SELECT * FROM completed_tasks ORDER BY PRIORITY DESC";
        $result = mysqli_query($conn, $q);

        if(mysqli_num_rows($result) > 0)
        {
            $output .= "<table class='sqltable'>
                        <tr class='sqltable'> 
                            <th class='sqltable'>From:</th>
                            <th class='sqltable'>Department:</th>
                            <th class='sqltable'>Name of the task:</th>                                                                             
                            <th class='sqltable'>Description:</th>          
                            <th class='sqltable'>Priority:</th>
                            <th class='sqltable'>Time elapsed:</th>      
                            <th class='sqltable'>Completed by:</th>                                       
                            <th class='sqltable'>Notes:</th>        
                        </tr>";

            // output data of each row
            while($row = $result->fetch_assoc()) 
            {
                $output .= "<tr class='sqltable'>"; 
                $output .=   "<td class='sqlcell'>".$row["FROM"]."</td>"; 
                $output .=   "<td class='sqlcell'>".$row["DEPT"]." </td>" 
                $output .=   "<td class='sqlcell'>".$row["TASK_NAME"]."</td>";
                $output .=   "<td class='sqlcell'>".$row["TASK"]."</td>";
                $output .=   "<td class='sqlcell'>". $row["PRIORITY"]."</td>";  
                $output .=   "<td class='sqlcell'>".$row["TIME_COMPLETED"]."</td>";
                $output .=   "<td class='sqlcell'>".$row["COMPLETED_BY"]."</td>";                           
                $output .=   "<td class='sqlcell'>".$row["TASK_RESOLVE"]."</td>";
                $output .=   "</tr>";
            } 
            $output .= "</table>"; 

            header('Content-Type: application/xls');
            header('Content-Disposition: attachment; filename=download.xls');
            echo $output;
        }

EDIT: I just realised that .xls file I produce is also looking for the .css style configuration.

Do you have standard code which outputs the headers/footers with your calls? — Nigel Ren, Mar 13 '19 at 13:06
Well no but the rest of the code is the typical connection MacGuffins like `session_start();` , `$servername = "127.0.0.1"; $username = "root"; $password = "";//"; $db = "mydb"; $table = "users"; ` and so on. — Jeanie Miflin, Mar 13 '19 at 13:14
Trying using [ob_end_clean](http://php.net/manual/en/function.ob-end-clean.php) to erase the output buffer and stop buffering right before your header calls and exit right after you echo the output. — Dave, Mar 13 '19 at 13:19
@Dave This is the very first thing I tried and it did not work. It is extremely odd.. — Jeanie Miflin, Mar 13 '19 at 14:25
If you don't already turn on error reporting and see if you are getting any errors. `ini_set('display_errors', 1); ini_set('display_startup_errors', 1); error_reporting(E_ALL);` — Dave, Mar 13 '19 at 14:27
I have `error_reporting` on and I do not get any errors whatsoever. — Jeanie Miflin, Mar 14 '19 at 06:19
I just realised that .xls file I produce is also looking for the .css style configuration. Could that mean something? — Jeanie Miflin, Mar 14 '19 at 06:24

Pascal Tovohery · Answer 1 · 2019-03-13T13:25:00.457

Use PHPSpreadsheet for build excell in php

PHPSpreadSheet is a library for Excell An open source project Build support Microsoft Excell 2007

Here is the link PHPSpreadSheet Installed from composer with on command line

composer require phpoffice/phpexcel

And your code should be like

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xls;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$range = range(65,90);

if(mysqli_num_rows($result) > 0)
{
   $sheet->setCellValue('A1', 'From');
   $sheet->setCellValue('B1', 'Department');
   $sheet->setCellValue('C1', 'Name of the task');
   $sheet->setCellValue('D1', 'Description');
   $sheet->setCellValue('E1', 'Priority:');
   $sheet->setCellValue('F1', 'Time elapsed');
   $sheet->setCellValue('G1', 'Notes:');

   // output data of each row
   while($row = $result->fetch_assoc()) 
   {
     // Do somme logic to store data to excell
   } 

   header('Content-Type: application/xls');
   header('Content-Disposition: attachment; filename=download.xls');
   $writer = new Xls($spreadsheet);
   $writer->save('php://stdout');

}

Hello @Pascal, is there really no way to do it without addons and what not? I kind of want to make it as vanilla as possible without adding any additional flavours to my server configuration. — Jeanie Miflin, Mar 14 '19 at 05:54
@JeanieMiflin Yes there's a way to do that but it's so hard, because you need to know to deal with xls file, how to create the file with the correct Mime-type. By the way i suggest you to see this post https://stackoverflow.com/questions/54748518/how-to-export-this-table-of-data-to-excel-file-from-php/54749180#54749180 — Pascal Tovohery, Mar 14 '19 at 13:13

My PHP script for exporting MySQL schemas to .xls dumps HTML artifacts

1 Answers1