How can I export a table created by PHP into an excel file?

[borris83]

How can I export a table created by PHP into an excel file? Is there any way?

(Also, I may be using more than 1 table in a page and I want to give the 'Export to excel" options only to specific table/tables...)

[forum_amnesiac]

Hopefully this helps

This link, http://www.electrictoolbox.com/create-csv-file-mysql-php/, explains how to export a CSV file from a database using PHP.

You can then import the CSV file into Excel

[CrazyChop]

You can also try PHPMyAdmin which has an export function to .csv

[borris83]

no, I actually want to do it this way:

Say I am querying the database is a php file and displaying results in a html table...

This html table will be dynamic depening on what the user is searching for. And the users should be able to save the html table as a excel file by clicking a button...

I think my question should be, 'how to export a html table to an excel file?'

Is there any option in php to do that?

[djr33]

The issue is not technical, but application-specific.
Of course you can alter the data (easily, too) with PHP into a new format.
But the problem is that format-- what format does excel use?
I'd suggest looking on google and if that doesn't work then looking at how open office does it, or something like that. Microsoft's formats are often complex and filled with excess formatting that isn't really relevant to the data but still required for it to be a valid spreadsheet in excel (or in word, etc.).

[borris83]

I have made some success but still need improvement...

Look at the following code:


<?php

if(isset($_GET['product']))
{

$key = $_GET['product'];

echo "<br /><br /><br />";

echo "<h2 style = \" text-align:center; \" > Total installations of " . ${$key} . "</h2>";
echo "<br /><br />";

$query_string = "SELECT * FROM inventory WHERE $key = 1";

$query = mysql_query($query_string);



if(!$query){ die(mysql_error()); }


$body = "";

$display = "<table border = \"1\" bgcolor = \"FFFFCC\" cellspacing= \"15\" cellpadding = \"7\">";
echo $display;
$body .= $display;


$display = "<thead style =\" font-weight:bold; color:green; \"><tr> <td>Name </td> <td>Cabin no</td> <td>Team</td><td> Service Tag</td> <td>System name</td> </tr></thead>";
echo $display;
$body .= $display;

while($result = mysql_fetch_array($query))
{
$display = "<tr>";

$display .= "<td><a href = \"search.php?searchfor=stagno&searchstring=" . $result['stagno'] . "\">";
$display .= $result['first_name'] . " " . $result['last_name'] . "</a></td>";

$display .= "<td>" .$result['cabin_no'] . "</td>";
$display .= "<td>" .$result['team'] . "</td>";
$display .= "<td>" .$result['stagno'] . "</td>";
$display .= "<td>" .$result['sname'] . "</td>";
$display .= "</tr>";
echo $display;
$body .= $display;

}


$display = "</table>";
echo $display;
$body .= $display;
$body = htmlspecialchars($body);

?>


<form action = "exp_to_excel.php" method = "post">
<input type = "hidden" name = "body" value = "<?php echo $body ; ?>">
<input type = "submit" name = "submit" Value = "Export to excel">
</form> <?php


}


?>





see that I have two variables $body and $display... $display is just for displaying the table to the current page but every piece of $display appends to $body.. Atlast $body is submitted to another page as a hidden value to another page called exp_to_excel.php..

Here is the code in exp_to_excel.php:



function setHeader($excel_file_name)//this function used to set the header variable
{

header("Content-type: application/octet-stream");//A MIME attachment with the content type "application/octet-stream" is a binary file.
//Typically, it will be an application or a document that must be opened in an application, such as a spreadsheet or word processor.
header("Content-Disposition: attachment; filename=$excel_file_name");//with this extension of file name you tell what kind of file it is.
header("Pragma: no-cache");//Prevent Caching
header("Expires: 0");//Expires and 0 mean that the browser will not cache the page on your hard drive



}

if(isset($_POST['submit']))
{
$body = $_POST['body'];
setHeader("inventory.xls");

echo $body;

}




This actually works great but there are two problems:

1) The button to 'export to excel' is at the bottom of the page because $body is getting built in each and every line and becomes complete only at the bottom... But I want the button to display above the table.

2) The table has some links, you notice that I have added some 'href' tags but I don't want them to be in the table that is exported..

Of course I can write the complete code twice and I can make the first one for building the $body and second one for just displaying the results in the same page... But is there any other efficient way? Will it be a smart programming if I repeat same kind of code twice in a page?

[forum_amnesiac]

This link is for exporting MYSQL into Excel in a single PHP file.

You may be able to use it as the action on your Submit


http://www.phpfreecode.com/export_mysql_to_excel.htm

[jude.fernando.1960]

you are almost there.
First show the form - then display the table.

[Melchisedec]

I have seen the solutions offered here but unfortunately they are quite different from my own problem. As for me my displayed Table is not based on filters but instead it is paginated. how do i export say the currently shown 4 rows to an excell file.

Thanks in advance!