Create excel files in php – simple and easy way

Database applications written in php often need to export data for reporting purpose. A popular export format is excel. Excel is a spreadsheet that lays out data in a grid format. Excel itself is a microsoft proprietory format. There are many libraries available for php like Spreadsheet_Excel_Writer pear package etc that can do the job. However these libraries need to be included with the application and are sometimes difficult to install.

If only a simple export in grid format is needed, then there are better solutions than excel. For example csv and tsv. These are very simple formats that can be generated with just a little code and are compatible with most spreadsheet applications like openoffice or ms-excel. Lets take a look at each of these.

TSV - Tab Separated Value Format

$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);

In that above example we are writing tabular data or say data from a database table into a file line by line and field by field. Its very simple to understand and read and at the same time its very portable as an export. That was nothing more than a simple tab delimited file (tsv : tab separated values) which Ms-excel , openoffice are comfortable at reading. And yes open it in excel and save as any other format you want. The filename can be 'data.tsv' if you like.

CSV - Comma separated value

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

And...






The above mentioned formats have many benefits over closed formats like excel. These formats are standard, portable and widely supported. They can be used for import directly in databases like mysql.

Unless there is a need to create complex formatting and media embedding, csv/tsv format should work well. If you need to create xlsx files however, check out the php library called phpexcel from codeplex.

Last Updated On : 29th September 2008

Subscribe to get updates delivered to your inbox

About Silver Moon

Php developer, blogger and Linux enthusiast. He can be reached at [email protected]. Or find him on

2 Comments + Add Comment

  • Hi
    This is only a trick. But excellent information. But it will not solve my problem. I want to export my data into a XLS file not as a CSV/TSV.

    Please post if you have any idea/updates.

    Thanks

Leave a comment