Writing to Excel File
Posted: Sun Jun 24, 2007 4:47 pm
feyd | Please use
Please suggest. I appreciate your time n consideration on this.
Thanks.
gtcol
feyd | Please use
Code: Select all
,Code: Select all
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.Code: Select all
<?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;
?>Thanks.
gtcol
feyd | Please use
Code: Select all
,Code: Select all
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]