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
– Nigel Ren, Commented Mar 13, 2019 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
– Jeanie Miflin, Commented Mar 13, 2019 at 13:14
Trying using ob_end_clean to erase the output buffer and stop buffering right before your header calls and exit right after you echo the output. — Dave
– Dave, Commented Mar 13, 2019 at 13:19

Pascal Tovohery · Accepted Answer · 2019-03-13 13:25:00Z

0

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');

}

edited Mar 13, 2019 at 13:25

answered Mar 13, 2019 at 13:17

Pascal Tovohery

9969 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jeanie Miflin Over a year ago

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.

Pascal Tovohery Over a year ago

@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 stackoverflow.com/questions/54748518/…

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related