Lia Nungaray
asked on
Exporting a table to Excel
I found this nice piece of code on phpfreaks.com:
<?
header("Content-Type: application/vnd.ms-excel") ;
echo "<table border=1>
<tr>
<td>Cell 1</td>
<td>Cell 2</td>
</tr>
</table>";
?>
How can I run it when a user presses a button or image, after the table has been generated and the user is viewing it?
<?
header("Content-Type: application/vnd.ms-excel")
echo "<table border=1>
<tr>
<td>Cell 1</td>
<td>Cell 2</td>
</tr>
</table>";
?>
How can I run it when a user presses a button or image, after the table has been generated and the user is viewing it?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi, sorry I haven't posted yet smozgur, I'll try asap and let you know...
Sure, no problem. Take your time and let me know if you have any question.
Suat
Suat
ASKER
Will do. Thanks!
ASKER
Hi smozgur, the file is working great, doing exactly what I requested, I was just hoping that the code could be made for a group of different pages that have a table. Could this be done or do I have to create a second page for each page which contains a table?
ASKER
I changed your first piece of code, page.php and saved the table as $data. Can this variable, $data, somehow be posted in tabletoexcel.php without having to type the table again? This way, tabletoexcel will actually work for any php page that has a table that needs to be exported to excel. Thanks!
Here is the code:
<?php
$data="
<html>
<head>
<style>
td{border:1px solid #cccccc}
.header{background-color:# 333333;col or:#ffffff ;text-weig ht:bold}
</style>
</head>
<body>
<table>
<tr>
<td class=\"header\">ID</td>
<td class=\"header\">Letter</t d>
</tr>";
for ($i=65;$i<91;$i++)
{
$data.= "<tr><td>$i</td><td>" . chr($i) . "</td></tr>";
}
$data.="</table>
</body>
</html>";
echo $data;
/*
</body>
</html>
*/
?>
<a href="tabletoexcel.php"><i mg src="excel1.gif" border="0"/></a>
Here is the code:
<?php
$data="
<html>
<head>
<style>
td{border:1px solid #cccccc}
.header{background-color:#
</style>
</head>
<body>
<table>
<tr>
<td class=\"header\">ID</td>
<td class=\"header\">Letter</t
</tr>";
for ($i=65;$i<91;$i++)
{
$data.= "<tr><td>$i</td><td>" . chr($i) . "</td></tr>";
}
$data.="</table>
</body>
</html>";
echo $data;
/*
</body>
</html>
*/
?>
<a href="tabletoexcel.php"><i
Aren't your real php script creating all the tables by using a database?
Isn't it dynamic data?
I understand that you are trying to create a generic solution for all possible pages that includes table that could be downloaded.
The best method that I will suggest is calling the same php script that creates the HTML output with a parameter that makes it understand that it should spit out the table data as excel file instead outputting to the page.
Suat
Isn't it dynamic data?
I understand that you are trying to create a generic solution for all possible pages that includes table that could be downloaded.
The best method that I will suggest is calling the same php script that creates the HTML output with a parameter that makes it understand that it should spit out the table data as excel file instead outputting to the page.
Suat
ASKER
Yes to both questions. :-) How can I do this, calling the same PHP script that creates the table from another table? Sorry, I'm still pretty new at this PHP stuff, and HTML as well.
Save the following code as sample.php and open it in your browser. I think you will like it.
<?
//here you connect your database etc.. So you get the $data variable to send as output
//I used sample data instead creating a code that creates this output from the database dynamically
//All you need is storing the html code in the $data variable
$data="
<html>
<head>
<style>
td{border:1px solid #cccccc}
.header{background-color:# 333333;col or:#ffffff ;text-weig ht:bold}
</style>
</head>
<body>
<table>
<tr>
<td class=\"header\">ID</td>
<td class=\"header\">Letter</t d>
</tr>";
for ($i=65;$i<91;$i++)
{
$data.= "<tr><td>$i</td><td>" . chr($i) . "</td></tr>";
}
$data.="</table>
{DOWNLOADLINK}
</body>
</html>";
//Following is the place to decide to output as HTML or create the file instead.
//If requested parameter has been sent by the link to this php pave
//then it will set the headers so it will send it as "file" as in my sample
if (isset($_GET['xldownload'] ))
{
//Also checking xldownload parameter value to make sure it is set correctly
//Not necessary but good habit to expect a known value instead only checking for the existence
if (intval($_GET['xldownload' ])==1) {
//removing the {DOWNLOADLINK}, because this will not be shown in the HTML page
$data = str_replace("{DOWNLOADLINK }","",$dat a);
header('Content-type: application/vnd.ms-excel') ;
header('Content-Dispositio n: attachment; filename=tabledata.xls');
}
}
else
{
//Sending output as HTML to the client browser
//So we need to put a link for download
//We use the same script name however with xldownload=1 value (check out the href property of the anchor
$data = str_replace("{DOWNLOADLINK }","<a href=\"sample.php?xldownlo ad=1\">Dow nload File</a>",$data);
}
echo $data;
?>
However, I believe these HTML pages might be including more than this table. So all you need is to modify the code to include the necessary parts for different calls. For example, you can store table HTML code in a $table variable. And 2 different codes as HTML header and footer, so you can use $table with these variables to join them and send as $data when file download is requested. So you wouldn't need to create same table code twice. And put the same $table content into the REAL PAGE code as I did for {DOWNLOADLINK} above. Please let me know if what I mean here is complicated so I can try creating a sample instead.
I hope it helps.
Suat
<?
//here you connect your database etc.. So you get the $data variable to send as output
//I used sample data instead creating a code that creates this output from the database dynamically
//All you need is storing the html code in the $data variable
$data="
<html>
<head>
<style>
td{border:1px solid #cccccc}
.header{background-color:#
</style>
</head>
<body>
<table>
<tr>
<td class=\"header\">ID</td>
<td class=\"header\">Letter</t
</tr>";
for ($i=65;$i<91;$i++)
{
$data.= "<tr><td>$i</td><td>" . chr($i) . "</td></tr>";
}
$data.="</table>
{DOWNLOADLINK}
</body>
</html>";
//Following is the place to decide to output as HTML or create the file instead.
//If requested parameter has been sent by the link to this php pave
//then it will set the headers so it will send it as "file" as in my sample
if (isset($_GET['xldownload']
{
//Also checking xldownload parameter value to make sure it is set correctly
//Not necessary but good habit to expect a known value instead only checking for the existence
if (intval($_GET['xldownload'
//removing the {DOWNLOADLINK}, because this will not be shown in the HTML page
$data = str_replace("{DOWNLOADLINK
header('Content-type: application/vnd.ms-excel')
header('Content-Dispositio
}
}
else
{
//Sending output as HTML to the client browser
//So we need to put a link for download
//We use the same script name however with xldownload=1 value (check out the href property of the anchor
$data = str_replace("{DOWNLOADLINK
}
echo $data;
?>
However, I believe these HTML pages might be including more than this table. So all you need is to modify the code to include the necessary parts for different calls. For example, you can store table HTML code in a $table variable. And 2 different codes as HTML header and footer, so you can use $table with these variables to join them and send as $data when file download is requested. So you wouldn't need to create same table code twice. And put the same $table content into the REAL PAGE code as I did for {DOWNLOADLINK} above. Please let me know if what I mean here is complicated so I can try creating a sample instead.
I hope it helps.
Suat
ASKER
Works really nice! So I can just briefly adjust the code I currently have which contains tables to be downloaded. Let me try it...
Sure. Take your time and let me know if you have any problem.
Suat
Suat
ASKER
Thanks!
You're welcome!
Suat
Suat
ASKER
I'm having some trouble getting this to work on one of my pages...Allow me to explain how I have them set up. In order not to clog each php page with so many code, I broke each page into three: the main page, one which reads the results and another one with the queries. Each row contained in the table is a link to another page. Here's a piece of my original code:
switch ($parameterType) {
case "sglDate":
?>
<td><a href="ad_person_details.ph p?operator _id=<?=$op erator_id? >&date=<?= $selDate?> &operator= <?=$Operat or?>¶m eter=<?=$p arameterTy pe?>&page_ name=<?=$p age_name?> "title="Cl ick here for details for <?=$Operator?>"><?=$Operat or?></td>
<?php
break;
So as you can imagine, I have several types of parameters, for which a different query is run and different infomation is passed on to the second table which name is ad_person_details.php. I tried adapting your code to this long line, but I think this is where ai have the problem...Here's the same line, modified:
switch ($parameterType) {
case "sglDate":
$data.="<td><a href=\'ad_person_details.p hp operator_id=$operator_id&d ate=$selDa te&operato r=$Operato r¶mete r=$paramet erType&pag e_name=$pa ge_name\'t itle=\'Cli ck here for details for . $Operator\'>$Operator</td> ";
break;
Any help will be appreciated. Thanks!
switch ($parameterType) {
case "sglDate":
?>
<td><a href="ad_person_details.ph
<?php
break;
So as you can imagine, I have several types of parameters, for which a different query is run and different infomation is passed on to the second table which name is ad_person_details.php. I tried adapting your code to this long line, but I think this is where ai have the problem...Here's the same line, modified:
switch ($parameterType) {
case "sglDate":
$data.="<td><a href=\'ad_person_details.p
break;
Any help will be appreciated. Thanks!
Is it possible for your to send me the scripts to create a complete HTML, so I can look at the issue easier and try to produce the solution. You can get my email address in my profile.
Sorry for the late reply, it is GMT+2, so too late here: 03:20AM at the moment. But I'll look at it tomorrow latest.
Suat
Sorry for the late reply, it is GMT+2, so too late here: 03:20AM at the moment. But I'll look at it tomorrow latest.
Suat
ASKER
No problem, I really appreciate you taking your time for your responses. I'll do as suggested, no hurry by the way. My supervisor is thrilled that he can view reports online anytime, so being able to export these to excel is just icing on the cake... Thanks!
Correction is below:
1- page.php is same.
2- Second file should be named as tabletoexcel.php (not pagetoexcel.php as I commented in my first post).
// Start tabletoexcel.php (in the same folder with page.php) //
<?php
$data="
<html><head>
<style>
td{border:1px solid #cccccc}
.header{background-color:#
</style>
</head>
<body>
<table>
<tr>
<td class=\"header\">ID</td>
<td class=\"header\">Letter</t
</tr>";
for ($i=65;$i<91;$i++)
{
$data.= "<tr><td>$i</td><td>" . chr($i) . "</td></tr>";
}
$data.="</table>
</body>
</html>";
header('Content-type: application/vnd.ms-excel')
header('Content-Dispositio
echo $data
?>
// End tabletoexcel.php
Suat