Sunday, December 5, 2010

PHP: Export excel sheet

Export dynamic data, images etc to excel in php.

Example 1

<?php
header("Expires: 0");
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
header("Cache-Control: no-store, no-cache, must-revalidate");
header("Cache-Control: post-check=0, pre-check=0", false);
header("Pragma: no-cache");
header("Content-type: application/vnd.ms-excel;charset:UTF-8");
header("Content-Disposition: attachment; filename=filename.xls");
print "\n"; // Add a line, unless excel error..
?>
<table border="1">
<tr>
<th>header 1</th>
<th>header 2</th>
</tr>
<tr>
<td>data 1</td>
<td><img src="photo.jpg"></td>
</tr>
</table>


Example 2

<?php
function cleanData(&$str) {
$str = preg_replace("/\t/", "\\t", $str);
$str = preg_replace("/\r?\n/", "\\n", $str);
if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
}

// database connection
$db = mysql_connect('localhost','root','');
mysql_select_db('Books');

// file name for download
$filename = "website_data.xls";
header("Content-Disposition: attachment; filename=\"$filename\"");
header("Content-Type: application/vnd.ms-excel");
$flag = false;
$result = mysql_query("SELECT id, name FROM users") or die('Query failed!');

echo "ID\tNAME\n";
while(false !== ($row = mysql_fetch_assoc($result))) {
if(!$flag) {
// display field/column names as first row
echo $row['id']."\t".$row['name']."\n";
$flag = true;
}
array_walk($row, 'cleanData');
echo $row['id']."\t".$row['name']."\n";
}
?>


Example 3

<?php
$filename ="excelreport.xls";
$contents = "testdata1 \t testdata2 \t testdata3 \t \n";
header('Content-type: application/ms-excel');
header('Content-Disposition: attachment; filename='.$filename);
echo $contents;
?>


http://www.gersh.no/posts/view/easy-excel-export-from-php
http://www.the-art-of-web.com/php/dataexport/
http://www.daniweb.com/forums/thread124300.html

1 comment:

  1. I could not get the image in the example 2. The script seems not to work . please help to be able to get a picture into excel. Thank's

    ReplyDelete