How to export tabular data from php / mysql to MS excel
In this article, I show how to build a PHP Web application that uses these Excel formats to export the contents of a mysql database table or array to an simple Excel spreadsheet.Microsoft Office 2003 for the Microsoft Windows® make simple Excel as simple xml data with predefined XSLT stylesheet. It also supports HTML table data and delemiter support.Here we take these two advantages.
First technique is just simple as we fetch the data from source like arrays or mysql table in HTML table,force the browser to download the file as xls file.When download completed we can open with MS Excel.
Code I
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | <?php /* This example demonstrates how to convert PHP array data to MS Excel 2003 data to download <br> user data base. Here we create a simple HTML tabler data and we force the web browser as stream data instead we simply displaying HTML table thats all */ $data_headers=array('First Name','Last Name','Date of Birth'); $data_array[]=array('Mahesh','Chari','25-08-1983'); $data_array[]=array('Chari','Mahesh','25-08-1980'); $data_array[]=array('Guggilla','Mahesh','25-08-1970'); $data_array[]=array('Mahesh','Guggilla','21-08-1985'); $data_array[]=array('Mahesh Chari','Guggilla','20-08-1950'); $data_array[]=array('Chari','Guggilla','25-08-1950'); // here we prepare a simple HTML table string $html_string='<table>'; $html_string.='<tr><td>'.implode('</td><td>',$data_headers).'</td></tr>'; foreach($data_array as $k=>$v){ $html_string.='<tr><td>'.implode('</td><td>',$v).'</td></tr>'; } $html_string.='</table>'; //make time dependent xls name that is always unique $xlsfile = "excel_example".date("m-d-Y-hiA").".xls"; //stop the browser displaying the HTML table displaying //force the browser to download as xcel document //if you make comment below two lines as php comments ,you see a simple HTML table header('Content-type: application/vnd.ms-excel'); header("Content-Disposition: attachment; filename=$xlsfile"); print $html_string;?> |
Second technique is just simple as we fetch the data from source like arrays or mysql table in simple tab delimitted text ,force the browser to download the file as xls file.When download completed we can open with MS Excel.
Code II
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | <?php /* This example demonstrates how to convert PHP array data to MS Excel 2003 data to download <br> user data base. Here we create a simple text format like CSV with a delimiter \t tab and new line delimiter for rows thats all */ $data_headers=array('First Name','Last Name','Date of Birth'); $data_array[]=array('Mahesh','Chari','25-08-1983'); $data_array[]=array('Chari','Mahesh','25-08-1980'); $data_array[]=array('Guggilla','Mahesh','25-08-1970'); $data_array[]=array('Mahesh','Guggilla','21-08-1985'); $data_array[]=array('Mahesh Chari','Guggilla','20-08-1950'); $data_array[]=array('Chari','Guggilla','25-08-1950'); // here we prepare a simple \t delimetered text format $delim_string=''; $delim_string.=implode("\t",$data_headers)."\n"; foreach($data_array as $k=>$v){ $delim_string.=implode("\t",$v)."\n"; } //make time dependent xls name that is always unique $xlsfile = "excel_example".date("m-d-Y-hiA").".xls"; //stop the browser displaying the data as text //force the browser to download as xcel document header('Content-type: application/vnd.ms-excel'); header("Content-Disposition: attachment; filename=$xlsfile"); print $delim_string; ?> |
To make advanced excel format like colors,cell formats,work book you can code with PEAR excel package.some opensource packages are available. please refer below for advanced excel convertion.
Download above Examples scripts
