[SOLVED] Downloading an excel file

[neverett]

I'm trying to run a query on my MySQL Database and export that query into an excel file that you can download.  I have included some code, but my browser isn't prompting me to download the file.  I've tried several different things, but please post any ideas you may have.  I need this to download an excel file, but if there are any alternative ways please let me know!!!  Thanks for all of your help in advance!  Thanks again,

 

NE

 

<?
	$query = "SELECT * FROM volunteers";
	$result = mysql_query($query) or die('Error, query failed');
	$tsv = array();
	$html = array();
	while($row = mysql_fetch_array($result, MYSQL_NUM)){
		$tsv[] = implode("\t", $row);
		$html[] = "<tr><td>" .implode("</td><td>", $row) . "</td></tr>";
	}
	$tsv = implode("\r\n", $tsv);
	$html = "<table>" . implode("\r\n", $html) . "</table>";

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

	echo $tsv;
	//echo $html;
?>

[MadTechie]

see ForceDownload

[neverett]

I tried that and it didn't seem to work.  I don't know what the problem is.  Also, I don't have a source file.  I'm creating the file from a MySQL query, and I don't know if you can do that.  Thanks for your help!  I appreciate it!!!!

[MadTechie]

can you post what you tried (in the way of the force download)

[Psycho]

Here is a sample script that I use to dynamically create and deliver Excel files for download:

 

<?
// Connect database. 
mysql_connect("localhost","","");
mysql_select_db("tutorial");

// Get data records from table. 
$result=mysql_query("select * from name_list order by id asc");

// Functions for export to excel.
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; 
}


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

xlsBOF();

while ($record = mysql_fetch_assoc($result)) {
    xlsWriteLabel(0,0,"Name");
    xlsWriteLabel(0,1,$record['name']);
    xlsWriteLabel(1,0,"Age");
    xlsWriteLabel(1,1,$record['age']);
    xlsWriteLabel(2,0,"Address:");
    xlsWriteNumber(2,1,$record['address']);
    xlsWriteLabel(3,1,$record['city'].', '.$record['state'].' '.$record['zip']);
}

xlsEOF();
exit();
?>

[neverett]

That still didn't work.  The results are like this:

 

 ����� ��������ID �������1�����

 

It still will not prompt me to download the file.  Any help is appreciated.  Some other info... I'm using Ubuntu Linux with Firefox 2.0.0.8.  Thanks for all of your help!!!

 

NE

[MadTechie]

can you post what you tried (in the way of the force download)

[MadTechie]

example

<?php
	$query = "SELECT * FROM volunteers";
	$result = mysql_query($query) or die('Error, query failed');
	$tsv = array();
	$html = array();
	while($row = mysql_fetch_array($result, MYSQL_NUM)){
		$tsv[] = implode("\t", $row);
		$html[] = "<tr><td>" .implode("</td><td>", $row) . "</td></tr>";
	}
	$tsv = implode("\r\n", $tsv);
	$html = "<table>" . implode("\r\n", $html) . "</table>";

	header("Pragma: no-cache");
	header("Expires: 0");
	header("Pragma: public");
	header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); 
	header("Content-Type: application/force-download");
	header("Content-Disposition: attachment; filename=\"extraction.xls\"");
	header("Content-Description: File Transfer");
	echo $tsv;
	exit;
	//echo $html;
?>

[neverett]

Still didn't work.  To test it yourself you can go to www.niceverett.com/test.php.  To view the code source you can go to www.niceverett.com/test.phps.  Thanks again for your help everyone!!!

[Psycho]

You're trying to output an Excel file in the middle of an HTML page. You need to ONLY create the Excel file and nothing else on that page.

 

Change that page to this and only this

 

<?php
$query = "SELECT * FROM movie";
$result = mysql_query($query) or die(mysql_error());
$tsv = array();
$html = array();
while($row = mysql_fetch_array($result, MYSQL_NUM)){
	$tsv[] = implode("\t", $row);
	$html[] = "<tr><td>" .implode("</td><td>", $row) . "</td></tr>";
}
$tsv = implode("\r\n", $tsv);
$html = "<table>" . implode("\r\n", $html) . "</table>";
header("Pragma: no-cache");
header("Expires: 0");
header("Pragma: public");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); 
header("Content-Type: application/force-download");
header("Content-Disposition: attachment; filename=\"extraction.xls\"");
header("Content-Description: File Transfer");
echo $tsv;
exit;
//echo $html;
?>

[XJTRy]

Anyway to post field names as well. This works for me but no field names are imported.