Solved

Exporting a table to Excel

Posted on 2006-07-19
17
287 Views
Last Modified: 2008-03-10
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?
0
Comment
Question by:horalia
  • 9
  • 8
17 Comments
 
LVL 17

Accepted Solution

by:
Suat Ozgur earned 500 total points
ID: 17142119
Hi,

You can do this by executing another php page that will create the same table content to open as Excel document.

Sample: Say we have the following page that creates the web page including ASCII numbers and corresponding characters;

// Start page.php //
<html>
<head>
<style>
      td{border:1px solid #cccccc}
      .header{background-color:#333333;color:#ffffff;text-weight:bold}
</style>
</head>
<body>
<table>
<tr>
<td class="header">ID</td>
<td class="header">Letter</td>
</tr>
<?php
      for ($i=65;$i<91;$i++)
      {
            echo "<tr><td>$i</td><td>" . chr($i) . "</td></tr>";
      }
?>      
</table>
<a href="tabletoexcel.php"><img src="downloadimg.gif" border="0"/></a>
</body>
</html>
// End page.php //

I created a clickable image at the bottom and link to the tabletoexcel.php file that includes the following code:

// Start pagetoexcel.php (in the same folder with page.php) //
<?php
$data="
<html><head>
<style>
      td{border:1px solid #cccccc}
      .header{background-color:#333333;color:#ffffff;text-weight:bold}
</style>
</head>
<body>
<table>
<tr>
<td class=\"header\">ID</td>
<td class=\"header\">Letter</td>
</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-Disposition: attachment; filename=tabledata.xls');
echo $data
?>
// End pagetoexcel.php

You can also send parameters to return filtered/queried data by the link. Or you can even use <form> to send more complex data.

I hope it helps.

Suat
0
 
LVL 17

Expert Comment

by:Suat Ozgur
ID: 17142150
Oops: second file name should be tabletoexcel.php (or the link in the page.php should be changed as pagetoexcel.php).

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:#333333;color:#ffffff;text-weight:bold}
</style>
</head>
<body>
<table>
<tr>
<td class=\"header\">ID</td>
<td class=\"header\">Letter</td>
</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-Disposition: attachment; filename=tabledata.xls');
echo $data
?>
// End tabletoexcel.php


Suat
0
 

Author Comment

by:horalia
ID: 17155175
Hi, sorry I haven't posted yet smozgur, I'll try asap and let you know...
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 17

Expert Comment

by:Suat Ozgur
ID: 17155208
Sure, no problem. Take your time and let me know if you have any question.

Suat
0
 

Author Comment

by:horalia
ID: 17155230
Will do. Thanks!
0
 

Author Comment

by:horalia
ID: 17178511
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?
0
 

Author Comment

by:horalia
ID: 17178786
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;color:#ffffff;text-weight:bold}
</style>
</head>
<body>
<table>
<tr>
<td class=\"header\">ID</td>
<td class=\"header\">Letter</td>
</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"><img src="excel1.gif" border="0"/></a>
0
 
LVL 17

Expert Comment

by:Suat Ozgur
ID: 17179037
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

0
 

Author Comment

by:horalia
ID: 17179286
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.
0
 
LVL 17

Expert Comment

by:Suat Ozgur
ID: 17179572
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;color:#ffffff;text-weight:bold}
</style>
</head>
<body>
<table>
<tr>
<td class=\"header\">ID</td>
<td class=\"header\">Letter</td>
</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}","",$data);
    header('Content-type: application/vnd.ms-excel');
    header('Content-Disposition: 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?xldownload=1\">Download 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
0
 

Author Comment

by:horalia
ID: 17179648
Works really nice! So I can just briefly adjust the code I currently have which contains tables to be downloaded. Let me try it...
0
 
LVL 17

Expert Comment

by:Suat Ozgur
ID: 17179771
Sure. Take your time and let me know if you have any problem.

Suat
0
 

Author Comment

by:horalia
ID: 17179798
Thanks!
0
 
LVL 17

Expert Comment

by:Suat Ozgur
ID: 17179818
You're welcome!

Suat
0
 

Author Comment

by:horalia
ID: 17180121
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.php?operator_id=<?=$operator_id?>&date=<?=$selDate?>&operator=<?=$Operator?>&parameter=<?=$parameterType?>&page_name=<?=$page_name?>"title="Click here for details for <?=$Operator?>"><?=$Operator?></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.php operator_id=$operator_id&date=$selDate&operator=$Operator&parameter=$parameterType&page_name=$page_name\'title=\'Click here for details for . $Operator\'>$Operator</td>";
break;

Any help will be appreciated. Thanks!

0
 
LVL 17

Expert Comment

by:Suat Ozgur
ID: 17180645
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
0
 

Author Comment

by:horalia
ID: 17180715
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!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question