Writing to Excel File

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!

Moderator: General Moderators

Post Reply
gtcol
Forum Newbie
Posts: 20
Joined: Mon May 28, 2007 10:15 am
Contact:

Writing to Excel File

Post 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]
User avatar
Gente
Forum Contributor
Posts: 252
Joined: Wed Jun 13, 2007 9:43 am
Location: Ukraine, Kharkov
Contact:

Post by Gente »

I think you chose wrong forum. Anyway try this

Code: Select all

header('Content-Disposition: attachment; filename="MyExcel.xls"');
gtcol
Forum Newbie
Posts: 20
Joined: Mon May 28, 2007 10:15 am
Contact:

Writing to Excel File

Post 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
User avatar
Gente
Forum Contributor
Posts: 252
Joined: Wed Jun 13, 2007 9:43 am
Location: Ukraine, Kharkov
Contact:

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Have you considered using the Excel Writer provided by PEAR? The code above is simply creating a tab-separated-values file.
gtcol
Forum Newbie
Posts: 20
Joined: Mon May 28, 2007 10:15 am
Contact:

Writing to Excel File

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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
Post Reply