Query result export to excel

Topics: Developer Forum, User Forum
Jan 30, 2013 at 3:01 PM

Hi All,

 

I'm using, based on example: 01simple-download-xls.php

 

This:

<?php 
require_once("includes/session.php"); 
require_once("includes/connection.php"); 
require_once("includes/functions.php");

$filter = $_SESSION['registry'];
$query = "SELECT registry.registry_num, registry.name, registry.sent_date,
        			registry.nin, rne.lgm.d_ctrl_fil, 
        			(CASE  WHEN (rne.lgm.resultado1+rne.lgm.resultado2 > 0)  THEN 1 ELSE 0 END) AS resolved, registry.ln_number,
        			registry.la_number, registry.resolved_abpsl
        			FROM registry
        			LEFT JOIN rne.lgm ON rne.lgm.nsc_f = registry.nin
        			WHERE (('' = '{$filter}') OR (CAST(registry.nin as char) = '{$filter}')
                  	OR ('' = '{$filter}') OR (registry.name LIKE '%{$filter}%')
                  	OR ('' = '{$filter}') OR (registry.registry_num LIKE '%{$filter}%')) ";
						if ($_SESSION['rne_resolved'] == 1) { $query = $query." AND (rne.lgm.resultado1 + rne.lgm.resultado2 > 0) "; }
	      				if ($_SESSION['rne_resolved'] == 0) { $query = $query." AND (rne.lgm.resultado1 + rne.lgm.resultado2 = 0) "; }
	      				if ($_SESSION['associations_resolved'] == 1) { $query = $query." AND (registry.resolved_abpsl = 1) "; }
	      				if ($_SESSION['associations_resolved'] == 0) { $query = $query." AND (registry.resolved_abpsl = 0) "; }
        			$query .=  "ORDER BY ".$_SESSION['orderby'];
        
            $result_set = mysql_query($query);
			export($result_set);

