Page 1 of 1

Writing to Excel File

Posted: Sun Jun 24, 2007 4:47 pm
by 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;
?>
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]

Posted: Mon Jun 25, 2007 2:48 am
by Gente
I think you chose wrong forum. Anyway try this

Code: Select all

header('Content-Disposition: attachment; filename="MyExcel.xls"');

Writing to Excel File

Posted: Mon Jun 25, 2007 3:08 am
by gtcol
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.

Thanking you once again,
gtcol

Posted: Mon Jun 25, 2007 3:31 am
by Gente
Hmm... I've tried this example

Code: Select all

<?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.

Posted: Mon Jun 25, 2007 8:02 am
by feyd
Have you considered using the Excel Writer provided by PEAR? The code above is simply creating a tab-separated-values file.

Writing to Excel File

Posted: Mon Jun 25, 2007 8:18 am
by gtcol
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?

Regards,
gtcol

Posted: Mon Jun 25, 2007 8:27 am
by feyd
You won't be able to create an Excel file without help, so switch the content-type and filename provided by the disposition.

http://filext.com/file-extension/tsv