Exporting to Excel from PHP

[blepblep]

Hi people. Need some help if anyone could give a hand!

 

I'm currently trying to incorporate an export to Excel feature on search returns on my web page. Here is how my searches are returned, so what I want now is when the user clicks on the 'Export to Excel' button it downloads the search results and opens it in the same format in Excel.

 

 

I've tried using http://phpexcel.codeplex.com/ but can't understand how to use it properly, does anyone have any suggestions? I don't have any code written for the Export to Excel yet as it's just a submit button. How I see it in my head is the user clicks the button, it goes to a new page and performs a new query then downloads that but I don't no if thats right or not.

[harristweed]

I have an export that I use, here is the code, it might point you in the right direction:

<?php
include("variables.php");

$link_id = mysql_connect("$db_host","$db_user","$db_password");
         if (mysql_select_db("$db_database", $link_id));
        else
         {
             echo "connection failed.";
         }
$select = "SELECT club_nr, first_name, surname, email, address1, address2, postcode, town, province, phone FROM members";                 
$export = mysql_query($select); 
$fields = mysql_num_fields($export); 
for ($i = 0; $i < $fields; $i++) { 
   $header .= mysql_field_name($export, $i) . "\t"; 
} 
while($row = mysql_fetch_row($export)) { 
    $line = ''; 
    foreach($row as $value) {                                             
        if ((!isset($value)) OR ($value == "")) { 
            $value = " \t"; 
        } else {
        $value=stripcslashes($value); 
            $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";                         
} 
header("Content-type: application/x-msdownload"); 
header("Content-Disposition: attachment; filename=bridge_club_members.xls"); 
header("Pragma: no-cache"); 
header("Expires: 0"); 
print "$header\n$data"; 
?>

[blepblep]

Thanks for that harristweed! That works but doesn't format it in excel in any way, and it returns the names of the tables how their stored in the database rather than written on the web page like so -

 

 

Is there any way I can format how it is displayed? And have it named correctly?

[blepblep]

Does anyone have any idea what to do?

[blepblep]

I've been looking online and found some things so this is how my code looks at the moment -

 

<?php

/*
$select = "SELECT * FROM tc_tool.review";      
   
$export = mysql_query($select); 
$rows = mysql_num_fields($export); 
*/

include 'connect_db.php';

$query = "SELECT * FROM tc_tool.review"; 
$db->setQuery($query);
$rows = $db->loadObjectList();

if ($rows) {

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; 
};

// 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=export.xls ");
header("Content-Transfer-Encoding: binary ");

// XLS Data Cell
xlsBOF();
xlsWriteLabel(1,0,"Review Forum");
xlsWriteLabel(1,1,"Review ID");
xlsWriteLabel(1,2,"Document Title");
xlsWriteLabel(1,3,"Document Number");
xlsWriteLabel(1,4,"Document Type");
xlsWriteLabel(1,5,"Project Name");
xlsWriteLabel(1,6,"Author Name");
xlsWriteLabel(1,7,"Chairperson");
$xlsRow = 2;

$i = 0;
foreach ($rows as $item) {

  $reviewForum   = $rows[$i]->reviewForum;
  $reviewId  	 = $rows[$i]->reviewId;
  $docTitle   	 = $rows[$i]->docTitle;
  $docNumber 	 = $rows[$i]->docNumber;
  $docType  	 = $rows[$i]->docType;
  $projectName   = $rows[$i]->projectName;
  $authorName    = $rows[$i]->authorName;
  $chairPerson   = $rows[$i]->chairPerson;

  xlsWriteNumber($xlsRow,0,"$reviewForum");
  xlsWriteNumber($xlsRow,1,"$reviewId");
  xlsWriteNumber($xlsRow,2,"$docTitle");
  xlsWriteNumber($xlsRow,3,"$docNumber");
  xlsWriteNumber($xlsRow,4,"$docType");
  xlsWriteNumber($xlsRow,5,"$projectName");
  xlsWriteNumber($xlsRow,6,"$authorName");
  xlsWriteNumber($xlsRow,7,"$chairPerson");

$xlsRow++;
$i++;
};

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

xlsEOF();
exit();

};
?>

 

When I run it how the code is above I get this error -

 

Fatal error: Call to a member function setQuery() on a non-object in /opt/htdocs/webpages/TC_Tool/Tool2/tc_tool/exportToExcel.php on line 13

 

And when I change the query part of the above code to this -

 

<?php

include 'connect_db.php';

$select = "SELECT * FROM tc_tool.review";      
   
$export = mysql_query($select); 
$rows = mysql_num_fields($export); 


if ($rows) {

.......

 

It downloads but when Excel tries to read the file this is what happens -

 

[blepblep]

Anyone? 

[kicken]

If you really must have the formatting then you'll probably need to use a library such as phpexcel.  I've never used it so can't be much help there, read the documentation and examples and try and adapt.

 

I never both with formatting for my excel exports, as such I just generate a CSV file and serve that up.  Excel does understand html tables so you could try dumping out a simple document with only the data table and formatting and see if it will properly import the formatting with that.

 

 

[blepblep]

No I don't need formatting kicken, that doesnt matter. Just once it displays it under the correct headings.

 

Also I've tried phpexcel and it won't work on my server as the root server doesn't allow it and I cant contact the admin.

[kicken]

If you don't need the formatting then just output a CSV file.  Have the first row be whatever heading values you want and the rest of the rows be the data.  Here's a sample of some code I use for an export:

$fp = tmpfile();
//Output headers
$len = fputcsv($fp, array(
'Name',
'Invoice',
'ID',
'Notes',
'Record Type',
'For',
'Transaction Type',
'Date',
'Amount',
'Credits',
'Debits',
'Payments',
'Balance'
));
foreach ($res as $row){
//Output data row.
$len+=fputcsv($fp, array(
	$row['fname'].' '.$row['lname'],
	$row['invoiceNumber'],
	$row['invoiceId'],
	$row['invoiceNotes'],
	$row['recordTypeName'],
	$row['invoiceFor'],
	$row['transactionType'],
	$row['invoiceDate']->formatLocal('m/d/Y'),
	$row['invoiceAmount'],
	$row['credits'],
	$row['debits'],
	$row['payments'],
	$row['invoiceBalance']
));
}

$outputFilename='invoices_'.$outputFilename.'.csv';

rewind($fp);
header('Content-length: '.$len);
header('Content-type: text/csv; charset=utf8');
header('Content-disposition: attachment; filename='.$outputFilename);
fpassthru($fp);
fclose($fp);
exit;

[blepblep]

Thanks I managed to get it working. I've just a problem now though, my query works if I want to export everything from the database.

 

$result = mysql_query("SELECT * FROM tc_tool.review");

 

 

But when I want to export on user input, so if the user inputs document number 5 say, just to have the option to export that. Here is my query for that but it's not exporting anything to excel, but works in the database?

 

$result = mysql_query("SELECT * FROM tc_tool.review WHERE docNumber like '$docNumber'");

 

Any ideas how I can export on user input?

 

 

[blepblep]

Bump

[cyberRobot]

$result = mysql_query("SELECT * FROM tc_tool.review WHERE docNumber like '$docNumber'");

 

Any ideas how I can export on user input?

 

 

The query is currently set up to be an exact match? Since you're using LIKE, you'll probably need to add some wildcard characters...

 

<?php
//...

$result = mysql_query("SELECT * FROM tc_tool.review WHERE docNumber like '%$docNumber%'");

//...
?>

 

 

For more information, see:

http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

 

 

Also, since it sounds like $docNumber contains user input, you might need to look into mysql_real_escape_string() if you haven't already:

http://php.net/manual/en/function.mysql-real-escape-string.php