Exchange export version of Excel with PHP/Mysql

Question

Guys, I have a code that exports my table from the database perfectly, but it exports it in excel 97 (2003), does anyone know any way to export this table in another version of excel and the file in xlsx instead of xls? NOTE: I already tried to change the file extension to xlsx and gives error when opening the file.

Code:

<?php
include('conexao.php');
error_reporting(0);

 $tabela = '<table border="1">';
 $tabela .= '<tr>';
 $tabela .= '<td><b>Codigo Prova</b></td>';
 $tabela .= '<td><b>Nome</b></td>';
 $tabela .= '<td><b>Supervisor</b></td>';
 $tabela .= '<td><b>Local</b></td>';
 $tabela .= '<td><b>Data</b></td>';
 $tabela .= '<td><b>Questao 1</b></td>';
 $tabela .= '<td><b>Questao 2</b></td>';
 $tabela .= '<td><b>Questao 3</b></td>';
 $tabela .= '<td><b>Questao 4</b></td>';
 $tabela .= '<td><b>Questao 5</b></td>';
 $tabela .= '<td><b>Questao 6</b></td>';
 $tabela .= '<td><b>Questao 7</b></td>';
 $tabela .= '<td><b>Questao 8</b></td>';
 $tabela .= '<td><b>Questao 9</b></td>';
 $tabela .= '<td><b>Questao 10</b></td>';
 $tabela .= '<td><b>Aprovado ou Reprovado</b></td>';
 $tabela .= '<td><b>Prova</b></td>';
 $tabela .= '<td><b>Numero Acertos</b></td>';
 $tabela .= '<td><b>Numero Erros</b></td>';
 $tabela .= '</tr>';

$result                 = "SELECT * FROM provas";
$result_busca           = mysqli_query($conn, $result);
$contar                 = mysqli_num_rows($row_busca);

while($row_busca = mysqli_fetch_array($result_busca)){
    $tabela .= '<tr>';
    $tabela .= '<td>'.$row_busca['cd_prova'].'</td>';
    $tabela .= '<td>'.$row_busca['nome'].'</td>';
    $tabela .= '<td>'.$row_busca['supervisor'].'</td>';
    $tabela .= '<td>'.$row_busca['local'].'</td>';
    $tabela .= '<td>'.$row_busca['data'].'</td>';
    $tabela .= '<td>'.$row_busca['questao1'].'</td>';
    $tabela .= '<td>'.$row_busca['questao2'].'</td>';
    $tabela .= '<td>'.$row_busca['questao3'].'</td>';
    $tabela .= '<td>'.$row_busca['questao4'].'</td>';
    $tabela .= '<td>'.$row_busca['questao5'].'</td>';
    $tabela .= '<td>'.$row_busca['questao6'].'</td>';
    $tabela .= '<td>'.$row_busca['questao7'].'</td>';
    $tabela .= '<td>'.$row_busca['questao8'].'</td>';
    $tabela .= '<td>'.$row_busca['questao9'].'</td>';
    $tabela .= '<td>'.$row_busca['questao10'].'</td>';
    $tabela .= '<td>'.$row_busca['status_aluno'].'</td>';
    $tabela .= '<td>'.$row_busca['tipo_prova'].'</td>';
    $tabela .= '<td>'.$row_busca['numero_acertos'].'</td>';
    $tabela .= '<td>'.$row_busca['numero_erros'].'</td>';
    $tabela .= '</tr>';
}
$tabela .= '</table>';

$arquivo = 'prova.xls';

header ('Cache-Control: no-cache, must-revalidate');
header ('Pragma: no-cache');
header('Content-Type: application/x-msexcel');
header ("Content-Disposition: attachment; filename=\"{$arquivo}\"");
echo $tabela;
?>

Answer 1

This is not EXCEL, is an HTML that by coincidence has a reasonable support in Office programs like Msexcel, but it is not actually excel, nor XLS nor XLSX, it is only HTML, so it has no version.

No use for an extension and to believe that the content will change, the extension is only a facilitator for the operating systems, as another example would not be useful to rename a PNG to JPG will not make the image in the format "JPEG" (I refer to the content).

If you are using Composer (only "if" you are using), you can try Composer require phpoffice/phpspreadsheet

Install in the project folder that ALREADY USE Composer (if you are actually using) this:

 composer require phpoffice/phpspreadsheet

Then your document should stay + or - like this:

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

$sheet->setCellValue('A1', 'Codigo Prova');
$sheet->setCellValue('B1', 'Nome');

...

$i = 1;

while($row_busca = mysqli_fetch_array($result_busca)){
    ++$i; //Incrementa a variavel para ter A2, A3, A4, A5, etc

    $sheet->setCellValue('A' . $i, $row_busca['cd_prova']);
    $sheet->setCellValue('B' . $i, $row_busca['nome']);

    ... Demais células aqui ...
}

$writer = new Xlsx($spreadsheet);

$arquivo = 'prova.xlsx';

header ('Cache-Control: no-cache, must-revalidate');
header ('Pragma: no-cache');
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header ("Content-Disposition: attachment; filename=\"{$arquivo}\"");

//Envia para o OUTPUT do PHP e posteriormente resolvido pelo SAPI
$writer->save("php://output");

It is not easy to create an XLSX file, so apart from this lib there are even other solutions, but I do not know if they are really reliable for use, nor can I quote because I have not used other.

---

Extra, do not use error_reporting(0); incorrectly

It’s not that you shouldn’t wear error_reporting, the problem is using thinking it serves to just disappear the errors of the output, but this is a problem, actually sitting down 0 it does much more than that, the ideal would be to set in php.ini of the hospesage display_errors=Off, but this should already be set, and if it is local it is preferred that the errors are always connected, this is because you should always correct all before sending to production, I recommend you read:

• Why use error_reporting with display_errors and display_startup_errors?