data populating in only one cell 2

[Zoom1234]

Hi All,


I am trying to create a spreadsheet though PHP. Tough i am not using any specific library, i am formatting the data first and then writing it into file as text stream. The excel file is getting generated but the data is populating only once cell (left first row), but i want it to be in different rows and columns (db extraction). I tried a simple code to check but the output is still in single cell. Following is the code.

<?php

$file = "report_" . date('Ymd') . ".xls";

$content = '  <Worksheet ss:Name="report">' . "\n";
$content .= '    <Table>' . "\n";

$content .= '<ss:Row>'."\n";
$content .= '<Cell>';
$content .= '<Data ss:Type="TEXT">TESTING</Data>';
$content .= '</Cell>';
$content .= '<Cell>';
$content .= '<Data ss:Type="TEXT">TESTING2</Data>';
$content .= '</Cell>';
$content .= '</ss:Row>'. "\n";
$content .= '    </Table>' . "\n";
$content .= '  </Worksheet>' . "\n";


$fd = fopen("f:\\test\\$file",'w+') or die ("can not open the file!!");
fwrite($fd,$content);
fclose($fd);


?>

The output file contains TESTINGTESTING2 in first left hand cell but i want it to be on different cells. Can anyone guide me here for what is wrong.

Thanks in advance.

 

[doc81]

why dont you use fputcsv

this is what i use and it does the job.

<?php

$list = array (
    'aaa,bbb,ccc,dddd',
    '123,456,789',
    '"aaa","bbb"'
);

$fp = fopen('file.csv', 'w');

foreach ($list as $line) {
    fputcsv($fp, split(',', $line));
}

fclose($fp);
?>

 

[jpadie]

@Zoom1234

can you point me to the xml specification for excel? i can then take a look for you.

 

[Zoom1234]

Hi doc81,
Thanks for the alternative way. I will try that out. But just wondering why the current code is not working, as i have another script (written by someone else) in similar fashion and works well.

Hi Jpadie,

I will give you the specification. Pushing for home right now

Again thanks for all your help.

 

[Zoom1234]

Hi doc / Jpadie,

I tried the csv route and it's working fine on my local windows machine. I will be creating the file and sending the file as attachment on Unix server. Will there be any problem becuase of the server change.

 

[jpadie]

you may have issues with line termination, so be sure to specify the 't' switch.

but i thought the exercise was to generate an excel file, not a csv. although csv can be imported in to excel, they lack any ability to have formulas, formatting etc.

 

[ingresman]

You could try SYLK format at

http://www.wotsit.org/download.asp?f=sylksum&sc=332935182

(and other places) or if your really brave

http://phpexcel.codeplex.com/

which I really must try sometime

 

[jpadie]

i'm with ingresman - I'd use phpexcel, but I was again reading that you did not want to use a class but were trying to create your own xml schema that excel would suck up.

 

[Zoom1234]

Hi jpadie,

Yes, basically the script is to generate a report and mail it to the users. So the extra facilities of excel like formulas are not necessary.

As you said, I may have issues with line terminsation. Can you please elaborate on how can i avoid that.

Thanks

 

[jpadie]

I was going to say 'use the 't' switch to fopen'
but I now read that this is deprecated.

so the answer is:

1. always use the binary mode

$fH = fopen('myfile.txt','rb'); //read only binary mode
$fH = fopen('myfile.txt', 'wb'); //write binary mode

2.  always use this is as a line terminator "\n" and NOT the windows variant

3.  don't try to edit the files in notepad or similar.

however if you are using fputcsv and fgetcsv, the function itself inserts and handles line terminators, so no need to worry.  but do make sure that you use binary mode for portability.

 

[Zoom1234]

Quality help. Thank you all.