0

I have written a small query. I want to create link on my php site which on click will run this script automatically and export results to an excel file. I dont want to create a page to view the results.

Any help to this new bie will be very useful. I am not well conversed with PHP :( just know a little bit of mysql.

4
Contributors
9
Replies
14
Views
6 Years
Discussion Span
Last Post by leardas
0

This is simple code for exporting student table to excel.
Hope this helps you.

<?
	mysql_connect('localhost','root','');
	mysql_select_db('test');
	
	$sql = "select name,sirname from student";

	// Query Database
	$result=mysql_query($sql);
	$filename = 'file.xls';
		
	// Send Header
	header("Pragma: public");
	header("Expires: 0");
	header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
	header("Content-Type: application/force-download");
	header("Content-Type: application/octet-stream");
	header("Content-Type: application/download");;
	header("Content-Disposition: attachment;filename=$filename");
	header("Content-Transfer-Encoding: binary ");
	
	// XLS Data Cell
	
	xlsBOF();	  
	xlsWriteLabel(0,0,"Name : ");
	xlsWriteLabel(0,1,"Sirname :");
   
	$xlsRow = 1;
	while(list($name,$sirname)=mysql_fetch_row($result)) 
	{		
		++$i;
		xlsWriteLabel($xlsRow,0,"$name");
		xlsWriteLabel($xlsRow,1,"$sirname");                         
		$xlsRow++;
	}
	xlsEOF();
	exit();

function xlsBOF() {
    echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);  
    return;
}

function xlsEOF() {
    echo pack("ss", 0x0A, 0x00);
    return;
}

function xlsWriteNumber($Row, $Col, $Value) {
    echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
    echo pack("d", $Value);
    return;
}

function xlsWriteLabel($Row, $Col, $Value ) {
    $L = strlen($Value);
    echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
    echo $Value;
return;
} 

?>
0

Thanks vibha. I see the below error message:

Excel found unreadable content in file.xls

0

This is because you may have incorrect data in query output.
comment all header lines and check mysql_fetch_row is returning proper output or not?
Also check all list function arguments.

while(list($name,$sirname)=mysql_fetch_row($result))
0

new error:

UserName : LearnerName :
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in path\\customreport.php on line 29

Also, can you explain me what i is xlsBOF() ann xlsEOF()? Do i need to modify it? If yes, how?

0

Hi

If interested please try the below code for csv

<?php
  //
  // establish database connection
  //
  $conn = mysql_connect( 'hostname', 'username', 'password' ) or die( mysql_error( ) );
  mysql_select_db( 'dbname', $conn ) or die( mysql_error( $conn ) );
  //
  // execute sql query
  //
  $query = sprintf( 'SELECT * FROM tablename' );
  $result = mysql_query( $query, $conn ) or die( mysql_error( $conn ) );
  //
  // send response headers to the browser
  // following headers instruct the browser to treat the data as a csv file called export.csv
  //
  header( 'Content-Type: text/csv' );
  header( 'Content-Disposition: attachment;filename=filename.csv' );
  //
  // output header row (if atleast one row exists)
  //
  $row = mysql_fetch_assoc( $result );
  if ( $row )
  {
    echocsv( array_keys( $row ) );
  }
  //
  // output data rows (if atleast one row exists)
  //
  while ( $row )
  {
    echocsv( $row );
    $row = mysql_fetch_assoc( $result );
  }
  //
  // echocsv function
  //
  // echo the input array as csv data maintaining consistency with most CSV implementations
  // * uses double-quotes as enclosure when necessary
  // * uses double double-quotes to escape double-quotes 
  // * uses CRLF as a line separator
  //
  function echocsv( $fields )
  {
    $separator = '';
    foreach ( $fields as $field )
    {
      if ( preg_match( '/\\r|\\n|,|"/', $field ) )
      {
        $field = '"' . str_replace( '"', '""', $field ) . '"';
      }
      echo $separator . $field;
      $separator = ',';
    }
    echo "\r\n";
  }
?>

Edited by Reverend Jim: Fixed formatting

0

I tried using the CSV code, but when I navigate to the page with the code it's just blank and doesn't download anything? Is my browser supposed to just automatically download the CSV or what?

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.