SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    export results to excel file

    How can I export the results of PHP script in Excel file


    Code PHP:
    <?php
    $sql="select * from employee where emp_id<6000  order by emp_id asc";
    $result=mysql_query($sql);
     
    while($rows=mysql_fetch_array($result))
    {
             echo $rows['name'];
     
     
    }
    ?>

  2. #2
    SitePoint Enthusiast
    Join Date
    Oct 2011
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This sample script demonstrates how to create a CSV file with PHP. CSV files are text-only spreadsheet files that can be opened in Excel. If you want to create an actual XLS or XLSX file, you should look up the PHPExcel library and use that to build your file. The library comes with enough documentation to get you well on your way.

    PHP Code:
    <?php
     
    // Connect and query the database for the users
    $conn = new PDO("mysql:host=localhost;dbname=mydatabase"'myuser''mypassword');
    $sql "SELECT username, email FROM users ORDER BY username";
    $results $conn->query($sql);
     
    // Pick a filename and destination directory for the file
    // Remember that the folder where you want to write the file has to be writable
    $filename "/tmp/db_user_export_".time().".csv";
     
    // Actually create the file
    // The w+ parameter will wipe out and overwrite any existing file with the same name
    $handle fopen($filename'w+');
     
    // Write the spreadsheet column titles / labels
    fputcsv($handle, array('Username','Email'));
     
    // Write all the user records to the spreadsheet
    foreach($results as $row)
    {
        
    fputcsv($handle, array($row['username'], $row['email']));
    }
     
    // Finish writing the file
    fclose($handle);
     
    ?>
    The code was originally posted at:
    http://dev.strategystar.net/2012/01/...xcel-with-php/

  3. #3
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Excel can import a csv file and Mysql can export the results of an sql select statement to a .csv file, and write the file for you -- if you have the correct permissions.

    Php can tell Mysql to run that select statement.

    There are also various Excel libraries that you can use which enable PHP to export a result set as an .xls file.

    mysql export to csv

    PHP Excel writer

  4. #4
    SitePoint Enthusiast
    Join Date
    Oct 2011
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Cups View Post
    Excel can import a csv file and Mysql can export the results of an sql select statement to a .csv file, and write the file for you -- if you have the correct permissions.

    Php can tell Mysql to run that select statement.
    That is the easiest way to do it if you do not need to manipulate the data before writing it to the file, but with the command line method you need access to execute command line scripts. Many shared hosts do not provide you with that capability, but it is definitely a worthy option to consider.

  5. #5
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Many shared hosts do not provide you with that capability
    Yeah, that was the point I failed to state.

    But sometimes you just want a quick and dirty one off dump to excel, so thought I'd chip in with those other ideas -- not taking away from your great example of how to do it using PHP.


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
  •