Export excel from postgres query in php

Navigation
0

I need to export the result of a query that I'm printing on a table in php, I currently have two tables (queries) in my page that are shown at par but I need export to an excel file the result of a.

this is my query 

<form type="submit" method="post" role="form- 
horizontal" id="form-fil"> 
<div class="form-group"> 

<div class="col-sm-2" id="tittle"> 

<h1>Formulario</h1> 

<select class="form-control" 
name="selRez" id="selRez" > 

<?php 


$query="select * from tabla1 where clave = $var;"; 

$result = pg_query($query) or die('Query  failed: ' . pg_last_error()); 

$rows = pg_num_rows ($result); 

$i = pg_num_fields($result); 

//Construyo mi tabla    

while ($line = pg_fetch_array($result)){ 

if ($line[0] <> "") { 

echo "<option value='$line[0]' >$line[1] 
</option>"; 

}else{ 

echo"<option >&nbsp;</option>"; 

} 

} 

?> 

</select> 

</div> 

</div>  

</form>

I would like to add the functionality that I tell you in this part of the code 

<td aling= "right" ><a href="excelTabla.php" target"_blank" style="display:inline"><img src="./img/excelIcon.png"</a></td>

I hope you can help me, I thank you for beforehand

    
asked by IndiraRivas 02.03.2018 в 21:36
source

2 answers

0

Maybe someone serves, here I leave a code that serves to export our query to excel with the postgres database engine 

<?php
include_once "miBDConection.php";
session_start();
//mi query
    $select="select * from table;";

    $export = pg_query ( $select ) or die ( "Sql error : " . pg_error( ) );
    $fields = pg_num_fields ( $export );

    for ( $i = 0; $i < $fields; $i++ )
    {
        $header .= pg_field_name( $export , $i ) . "\t";
    }

    while( $row = pg_fetch_row( $export ) )
    {
        $line = '';
        foreach( $row as $value )
        {
            if ( ( !isset( $value ) ) || ( $value == "" ) )
            {
                $value = "\t";
            }
            else
            {
                $value = str_replace( '"' , '""' , $value );
                $value = '"' . $value . '"' . "\t";
            }
            $line .= $value;
        }
        $data .= trim( $line ) . "\n" ;
    }
    $data = str_replace( "\r" , "" , $data );



    if ( $data == "" )
    {
        $data = "\n(0) Records Found!\n";
    }
    $filename = "reporte_rezagoZAP-" . date('Ymd') . ".xls";
    header("Content-type: application/octet-stream");
    header("Content-Disposition: attachment; filename=\"$filename\"");
    header("Pragma: no-cache");
    header("Expires: 0");
    print "$header\n$data";
?>

I hope you serve, greetings

    
answered by 22.03.2018 / 01:27
source
0

You can use the phpExcel library in order to generate interaction with excel, either to generate excel files as I enunciate below: 

/*
aquí generas tu consulta y almacenas en un dataSet con el fin de generar una variable php que almacene lo que requieres... puedes iterar con un array que 'introducir el código aquí'armes...
*/

    require_once('../_lib/libraries/sys/PHPExcel/PHPExcel.php');

    // Crea un nuevo objeto PHPExcel
    $objPHPExcel = new PHPExcel();

    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', {dsExample[0][0]});    
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B1', 'column B');
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C1', 'column C');
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D1', 'Column D');    
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E1', 'Column E');
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F1', 'Column F');
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G1', 'Column G');
    ob_end_clean();

    header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
    header("Cache-Control: no-store, no-cache, must-revalidate");
    header("Cache-Control: post-check=0, pre-check=0", false);
    header("Pragma: no-cache");
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="test.xlsx"');

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    ob_end_clean();

    $objWriter->save('php://output');
    
answered by 02.03.2018 в 22:16
Polynomials in visusla basic ERR_UNSAFE_REDIRECT when opening local file