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.
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.
Or you could use PHP built in fputcsv function
The excel file is created and it opens on linux, but it says it is corrupt in windows.
Any suggestions please.
Thank you so much, it’s very helpful :)
Thanks for this script but its generate xls file but when i open that file its give me this warning http://prntscr.com/i2gopz ,
So can you let me know what wrong over here?
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.
For xls or xlsx formats use the PHPExcel Library from codeplex.