I'm trying to get my MySQL data to Excel file, but I'm having problems with Excel cells. All my text goes to one cell, I would like to have each row value in separate Excel cell. Here is my code:
$queryexport = ("
SELECT username,password,fullname FROM ecustomer_users
WHERE fk_customer='".$fk_customer."'
");
$row = mysql_fetch_assoc($queryexport);
$result = mysql_query($queryexport);
$header = '';
for ($i = 0; $i < $count; $i++){
$header .= mysql_field_name($result, $i)."\t";
}
while($row = mysql_fetch_row($result)){
$line = '';
foreach($row as $value){
if(!isset($value) || $value == ""){
$value = "\t";
}else{
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
$data = str_replace("\r", "", $data);
if ($data == "") {
$data = "\nno matching records found\n";
}
}
header("Content-type: application/vnd.ms-excel; name='excel'");
header("Content-Disposition: attachment; filename=exportfile.xls");
header("Pragma: no-cache");
header("Expires: 0");
// output data
echo $header."\n".$data;
mysql_close($conn);`
PHPExcel is your friend. Very easy to use and works like a charm.
If you just want your query data dumped into excel I have to do this frequently and using an html table is a very simple method. I use mysqli for db queries and the following code for exports to excel:
header("Content-Type: application/xls");
header("Content-Disposition: attachment; filename=filename.xls");
header("Pragma: no-cache");
header("Expires: 0");
echo '<table border="1">';
//make the column headers what you want in whatever order you want
echo '<tr><th>Field Name 1</th><th>Field Name 2</th><th>Field Name 3</th></tr>';
//loop the query data to the table in same order as the headers
while ($row = mysqli_fetch_assoc($result)){
echo "<tr><td>".$row['field1']."</td><td>".$row['field2']."</td><td>".$row['field3']."</td></tr>";
}
echo '</table>';
try this code
data.php
<table border="1">
<tr>
<th>NO.</th>
<th>NAME</th>
<th>Major</th>
</tr>
<?php
//connection to mysql
mysql_connect("localhost", "root", ""); //server , username , password
mysql_select_db("codelution");
//query get data
$sql = mysql_query("SELECT * FROM student ORDER BY id ASC");
$no = 1;
while($data = mysql_fetch_assoc($sql)){
echo '
<tr>
<td>'.$no.'</td>
<td>'.$data['name'].'</td>
<td>'.$data['major'].'</td>
</tr>
';
$no++;
}
?>
code for excel file
export.php
<?php
// The function header by sending raw excel
header("Content-type: application/vnd-ms-excel");
// Defines the name of the export file "codelution-export.xls"
header("Content-Disposition: attachment; filename=codelution-export.xls");
// Add data table
include 'data.php';
?>
if mysqli version
$sql="SELECT * FROM user_details";
$result=mysqli_query($conn,$sql);
if(mysqli_num_rows($result) > 0)
{
$no = 1;
while($data = mysqli_fetch_assoc($result))
{echo '
<tr>
<<td>'.$no.'</td>
<td>'.$data['name'].'</td>
<td>'.$data['major'].'</td>
</tr>
';
$no++;
http://codelution.com/development/web/easy-ways-to-export-data-from-mysql-to-excel-with-php/
This is new version of php code
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "your_dbname";
//mysql and db connection
$con = new mysqli($servername, $username, $password, $dbname);
if ($con->connect_error) { //error check
die("Connection failed: " . $con->connect_error);
}
else
{
}
$DB_TBLName = "your_table_name";
$filename = "excelfilename"; //your_file_name
$file_ending = "xls"; //file_extention
header("Content-Type: application/xls");
header("Content-Disposition: attachment; filename=$filename.'.'.$file_ending");
header("Pragma: no-cache");
header("Expires: 0");
$sep = "\t";
$sql="SELECT * FROM $DB_TBLName";
$resultt = $con->query($sql);
while ($property = mysqli_fetch_field($resultt)) { //fetch table field name
echo $property->name."\t";
}
print("\n");
while($row = mysqli_fetch_row($resultt)) //fetch_table_data
{
$schema_insert = "";
for($j=0; $j< mysqli_num_fields($resultt);$j++)
{
if(!isset($row[$j]))
$schema_insert .= "NULL".$sep;
elseif ($row[$j] != "")
$schema_insert .= "$row[$j]".$sep;
else
$schema_insert .= "".$sep;
}
$schema_insert = str_replace($sep."$", "", $schema_insert);
$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
$schema_insert .= "\t";
print(trim($schema_insert));
print "\n";
}
Posts by John Peter and Dileep kurahe helped me to develop what I consider as being a simpler and cleaner solution, just in case anyone else is still looking. (I am not showing any database code because I actually used a $_SESSION variable.)
The above solutions invariably caused an error upon loading in Excel, about the extension not matching the formatting type. And some of these solutions create a spreadsheet with the data across the page in columns where it would be more traditional to have column headings and list the data down the rows. So here is my simple solution:
$filename = "webreport.csv";
header("Content-Type: application/xls");
header("Content-Disposition: attachment; filename=$filename");
header("Pragma: no-cache");
header("Expires: 0");
foreach($results as $x => $x_value){
echo '"'.$x.'",' . '"'.$x_value.'"' . "\r\n";
}
$results so the spreadsheet looked even nicer.You can export the data from MySQL to Excel by using this simple code.
<?php
include('db_con.php');
$stmt=$db_con->prepare('select * from books');
$stmt->execute();
$columnHeader ='';
$columnHeader = "Sr NO"."\t"."Book Name"."\t"."Book Author"."\t"."Book
ISBN"."\t";
$setData='';
while($rec =$stmt->FETCH(PDO::FETCH_ASSOC))
{
$rowData = '';
foreach($rec as $value)
{
$value = '"' . $value . '"' . "\t";
$rowData .= $value;
}
$setData .= trim($rowData)."\n";
}
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=Book record
sheet.xls");
header("Pragma: no-cache");
header("Expires: 0");
echo ucwords($columnHeader)."\n".$setData."\n";
?>
complete code here php export to excel
Try this code:
<?php
header("Content-type: application/vnd-ms-excel");
header("Content-Disposition: attachment; filename=hasil-export.xls");
include 'view-lap.php';
?>
I think you should try with this API
http://code.google.com/p/php-excel/source/browse/trunk/php-excel.class.php
With This
Create a quick export from a database table into Excel
Compile some statistical records with a few calculations and deliver
the result in an Excel worksheet
Gather the items off your (web-based) todo list, put them in a
worksheet and use it as a foundation for some more statistics
magic.**
Try this code. It's definitly working.
<?php
// Connection
$conn=mysql_connect('localhost','root','');
$db=mysql_select_db('excel',$conn);
$filename = "Webinfopen.xls"; // File Name
// Download file
header("Content-Disposition: attachment; filename=\"$filename\"");
header("Content-Type: application/vnd.ms-excel");
$user_query = mysql_query('select name,work from info');
// Write data to file
$flag = false;
while ($row = mysql_fetch_assoc($user_query)) {
if (!$flag) {
// display field/column names as first row
echo implode("\t", array_keys($row)) . "\r\n";
$flag = true;
}
echo implode("\t", array_values($row)) . "\r\n";
}
?>
Try the Following Code Please.
just only update two values.
1.your_database_name
2.table_name
<?php
$host="localhost";
$username="root";
$password="";
$dbname="your_database_name";
$con = new mysqli($host, $username, $password,$dbname);
$sql_data="select * from table_name";
$result_data=$con->query($sql_data);
$results=array();
filename = "Webinfopen.xls"; // File Name
// Download file
header("Content-Disposition: attachment; filename=\"$filename\"");
header("Content-Type: application/vnd.ms-excel");
$flag = false;
while ($row = mysqli_fetch_assoc($result_data)) {
if (!$flag) {
// display field/column names as first row
echo implode("\t", array_keys($row)) . "\r\n";
$flag = true;
}
echo implode("\t", array_values($row)) . "\r\n";
}
?>
Source: Stackoverflow.com