Write Excel Files in PHP – Simple and Easy Way

An article by Binary Tides    2 Comments

Hi

So this time my simple task was to extract some data from some websites and write them into an excel file and submit. So I simply googled up and found a class called
MS-Excel Stream Handler. With it I went really happy as it was very easy and simple to use, with just a include file.

After writing and writing excel files suddenly I noticed that some fields in the excel file were messed with non-sense characters and that the data was corrupted. This happened on fields where the data size exceeded 255 bytes. The support section of this class had a post related to this point and it came out that this class indeed had this drawback since it was using the excel-97 file format.

Again I googled up and landed on Pear package Spreadsheet_Excel_Writer. But I wanted something simple like a small file. And here is what I discovered , a really simple and easy way to write excel files :

$data = "Name \t City \t Country\n";
$data .= "Ashok \t New Delhi \t India\n";
$data .= "Krishna \t Bangalore \t India\n";

$f = fopen('data.xls' , 'wb');
fwrite($f , $data );
fclose($f);

And you are done.

Your excel file is ready. That was nothing more than a simple tab delimited file (tsv : tab separated values) which excel 2000+ ,openoffice.org are comfortable at reading. And yes open it in excel and save as any other format you want. Amazing ? Well its all about a broader perception.

Another very simple format is csv : comma separated values format which can be opened in applications like ms-excel and openoffice.org spreadsheet. Can be written like this :

$data = "Name , City , Country\n";
$data .= "Ashok , New Delhi , India\n";
$data .= "Krishna , Bangalore , India\n";

$f = fopen('data.csv' , 'wb');
fwrite($f , $data );
fclose($f);

Both the above mentioned methods are powerful ways of representing tabular data being output from a database. Both the formats are very portable. There are some more rules to how to write csv files in proper format and can be read at the wikipedia article on the subject

Have fun!

Popularity: 8% [?]