Generate Excel with php query

Question

I need some help. I have a query in php and Mysql and would like to add to this query the option to export the data also to excel. It is possible to do this with php?

<div id="tabs">
<ul>
    <li><a href="#tabs-1">Informação</a></li>
<li><a href="#tabs-4">Trabalhadores</a></li>
    <li><a href="#tabs-2">Credenciais</a></li>
    <li><a href="#tabs-3">Equipamentos</a></li>
</ul>
<?php

 include("conectar.php");

$id = $_GET['id'];
$sql = "select * from TABELAS   Where CAMPOS and tb_trabalhador.id = $id order by tb_trabalhador.id asc ";
$qr = mysql_query($sql) or die(mysql_error());
while($exibe = mysql_fetch_array($qr)){
//Todos os dados a serem mostrado

Answer 1

Yes, it is possible. One possibility is to create a button to export, when the user clicks on this button you will have to form your html table the same way it generates for the view but you have to configure the header to download excel.

For example:

$html = "<table>
    <tr>
        <td>Coluna 1</td>        
        <td>Coluna 2</td>        
        <td>Coluna 2</td>        
    </tr>
    <tr>
        <td>Coluna 1</td>        
        <td>Coluna 2</td>        
        <td>Coluna 2</td>        
    </tr>
    <tr>
        <td>Coluna 1</td>        
        <td>Coluna 2</td>        
        <td>Coluna 2</td>        
    </tr>
</table>";

// Configurações header para forçar o download
header ("Expires: Mon, 26 Jul 1997 05:00: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=\"nome_arquivo.xls\"" );
header ("Content-Description: PHP Generated Data" );

echo $html;

Answer 2

It is possible, one of the alternatives is to use lib phpexcel for handling excel files

include '../../includes/excel/PHPExcel.php';
$objPHPExcel = new PHPExcel();

$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'Nome');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B1', 'Idade');

$linha=2;
foreach ($dadosDoBanco as $item){
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$linha, $item['nome']);
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$linha, $item['idade']);
    $linha++;
}

//formata o cabeçalho
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="lista.xls"');
header('Cache-Control: max-age=0');


$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');

It is also possible to assign values to cells using the method setCellValueByColumnAndRow('coluna', 'linha', 'valor'), suitable for working with variable number of columns/values.

Answer 3

There are some ways

1. Force header for browser save with extension. Although it is a html with another extension excel accepts to load, in the form that this, without formatting if using external css / images without full address of the site. In this procedure he identifies the columns of tables of HTML like cells.

   <?php header ("Content-type: application/x-msexcel"); header ("Content-Disposition: attachment; filename=\"nome_arquivo.xls\"" ); ?>

2. Use a Openxml, the one generated more easily by PHP Excel. The difference is that the Openxml replicates the correct Excel file format, both in .XLS, just as .XLSX. A file generated by excel is a Openxml generated and encrypted, as well as attached images are converted to Base64. On the website of PHP Excel when downloading already comes with several examples. just extract them and run them

Example of Openxml taken from the Microsoft website