Generate Excel with query php

Question

Generate Excel with query php

Navigation

#1 by (2 votes)
#2 by (2 votes)
#3 by (1 votes)

2

I need some help. I have a query in php and mysql and I would like to add to this query the option to export the data also to excel. Is it 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

php mysql excel mysql-workbench

asked by anonymous 26.05.2014 / 18:20

3 answers

How to select element inside another with CSS? JQuery DataTables - Number of Paging Numbers

score 2 · 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 in the same way you are generating it for visualization 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;

score 2 · Answer 2

It is possible, one of the alternatives is to use phpexcel for manipulating 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 setCellValueByColumnAndRow('coluna', 'linha', 'valor') method, indicated to work with a variable number of columns / values.

score 1 · Answer 3

There are a few ways

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

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

Use an OpenXML , this is most easily generated by PHP Excel . The difference is that OpenXML replicates the correct Excel file format, both in .XLS , such as .XLSX . An excel-generated file is an OpenXML generated and encrypted, as well as images attached to it are converted to Base64 . In the PHP Excel site when downloading comes with several examples. just extract them and run them

OpenXML sample taken from Microsoft