[SOLVED] Writing to Excel

[ballouta]

hi, i am testing a script to generate an excel file after reading an order items from my database.

 

the first problem i got is a syntax error on line 13.

 

the link where I found the tutorial is:

http://www.999tutorials.com/tutorial-create-excel-files-with-php.html

 

I already downloaded the excel.php class and uploaded to the directory 'shopping' where the script page exists.

 

<?php
//Written by Dan Zarrella. Some additional tweaks provided by JP Honeywell
//pear excel package has support for fonts and formulas etc.. more complicated
//this is good for quick table dumps (deliverables)

//
//	

require_once "excel.php";

$filename = "theFile.xls";

export_file = "xlsfile://tmp/".$filename;  // this is line 13
$fp = fopen($export_file, "wb");
if (!is_resource($fp))
{
die("Cannot open $export_file");
}

// typically this will be generated/read from a database table
$assoc = array(
array("First name" => "Mattias", "IQ" => 250,
array("First name" => "Tony", "IQ" => 100,
array("First name" => "Peter", "IQ" => 100,
array("First name" => "Edvard", "IQ" => 100);

fwrite($fp, serialize($assoc));
fclose($fp);



header ("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
header ("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT");
header ("Cache-Control: no-cache, must-revalidate");
header ("Pragma: no-cache");
header ("Content-type: application/x-msexcel");
header ("Content-Disposition: attachment; filename=\"" . $filename . "\"" );
header ("Content-Description: PHP/INTERBASE Generated Data" );
readfile($export_file);
exit;
?>

 

I need to fix this error, after that I want to read from my database and check the last result.

Please help

..thank you

[bobbinsbro]

line 13 missing the $ sign before the variable name.

also, in the 21-25 block, you have to add an extra ) onto the end of each line (including on line 25).

[ballouta]

thanks bobbinsbro

 

the error of line 13 disappeared, still have error on line 22

here's my modified code:

 

<?php
//Written by Dan Zarrella. Some additional tweaks provided by JP Honeywell
//pear excel package has support for fonts and formulas etc.. more complicated
//this is good for quick table dumps (deliverables)

//	include('../CMS/operations.inc.php');
//	mysql_query("SET CHARACTER_SET_RESULTS=NULL");

require_once "excel.php";

$filename = "daouk.xls";

$export_file = "xlsfile://tmp/".$filename;
$fp = fopen($export_file, "wb");
if (!is_resource($fp))
{
die("Cannot open $export_file");
}

// typically this will be generated/read from a database table
$assoc = array()
array("First name" => "Mattias", "IQ" => 250,)
array("First name" => "Tony", "IQ" => 100,)
array("First name" => "Peter", "IQ" => 100,)
array("First name" => "Edvard", "IQ" => 100));

fwrite($fp, serialize($assoc));
fclose($fp);



header ("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
header ("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT");
header ("Cache-Control: no-cache, must-revalidate");
header ("Pragma: no-cache");
header ("Content-type: application/x-msexcel");
header ("Content-Disposition: attachment; filename=\"" . $filename . "\"" );
header ("Content-Description: PHP/INTERBASE Generated Data" );
readfile($export_file);
exit;
?>

 

But please before we fix this array, I need to replace it with the query that will read the orders table.

 

my query is:

$result = mysql_query("SELECT * FROM `orders` where `member` = '$user' AND `orderno` = '$thisorder'");

 

Thank you

[bobbinsbro]

is this what you wanted?

<?php
//Written by Dan Zarrella. Some additional tweaks provided by JP Honeywell
//pear excel package has support for fonts and formulas etc.. more complicated
//this is good for quick table dumps (deliverables)

//	include('../CMS/operations.inc.php');
//	mysql_query("SET CHARACTER_SET_RESULTS=NULL");

require_once "excel.php";

$filename = "daouk.xls";

$export_file = "xlsfile://tmp/".$filename;
$fp = fopen($export_file, "wb");
if (!is_resource($fp))
{
die("Cannot open $export_file");
}

// fetch DB data into an array
$result = mysql_query("SELECT * FROM `orders` where `member` = '$user' AND `orderno` = '$thisorder'");
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
//do something with the data.
//$row is an associative array, where each key corresponds to 1 column from the table 'orders'
}

fwrite($fp, serialize($assoc));
fclose($fp);



header ("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
header ("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT");
header ("Cache-Control: no-cache, must-revalidate");
header ("Pragma: no-cache");
header ("Content-type: application/x-msexcel");
header ("Content-Disposition: attachment; filename=\"" . $filename . "\"" );
header ("Content-Description: PHP/INTERBASE Generated Data" );
readfile($export_file);
exit;
?>

[ballouta]

hi, i need to go back to my second post, i would liek to fix the exisiting script as is and test it before adding my own query...

 

kindly note that the one of the error i got was fixed, still a problem in the array brackets.

<?php

require_once "excel.php";
$filename = "theFile.xls";


$export_file = "xlsfile://tmp/".$filename; // this error was fixed (missing $ sign before)
$fp = fopen($export_file, "wb");
if (!is_resource($fp))
{
die("Cannot open $export_file");
}

// typically this will be generated/read from a database table
$assoc = array(
array("First name" => "Mattias", "IQ" => 250,
array("First name" => "Tony", "IQ" => 100,
array("First name" => "Peter", "IQ" => 100,
array("First name" => "Edvard", "IQ" => 100);  // this is line 19

fwrite($fp, serialize($assoc));
fclose($fp);


header ("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
header ("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT");
header ("Cache-Control: no-cache, must-revalidate");
header ("Pragma: no-cache");
header ("Content-type: application/x-msexcel");
header ("Content-Disposition: attachment; filename=\"" . $filename . "\"" );
header ("Content-Description: PHP/INTERBASE Generated Data" );
readfile($export_file);
exit;
?>

 

the error i got is:

Parse error: syntax error, unexpected ';', expecting ')' in /home/dcompany/public_html/shopping/excel1.php on line 19

please help

thank you

[Mark Baker]

assoc = array(
array("First name" => "Mattias", "IQ" => 250),
array("First name" => "Tony", "IQ" => 100),
array("First name" => "Peter", "IQ" => 100),
array("First name" => "Edvard", "IQ" => 100)
);

[ballouta]

thanks the error disppeared now, i added $ to the assoc you wrote, but the output was strange symbols like those:

 

First name IQMattias@o@ TonyY@ PeterY@EdvardY@

 

would u please check the link, do u get the same think?

http://www.dmcpublisher.com/shopping/excel1.php

 

[Mark Baker]

The output was strange symbols like those:

 

First name IQMattias@o@ TonyY@ PeterY@EdvardY@

 

would u please check the link, do u get the same think?

http://www.dmcpublisher.com/shopping/excel1.php

No: I see an Excel file that has the data as required.

You should only see those strange symbols if you're opening the generated file in a text editor or similar. If you open it in Excel, it should be OK.

[ballouta]

Thanks again

 

Do you mean you were able to download an excel file?!

 

when i open this link, i get an error mesage (popup), windows Internet explorer :

internet explorer can;t download excel1.php from www.sitename.com

etc..

 

I also asked my brother in NL, he got the same error!

 

why?

[Mark Baker]

I can't explain why you don't see it as an Excel file, unless you don't have Excel installed (or at least have the .xls extension associated with Excel.

I get the Open/Save/Cancel dialogue as I'd expect... the file opens in Excel, and appears as I'd expect

 

Works OK in IE7 and FF3