[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

PHP/MySQL - Let user download Excel Spreadsheet of query result?

Posted on 2009-04-28
9
Medium Priority
?
729 Views
Last Modified: 2013-12-12
Hi,
I have the following PHP / MySQL code. It is suppose to export the query to an excel file however it instead displays the text in the browser window.

How do I get it ask the user to save an excel spreadsheet with the data in it?

The original scipt came from here: http://www.stargeek.com/scripts.php?script=2&cat=sql311

Regards
Mitch
//Connection string removed
//Query removed
 
for ($i = 0; $i < $count; $i++){
    $header .= mysql_field_name($result, $i)."\t";
}
 
while($row = mysql_fetch_row($result)){
  $line = '';
  foreach($row as $value){
    if(!isset($value) || $value == ""){
      $value = "\t";
    }else{
# important to escape any quotes to preserve them in the data.
      $value = str_replace('"', '""', $value);
# needed to encapsulate data in quotes because some data might be multi line.
# the good news is that numbers remain numbers in Excel even though quoted.
      $value = '"' . $value . '"' . "\t";
    }
    $line .= $value;
  }
  $data .= trim($line)."\n";
}
# this line is needed because returns embedded in the data have "\r"
# and this looks like a "box character" in Excel
  $data = str_replace("\r", "", $data);
 
 
# Nice to let someone know that the search came up empty.
# Otherwise only the column name headers will be output to Excel.
if ($data == "") {
  $data = "\nno matching records found\n";
}
 
# This line will stream the file to the user rather than spray it across the screen
header("Content-type: application/octet-stream");
 
# replace excelfile.xls with whatever you want the filename to default to
header("Content-Disposition: attachment; filename=excelfile.xls");
header("Pragma: no-cache");
header("Expires: 0");
 
echo $header."\n".$data; 
 
 
mysql_close($link);
?>

Open in new window

0
Comment
Question by:SM17CH
  • 5
  • 3
8 Comments
 
LVL 10

Expert Comment

by:mstrelan
ID: 24257540
make sure NOTHING is output before the header()s are sent. depending on your browser you may need to test it by clicking a link from another page. having it in your browser and just hitting refresh might not work. also your script is to make csv, not xls. xls has binary data to format cells etc, but Excel reads your csv files.
0
 
LVL 2

Author Comment

by:SM17CH
ID: 24257741
Ok cool Ill try that.
Is there anyway to output Excel file instead?
0
 
LVL 10

Expert Comment

by:mstrelan
ID: 24265355
Try the following
$data = '';
$filename = '';
 
/*First create a string variable and write the Excel header */
$data .= pack('vvvvvv', 0x809, 0x08, 0x00,0x10, 0x0, 0x0);
 
/* Then for each cell you need to append to the string with this code where length is the length of the string and obviously row and col are the cell you want to put the data in. */
$data .= pack('v*', 0x0204, 8 + $length, $row, $col, 0x00, $length) . $data;
 
/* However if the data you want to put in the cell is numeric you need to append this */
$data .= pack('vvvvv', 0x0203, 14, $row, $col, 0x00) . pack('d', $data);
 
/* Then to close the file you need to append this */
$data .= pack('vv', 0x0A, 0x00);
 
/* Then just output the headers and the string you've created */
header("Pragma: public");
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Content-Type: application/x-msexcel');
header('Content-Disposition: attachment; filename=' . $filename);
header("Content-Transfer-Encoding: binary");
echo($data);

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:mstrelan
ID: 24265381
ignore the above, i have used $data for two different things
$data = '';
$filename = '';
 
/*First create a string variable and write the Excel header */
$data .= pack('vvvvvv', 0x809, 0x08, 0x00,0x10, 0x0, 0x0);
 
/* Then for each cell you need to append to the string with this code where length is the length of the string and obviously row and col are the cell you want to put the data in. */
$data .= pack('v*', 0x0204, 8 + $length, $row, $col, 0x00, $length) . $celldata;
 
/* However if the data you want to put in the cell is numeric you need to append this */
$data .= pack('vvvvv', 0x0203, 14, $row, $col, 0x00) . pack('d', $celldata);
 
/* Then to close the file you need to append this */
$data .= pack('vv', 0x0A, 0x00);
 
/* Then just output the headers and the string you've created */
header("Pragma: public");
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Content-Type: application/x-msexcel');
header('Content-Disposition: attachment; filename=' . $filename);
header("Content-Transfer-Encoding: binary");
echo($data);

Open in new window

0
 
LVL 2

Author Comment

by:SM17CH
ID: 24265958
Im a little bit confuesd where abouts your script goes into mine? The fact that you are using $data in yours and I am too is thowing me off I think.
Do I replace the FOR, WHILE part near the start with yours or do I need to integrate it.
Thanks heaps for that effort you have put in.
0
 
LVL 10

Accepted Solution

by:
mstrelan earned 2000 total points
ID: 24266029
sorry, i didn't actually look at your code when giving the advice. i guess that would help wouldn't it. you'll need to integrate... below is the basic idea ... i havent checked the syntax or anything so you might have to get your hands dirty
$data = '';
$filename = '';
$rowNumber = 0;
 
/*First create a string variable and write the Excel header */
$data .= pack('vvvvvv', 0x809, 0x08, 0x00,0x10, 0x0, 0x0);
 
 
// write the header row
for ($i = 0; $i < $count; $i++){
    $celldata = mysql_field_name($result, $i);
    $length = strlen($celldata);
    $col = $i;
    $row = 0;
    $data .= pack('v*', 0x0204, 8 + $length, $row, $col, 0x00, $length) . $celldata;
}
 
 
while($row = mysql_fetch_row($result)){
  $rowNumber++;
  $line = '';
  foreach($row as $col => $value){
    if(!isset($value) || $value == ""){
      // $value = "\t";
      // do nothing
    }else{
# important to escape any quotes to preserve them in the data.
      $value = str_replace('"', '""', $value);
# needed to encapsulate data in quotes because some data might be multi line.
# the good news is that numbers remain numbers in Excel even though quoted.
      $value = '"' . $value . '"' . "\t";
    }
    //$line .= $value;
    $celldata = $value;
    $length = strlen($celldata);
    $row = $rowNumber;
    if (is_numeric($celldata)) {
    	$data .= pack('vvvvv', 0x0203, 14, $row, $col, 0x00) . pack('d', $celldata);
    } else {
    	$data .= pack('v*', 0x0204, 8 + $length, $row, $col, 0x00, $length) . $celldata;
    }
  }
  
}
# this line is needed because returns embedded in the data have "\r"
# and this looks like a "box character" in Excel
//  $data = str_replace("\r", "", $data);
 
mysql_close($link); 
# Nice to let someone know that the search came up empty.
# Otherwise only the column name headers will be output to Excel.
if ($data == "") {
  echo "\nno matching records found\n";
  return;
}
 
# This line will stream the file to the user rather than spray it across the screen
header("Content-type: application/octet-stream");
 
/* Then just output the headers and the string you've created */
header("Pragma: public");
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Content-Type: application/x-msexcel');
header('Content-Disposition: attachment; filename=' . $filename);
header("Content-Transfer-Encoding: binary");
echo($data);
 
 
 
?>

Open in new window

0
 
LVL 2

Author Comment

by:SM17CH
ID: 24266056
Great, Thanks. I see what you mean now. Ill play around with it and report back
Thanks again for your help. Greatly appreciated.
0
 
LVL 10

Expert Comment

by:mstrelan
ID: 24266065
also note that where i've said pack(...... $row) the choice of variable name "row" is probably a terrible idea since youve already used that in your while loop ... so just get rid of $row = $rowNumber and change the row parameter in pack to $rowNumber
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Although a lot of people devote their energy toward marketing for specific industries, there are some basic principles that can be applied to any sector imaginable. We’ll look at four steps to take and examine how those steps were put into action fo…
CTAs encourage people to do something specific to show interest in your company, product or service. Keep reading to learn why CTAs should always be thought of as extremely important, albeit small, sections of websites.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question