Export results to excel file

PHP
1

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

<?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

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

// 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/export-data-from-a-database-to-csv-excel-with-php/

3

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.

[google]mysql export to csv[/google]

[google]PHP Excel writer[/google]

4

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

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.