Problem to export table in xls format with PHP

Asked

Viewed 669 times

2

Good afternoon. I have a button that generates a file . xls that should bring a spreadsheet from an SQL query... The file is generated, the problem is that a message appears in Excel saying that the format does not match. Where I am missing?

This is the button: 

<div style="float: right;"><a href="gerar_planilha.php"><button type="button">Gerar Excel</button></a> </div>

Here is my file code that generates the download: 

<body>
    <?php 

    //Definição do nome do arquivo que será exportado
    $arquivo = 'planilhactts.xls';

    //Criamos uma tabela HTML com o formato da planilha
    $html = '<meta charset="UTF-8">';
    $html .= '<table border="1">';
    $html .= '<tr>';
    $html .= '<td colspan="2">Planilha de Contato</tr>';
    $html .= '</tr>';

    $html .= '<tr>';
    $html .= '<td><b>Nome Cliente</b></td>';
    $html .= '<td><b>E-mail</b></td>';
    $html .= '</tr>';

    //Selecionar os itens da tabela
    $result_ctts = "SELECT Empresa,EMail from payment_fornecedor";
    $resultado_ctts = mysqli_query($App_conexao, $result_ctts);

    while($row_ctts = mysqli_fetch_assoc($resultado_ctts)){
        $html .= '<tr>';
        $html .= '<td>'.$row_ctts["Empresa"].'</td>';
        $html .= '<td>'.$row_ctts["EMail"].'</td>';
        $html .= '</tr>';
        ;
    }

    //Configurações header para forçar o download
    header ("Expires: Fri, 17 Apr 2020 16:50:00 GMT");
    header ("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT");
    header ("Cache-Control: no-cache, must-revalidate");
    header ("Pragma: no-cache");
    header ("Content-type: application/x-msexcel");
    header ("Content-Disposition: attachment; filename=\"{$arquivo}\"" );
    header ("Content-Description: PHP Generated Data" );

    //Envia o conteúdo do arquivo
    echo $html;
    exit; ?>
</body>

That is the mistake: inserir a descrição da imagem aqui

php html mysql xls
  • 1

    I think the mistake is here: header ("Content-type: application/x-msexcel"); Try to use: header ("Content-type: application/vnd.ms-excel"); See more here: https://answall.com/a/103363/15361

  • 1

    Hi Andrei, thanks for your attention! I tried to make the change you suggested, but the error remains the same... Thanks anyway.

  • 1

    When I had this problem I solved it using https://github.com/PHPOffice/PhpSpreadsheet

  • This should really work, the problem is that I wanted an alternative where I didn’t need to import a library, you know? Because the version of PHP that my company’s ERP uses is 5.6, and this library you mentioned is compatible with versions above PHP 7...

1 answer

1

change your Content-type header to 

header('Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

  • I tried and remains the same mistake... But still thanks for the help.

Browser other questions tagged php html mysql xls

You are not signed in. Login or sign up in order to post.