Hi, I am new to PHP but have setup a page that uses PHP to select people from an Oracle database and display their details on screen. This works fine however it has been asked if the results can also be exported to excel. I've looked around at various bits of code to try and help me do it and have put something together - however nothing happens when I click on my export button. I have included the code as it stands below - Any help much appreciated and sorry if I am being dumb somewhere!


<?php 
	$person_name =$_POST["person_name"] ;
	$person_phone =$_POST["person_phone"] ;
	$person_title =$_POST["person_title"] ;
	$person_dept =$_POST["person_dept"] ;


$contents="Name,Phone,Title,Department\n";
$query = ("select name,telephonenumber, title, department, mail 
		 from ad_data 
		 where upper(nvl(name,'X')) LIKE NVL(upper('%$person_name%'),upper(nvl(name,'X'))) 
	 	 and upper(nvl(telephonenumber,'X')) LIKE NVL(upper('%$person_phone%'),upper(nvl(telephonenumber,'X'))) 
		 and upper(nvl(title,'X')) LIKE NVL(upper('%$person_title%'),upper(nvl(title,'X'))) 
		 and upper(nvl(department,'X')) = NVL(upper(replace('$person_dept','*','''')),upper(nvl(department,'X')))
             and telephonenumber is not null
		 order by upper(name)
		 ");

    include "/u02/secure/dbcon.php";

    $stmt = ociparse($db_conn, $query);

OCIDefineByName($stmt, "NAME", $name);
OCIDefineByName($stmt, "TELEPHONENUMBER", $telephonenumber);
OCIDefineByName($stmt, "TITLE", $title);
OCIDefineByName($stmt, "DEPARTMENT", $department);
OCIDefineByName($stmt, "MAIL", $mail); 

if (!$person_name && !$person_phone && !$person_title && !$person_dept)
{
     exit;
    } else {

OCIExecute($stmt);

 while (OCIFetch($stmt)) {

   $contents.=$name.",";
   $contents.=$telephonenumber.",";
   $contents.=$title.",";
   $contents.=$department."\n";

OCIFreeStatement($stmt);
    }

   }
   $contents = strip_tags($contents); // remove html and php tags etc.

header("Content-type: vnd.ms-excel");
header("Content-Disposition: attachment; filename=C:/export.xls");
header("Pragma: no-cache");
header("Expires: 0");

print $contents;

?>

    Look into creating a Comma Separated Values (CSV) file (e.g. myfile.csv). Basically, you output a text file ending with .CSV whose data items are comma separated and each line has a carriagereturn/linefeed. Then, open that file up using Microsoft Excel as a CSV file.

    Example:
    $handle = fopen("myfile.csv", "w");
    fwrite($handle, "Name, Telephone, Title, Department, Mail\n");
    while (OCIFetch($stmt)) {
    $contents.=$name.",";
    $contents.=$telephonenumber.",";
    $contents.=$title.",";
    $contents.=$department."\n"
    fwrite($handle, $contents);
    }
    fclose($handle);

    Open up the CSV file with Excel.

      Thanks Kerry! - that has enabled me to successfully create a csv file on the server.

      How can I launch it into excel? Because I am using the php code directly within the HTML page I am having problems with headers already existing.

      Warning: Cannot modify header information - headers already sent by (output started at /u02/php/home.php:6) in /u02/php/home.php on line 172

      Is that the way/only way to launch into excel using the header?

      also how would this work for potentially multiple people running it at the same time?

        The headers are sent as soon as you send any output, usually through echo or print.

        Either make sure you set your header before output starts, or use output buffering

        In your case you start the output in home.php on line 6 as suggested by the error reporting.

          Write a Reply...