PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Hello all,
I have MSSQL db and i want to export some reports from the DB using PHP. Is there any function i can use to extract records from DB and write it into some file format like excel etc.
Please check this snippet. I found it in the Net. It runs fine. I echoed and it wrote all in excel. But the problem is it does not write into the file and that is what I want to do.
<?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)
$connection = mssql_connect('gt-lap', 'sa', 'sa') or die ("Could not connect to the server");
$db = mssql_select_db("testyms", $connection) or die ("Could not connect to the database");
//include('DB_connection.php');
$result = mssql_query('select * from tbl_ContainerDetails', $connection);
$count = mssql_num_fields($result);
for ($i = 0; $i < $count; $i++){
$header .= mssql_field_name($result, $i)."\t";
}
while($row = mssql_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=G:\Ganesh\YMS\includes\MyExcel.xls");
header("Pragma: no-cache");
header("Expires: 0");
echo $header."\n".$data;
?>
Please suggest. I appreciate your time n consideration on this.
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Hello Gente,
Thanks for the reply.
I had already tried that option. When that didn't work, I landed into this option, providing full path for the xls file. Also, you mentioned I've come into wrong forum, if it is so, could you please suggest a forum where i might get the answers. Or any manuals, tutorials on the topic would be great.
I appreciate your kind help.
<?php
# 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="MyExcel.xls"');
header("Pragma: no-cache");
header("Expires: 0");
echo "cucu-mumu\n";
?>
Works great. Or maybe I didn't understand your main problem.
About forum. Because your problem is PHP-relative only you should chouse 'PHP-code' section in the future.
Hi all,
Thanks for your valueable replies.
Feyd has the got what I meant. Actually, I didn't explain the issue properly earlier. This snippet writes the output to the screen and not to the excel file. The snippet should be able to create an excel file and write into it or write into an existing file.
And as for trying Excel Writer provided by PEAR, I don't have PEAR at the moment and I can't afford to install anything new due to dead lines.
Any more ideas or snippets on this?