How to Create Excel files in php with csv and tsv format

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.

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

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

Conclusion

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.