function export ($export = NULL){
	if ($export =!NULL) {
		require_once '/classes/PHPExcel.php';
		$objPHPExcel = new PHPExcel();

		$objPHPExcel->setActiveSheetIndex(0)
		            ->setCellValue('A1', 'N�Registo')
		            ->setCellValue('B1', 'Nome')
		            ->setCellValue('C1', 'Data Pedido')
		            ->setCellValue('D1', 'NIN/NSC')
					->setCellValue('E1', 'Data ROGD/LGM')
					->setCellValue('F1', 'Resolvido RNE')
		            ->setCellValue('G1', 'N� LN')
		            ->setCellValue('H1', 'N� LA')
		            ->setCellValue('I1', 'Finalizado pela Associa��o');
		            
        //FILL With DATA
		            
		         
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename="ROGDList.xls"');
		header('Cache-Control: max-age=0');

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

 

But some how, is giving me corrupt file. What am i doing wrong. I've tested the query. I don't know how to populate the excel.

 

Any help ?

Jan 30, 2013 at 4:20 PM

Now Working, but the file goes to root folder of my site.

 

using this

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save(str_replace('.php', '.xls', __FILE__));

 

How can i send it to user as download file.

Coordinator
Jan 30, 2013 at 4:29 PM

You send it to the user as in your original post.... if you're getting a corrupt file, then open it in a text editor and look for leading/trailing whitespace; BOM marker, or any obvious PHP error messages in the content of the file.

 

And I think

 if ($export =!NULL) {

should be a comparison rather than an assignment

Jan 30, 2013 at 4:51 PM
Edited Jan 30, 2013 at 4:54 PM

Hi MarkBaker,

 

Tks for the reply, i've changed the code,

In my registry_list.php page i have a link "export to excel" that points to xls.php and in xls.php file i've got this

 

<?php
require_once("includes/session.php"); 
require_once("includes/connection.php"); 
require_once("includes/functions.php");

error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');

define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '
'
); /** Include PHPExcel */ require_once '/Classes/PHPExcel.php'; // Create new PHPExcel object $objPHPExcel = new PHPExcel(); $filter = $_SESSION['registry']; $query = "SELECT registry.registry_num, registry.name, registry.sent_date, registry.nin, rne.lgm.d_ctrl_fil, (CASE WHEN (rne.lgm.resultado1+rne.lgm.resultado2 > 0) THEN 1 ELSE 0 END) AS resolved, registry.ln_number, registry.la_number, registry.resolved_abpsl FROM registry LEFT JOIN rne.lgm ON rne.lgm.nsc_f = registry.nin WHERE (('' = '{$filter}') OR (CAST(registry.nin as char) = '{$filter}') OR ('' = '{$filter}') OR (registry.name LIKE '%{$filter}%') OR ('' = '{$filter}') OR (registry.registry_num LIKE '%{$filter}%')) "; if ($_SESSION['rne_resolved'] == 1) { $query = $query." AND (rne.lgm.resultado1 + rne.lgm.resultado2 > 0) "; } if ($_SESSION['rne_resolved'] == 0) { $query = $query." AND (rne.lgm.resultado1 + rne.lgm.resultado2 = 0) "; } if ($_SESSION['associations_resolved'] == 1) { $query = $query." AND (registry.resolved_abpsl = 1) "; } if ($_SESSION['associations_resolved'] == 0) { $query = $query." AND (registry.resolved_abpsl = 0) "; } $query .= "ORDER BY ".$_SESSION['orderby']; $result_set = mysql_query($query); confirm_query($result_set); // Add some data $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A1', 'N� Registo') ->setCellValue('B1', 'Nome') ->setCellValue('C1', 'Data Pedido') ->setCellValue('D1', 'NIN/NSC') ->setCellValue('E1', 'Data ROGD/LGM') ->setCellValue('F1', 'Resolvido RNE') ->setCellValue('G1', 'N� LN') ->setCellValue('H1', 'N� LA') ->setCellValue('I1', 'Finalizado pela Associa��o'); $row = 2; while ($data = mysql_fetch_array($result_set)) { $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A' . $row, $data['registry_num']) ->setCellValue('B'. $row, $data['name']) ->setCellValue('C'. $row, $data['sent_date']) ->setCellValue('D'. $row, $data['nin']) ->setCellValue('E'. $row, $data['d_ctrl_fil']) ->setCellValue('F'. $row, getbool($data['resolved'])) ->setCellValue('G'. $row, $data['ln_number']) ->setCellValue('H'. $row, $data['la_number']) ->setCellValue('I'. $row, getbool($data['resolved_abpsl'])); $row++; } // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save(str_replace('.php', '.xlsx', __FILE__)); ?>

 

Which is working with  the data from the query. But my main goal here is:

instead of sending the file into one folder (fine is working ok) is send the file via download on browser...

i've tried

 

$objPHPExcel->setActiveSheetIndex(0);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="01simple.xlsx"');
header('Cache-Control: max-age=0');

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

 

But is giving me an error ... can not open file 01simple.xlsx because the format or extension is not valid...

 

Any idea ?

TY in advance

Coordinator
Jan 30, 2013 at 5:02 PM

if you're getting a corrupt file, then open it in a text editor and look for leading/trailing whitespace; BOM marker, or any obvious PHP error messages in the content of the file.

Jan 30, 2013 at 8:01 PM
Edited Jan 30, 2013 at 8:02 PM

4728 lines.

6d6c 2e72 656c 7350 4b05 0600 0000 000b
000b 00d1 0200 0093 2401 0000 00
 last line, is missing some bytes ?

Jan 30, 2013 at 8:30 PM
I don't understand. when i'm using this header:
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save(str_replace('.php', '.xls', __FILE__));
I can open the file with no warning or error and every thing is ok.

When i change the headers to this:
header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="ROGDList.xls"');
        header('Cache-Control: max-age=0');

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
BAM, error!
Jan 30, 2013 at 8:42 PM
This is now Working,
<?php            
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');

define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

/** Include PHPExcel */
require_once '/Classes/PHPExcel.php';

$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
                             ->setLastModifiedBy("Maarten Balliauw")
                             ->setTitle("Office 2007 XLSX Test Document")
                             ->setSubject("Office 2007 XLSX Test Document")
                             ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
                             ->setKeywords("office 2007 openxml php")
                             ->setCategory("Test result file");

$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', 'Nº Registo')
            ->setCellValue('B1', 'Nome')
            ->setCellValue('C1', 'Data Pedido')
            ->setCellValue('D1', 'NIN/NSC')
            ->setCellValue('E1', 'Data ROGD/LGM')
            ->setCellValue('F1', 'Resolvido RNE')
            ->setCellValue('G1', 'Nº LN')
            ->setCellValue('H1', 'Nº LA')
            ->setCellValue('I1', 'Finalizado pela Associação');

$objPHPExcel->getActiveSheet()->setTitle('Simple');

$objPHPExcel->setActiveSheetIndex(0);
// Set active sheet index to the first sheet, so Excel opens this as the first sheet

header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="ROGDList.xls"');
        header('Cache-Control: max-age=0');

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
?>
Now i just add the php includes on top of the file. And again. error :(

Sorry by "spamming" i'm desesparated =(
Jan 30, 2013 at 9:03 PM
Solved!
The problem i think was the loop..

Found out this
http://phpexcel.codeplex.com/discussions/359829

And again,
Tks MarkBaker you solve it :D
And without replying to my posts