Reading and Writing Excel Spreadsheet Using PHP

Filed Under (General) by Wenbert on 04-04-2008

Tagged Under : ,

I have blogged about Microsoft Excel and PHP within the past few months. So a post from Zend Devzone caught my attention. It is about reading and writing into Excel using PHP. You can add a spreadsheet, do calculations and more. Click here for the post.

No Comments
Read More

Zend Framework: View File downloaded as Excel File

Filed Under (General) by Wenbert on 02-04-2008

Tagged Under : ,

This is basically a back-up post from my previous posts regarding exporting of data from a database to an excel file. Since Excel is able to read HTML tables, we can directly export a “view” file into an excel file by changing the headers of the request.

< ?php
header("Pragma: public");
header("Cache-Control: no-store, no-cache, must-revalidate");
header("Cache-Control: pre-check=0, post-check=0, max-age=0");
header("Pragma: no-cache");
header("Expires: 0");
header("Content-Transfer-Encoding: none");
header("Content-Type: application/vnd.ms-excel;");
header("Content-type: application/x-msexcel");
header("Content-Disposition: attachment; filename=report2_opendebitsummary".date(‘Ymd’).".xls");
?>

< html>
< body>
< table border="1">
< tr>
    < th>Employee ID< / th>
    < th>Employee Name< / th>
< /tr>
< ?php
foreach ($data AS $row) :
?>
< tr>
    < td>< ?=$row[‘employee_id’]?>< / td>
    < td>< ?=$row[‘employee_name’]?>< / td>
< / tr>
< ?php
endforeach;
?>
< / table>
< / body>
< / html>
 

It is important that you disable layouts and other stuff that will directly change the output of the view file.

No Comments
Read More

HTML Table to Excel File Tutorial

Filed Under (General) by Wenbert on 02-10-2007

Tagged Under : ,

Here is a tutorial on how to export something from your database to an excel file using PHP. It is useful when you want to download a report.

Basically, it displays an HTML table but instead of showing it in the browser, we have set the header to treat the output as an excel file.

$dbhost = ‘localhost’;
$dbuser = ‘root’;
$dbpass = ‘xxx’;

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die (‘Error connecting to mysql’);

$dbname = ‘employee’;
mysql_select_db($dbname);

$query  = ‘SELECT
            *
            FROM employee_table’;

$result = mysql_query($query);

header("Pragma: public");
header("Cache-Control: no-store, no-cache, must-revalidate");
header("Cache-Control: pre-check=0, post-check=0, max-age=0");
header("Pragma: no-cache");
header("Expires: 0");
header("Content-Transfer-Encoding: none");
header("Content-Type: application/vnd.ms-excel;");
header("Content-type: application/x-msexcel");
header("Content-Disposition: attachment; filename=my_excel_file.xls");

echo ‘
<table border="1">’;
echo ‘
<tbody>
<tr>’;
echo ‘
<td><strong>Firt Name</strong></td>
‘;
echo ‘
<td><strong>Last Name</strong></td>
‘;
echo ‘
<td><strong>Username</strong></td>
‘;
echo ‘
<td><strong>Date Hired</strong></td>
‘;
echo ‘
<td><strong>Marital Status</strong></td>
‘;

while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    echo ‘</tr>
<tr>’;
    echo ‘
<td>’.$row[‘firstname’].‘</td>
‘;
    echo ‘
<td>’.$row[‘lastname’].‘</td>
‘;
    echo ‘
<td>’.$row[‘username’].‘</td>
‘;
    echo ‘
<td>’.$row[‘datehired’].‘</td>
‘;
    echo ‘
<td>’.$row[‘maritalstatus’].‘</td>
‘;
    echo ‘</tr>
‘;
}

echo ‘</tbody></table>
‘;

No Comments
Read More
Subscribe to Rss Feed : Rss