creating excel file using php..need help..

[syazyan]

helo there..can anyone help me on how to make excel file using php. the data will be retrieve from multiple table in database. i've done excel file where the data from single table and i've made it. but for multiple table, i don't know how to do it..plz help me...:confused:

e.g: excel code for single table in database.



<?

include "connectdb.php";

$id = $_REQUEST['id'];

mysql_select_db('rocincorporation', $dbProd);
$SQL = "select a.*,b.vchdescription as vchcompanytype,c.vchdescription as vchcompanystatus,
d.vchdescription as status,e.vchdescription as country
from (rocincorporation.rocincorporation a
left join parameters.parameters b on b.vchcode = a.chrcompanytype and b.vchparametertype = 'Company Category Eng'
left join parameters.parameters c on c.vchcode = a.chrcompanystatus and c.vchparametertype = 'Status Of Company'
left join parameters.parameters d on d.vchcode = a.chrstatusofcompany and d.vchparametertype = 'CompanyStatus'
left join parameters.parameters e on e.vchcode = a.vchcompanycountry and e.vchparametertype = 'Country')
where a.companyno='$id'";
//echo $SQL;

$result = mysql_query($SQL, $dbProd)or die(mysql_error());
$count = mysql_num_fields($result);


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

$header=$header."\n";

$dir = "Data/";
$fname='Excel-'.$id.'.xls';
$fp = fopen($dir.$fname,'w');
fwrite ($fp,$header);
fwrite ($fp,$data);



?>

[codeexploiter]

http://www.phphacks.com/content/view/26/33/

[syazyan]

erm..codeexploiter , the link that u gave..is it means that i must install the pear??

[codeexploiter]

Yes; you need to install PEAR package.

[syazyan]

is there other way instead of using pear?

[codeexploiter]

You can try .CSV files like the same way you handle the normal file processing in PHP. The only thing is that you need to put comma after each cell value. As you know .CSV files can be opened in Microsoft Excel.

But if you are looking for generating .XLS files then I think you can try the following options:

PEAR Spreadsheet_Excel_Writer (http://pear.php.net/package/Spreadsheet_Excel_Writer)

MS-Excel Stream Handler (http://www.phpclasses.org/browse/package/1919.html)

Eg of CSV file creation using PHP (http://www.rawdata.net/developer/php_examples/files/fputcsv.php)

[syazyan]

how about if i want to display the data from various databases...can anyone help me with the code..from the example i've given, the data is from single database only... can somebody help??:confused:

[NXArmada]

you can try this. does not use any of the classes codeexploiter said



<?PHP

include('include/config.php');
include('include/opendb.php');

$result = mysql_query('SELECT * FROM repair');
$count = mysql_num_fields($result);

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 Spreadsheet.xls with whatever you want the filename to default to
header("Content-Disposition: attachment; filename=$dbname.xls");
header("Pragma: no-cache");
header("Expires: 0");

echo $header."\n".$data;

?>