Jump to content

Exporting to Excel from PHP


blepblep

Recommended Posts

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.

 

9a5wyw.jpg

 

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.

Link to comment
Share on other sites

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"; 
?>

Link to comment
Share on other sites

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 -

 

152nvhk.jpg

 

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

Link to comment
Share on other sites

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 -

 

ayqann.jpg

Link to comment
Share on other sites

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.

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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;

Link to comment
Share on other sites

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?

 

 

Link to comment
Share on other sites

$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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.