Results 1 to 9 of 9

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

  1. #1
    Join Date
    Mar 2009
    Location
    Chennai, India
    Posts
    77
    Thanks
    16
    Thanked 7 Times in 6 Posts

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

    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...)

  2. #2
    Join Date
    Apr 2009
    Location
    Cognac, France
    Posts
    400
    Thanks
    2
    Thanked 57 Times in 57 Posts

    Default

    Hopefully this helps

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

    You can then import the CSV file into Excel

  3. #3
    Join Date
    Mar 2009
    Posts
    65
    Thanks
    13
    Thanked 4 Times in 4 Posts

    Default

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

  4. #4
    Join Date
    Mar 2009
    Location
    Chennai, India
    Posts
    77
    Thanks
    16
    Thanked 7 Times in 6 Posts

    Default

    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?

  5. #5
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,164
    Thanks
    265
    Thanked 690 Times in 678 Posts

    Default

    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.).
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  6. #6
    Join Date
    Mar 2009
    Location
    Chennai, India
    Posts
    77
    Thanks
    16
    Thanked 7 Times in 6 Posts

    Default

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

    Look at the following code:

    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:


    Code:
    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?
    Last edited by borris83; 04-15-2009 at 01:34 AM.

  7. #7
    Join Date
    Apr 2009
    Location
    Cognac, France
    Posts
    400
    Thanks
    2
    Thanked 57 Times in 57 Posts

    Default

    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

  8. #8
    Join Date
    Nov 2009
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

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

  9. #9
    Join Date
    Jan 2015
    Location
    Kampala Uganda
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Angry A Robust Tutorial on Exporting a dynamically developed table to Excell

    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!

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •