Solved

Exporting a table to Excel

Posted on 2006-07-19
17
282 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:
smozgur 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:smozgur
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
 
LVL 17

Expert Comment

by:smozgur
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:smozgur
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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:smozgur
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:smozgur
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:smozgur
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:smozgur
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

Easy Project Management (No User Manual Required)

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now