Exporting MySQL Data to Excel using PHP

Posted by ajtrichards@googlemail.com on January 5, 2009, 10:18 am

Please log in for more thread options

Hi.

I've got the following code:

$file = "exported_data.xls";

require('../../Connections/mysql.php');

header("Content-Disposition: attachment; filename=\"".$file."\"");
header("Content-Type: application/excel");
flush();

$query = "SELECT * FROM scores WHERE month = '$month' AND year =
'$year' AND contract_id = '".$_SESSION['contract_id']."' ORDER BY
agentname, timestamp ASC";
$result= mysql_query($query);
$num = mysql_num_rows($result);

show/hide quoted text
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
show/hide quoted text
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11"
show/hide quoted text
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="8"
show/hide quoted text
<Border ss:Position="Bottom" ss:LineStyle="Continuous"
show/hide quoted text
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="9"
ss:Color="#000000"
show/hide quoted text
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"
show/hide quoted text
<Border ss:Position="Bottom" ss:LineStyle="Continuous"
show/hide quoted text
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="9"
ss:Color="#000000"
show/hide quoted text
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="8"
show/hide quoted text
<Table ss:ExpandedColumnCount="27" ss:ExpandedRowCount="<?php echo
show/hide quoted text
<Column ss:Index="4" ss:StyleID="s62" ss:AutoFitWidth="0"
ss:Width="56.25"
show/hide quoted text
<Column ss:Index="13" ss:StyleID="s62" ss:AutoFitWidth="0"
show/hide quoted text
<Column ss:Index="15" ss:StyleID="s62" ss:AutoFitWidth="0"
show/hide quoted text

<?php

while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
show/hide quoted text

show/hide quoted text

<?php
}
show/hide quoted text
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7"
show/hide quoted text
<?php
flush();
show/hide quoted text

When I run this script using Firefox it works fine. The file gets
downloaded and MS Excel opens up. However, if I use Internet Explorer
I get a message saying:

"Internet Explorer cannot download export.php?month=12&year=2008 from
HOSTNAME.

Internet Explorer was not able to open this Internet site. The
requested site is either unavailable or cannot be found. Please try
again later."

The site is there and it is available!!!

Can anyone help me??

Best Regards,
Alex

Posted by Mathieu Maes on January 5, 2009, 11:56 am

Please log in for more thread options

Hi Alex,

It is possible that PHP generates an error in the output. Firefox
ignores this and simply downloads the file as told (by your HTTP
header). Internet explorer has an option called "show user friendly
error messages" which gives you the error message you mentioned.

I suggest you run the PHP script with application-type: text/plain...
Hopefully you should find yourself a PHP error that you need to solve
first.

Kind regards,
Mathew

Posted by FutureShock on January 5, 2009, 4:18 pm

Please log in for more thread options

Mathieu Maes wrote:
show/hide quoted text
Wow, I am writing this one down in my handy-dandy notebook.

Learn something new everyday.

Scotty

Posted by Peter Chant on January 6, 2009, 6:29 am

Please log in for more thread options

Mathieu Maes wrote:

show/hide quoted text

I've found that Content-disposition and related header that work fine with
Firefox don't work with IE. That might be your problem.

Here's what I use for PDFs:

/* Even works with MS Internet Explorer */
//header("Cache-Control: public");
header("Content-Description: File Transfer");
header('Content-disposition: attachment; filename='.basename($filename));
header("Content-Type: application/pdf");
header("Content-Transfer-Encoding: binary");
header('Content-Length: '. filesize($serve_file_path));
header("Pragma: public");
header('Cache-Control: max-age=0');
readfile($serve_file_path);

$serve_file_path is the filename with full path.

It may be worth your while looking up Excel_Spreadsheet_Writer under Pear.

Pete

--
http://www.petezilla.co.uk

Posted by C. (http://symcbean.blogspot.c on January 6, 2009, 7:25 am

Please log in for more thread options

On 5 Jan, 15:18, "ajtricha...@googlemail.com"
show/hide quoted text

(we didn't really need to know all this - when you come across a
problem like this try to build a test rig which replicates the error
with the minimum amount of code - you'll find it much easier to debug)

show/hide quoted text

Is this via SSL? MSIE has several bugs with regard to caching secure
content.

http://support.microsoft.com/kb/812935

Might help.

C.