Export MySQL data to Excel in PHP

Question

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

User · Answer

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

User · Answer

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- gt connect error       error check     die  Connection failed       con- gt 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- gt query  sql   while   property   mysqli fetch field  resultt       fetch table field name     echo  property- gt name   t      print   n         while  row   mysqli fetch row  resultt      fetch table data        schema insert           for  j 0   j lt  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

User · Answer

Try this code    lt  php     header  Content-type  application vnd-ms-excel         header  Content-Disposition  attachment  filename hasil-export xls         include  view-lap php     gt

User · Answer

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   lt table border  1  gt      make the column headers what you want in whatever order you want echo   lt tr gt  lt th gt Field Name 1 lt  th gt  lt th gt Field Name 2 lt  th gt  lt th gt Field Name 3 lt  th gt  lt  tr gt      loop the query data to the table in same order as the headers while   row   mysqli fetch assoc  result        echo   lt tr gt  lt td gt    row  field1     lt  td gt  lt td gt    row  field2     lt  td gt  lt td gt    row  field3     lt  td gt  lt  tr gt      echo   lt  table gt

User · Answer

PHPExcel is your friend  Very easy to use and works like a charm   https   github com PHPOffice PHPExcel

User · Answer

try this code  data php       lt table border  1  gt   lt tr gt       lt th gt NO  lt  th gt       lt th gt NAME lt  th gt       lt th gt Major lt  th gt   lt  tr gt   lt  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        lt tr gt           lt td gt    no   lt  td gt           lt td gt    data  name     lt  td gt           lt td gt    data  major     lt  td gt       lt  tr gt              no        gt      code for excel file  export php   lt  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     gt    if mysqli version   sql  SELECT   FROM user details    result mysqli query  conn  sql   if mysqli num rows  result   gt  0         no   1              while  data   mysqli fetch assoc  result                echo        lt tr gt           lt  lt td gt    no   lt  td gt           lt td gt    data  name     lt  td gt           lt td gt    data  major     lt  td gt        lt  tr gt              no      http   codelution com development web easy-ways-to-export-data-from-mysql-to-excel-with-php

User · Answer

Try the Following Code Please  just only update two values  1 your database name 2 table name    lt  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- gt 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               gt

User · Answer

Just Try With The Following    PHP Part     lt  php         EDIT LINES 3-8          DB Server    localhost     MySQL Server      DB Username    username     MySQL Username       DB Password    password                 MySQL Password       DB DBName    databasename             MySQL Database Name    DB TBLName    tablename     MySQL Table Name     filename    excelfilename             File Name         YOU DO NOT NEED TO EDIT ANYTHING BELOW THIS LINE               create MySQL connection     sql    Select   from  DB TBLName    Connect    mysql connect  DB Server   DB Username   DB Password  or die  Couldn t connect to MySQL  lt br gt     mysql error       lt br gt     mysql errno       select database     Db    mysql select db  DB DBName   Connect  or die  Couldn t select database  lt br gt     mysql error      lt br gt     mysql errno          execute query   result    mysql query  sql  Connect  or die  Couldn t execute query  lt br gt     mysql error      lt br gt     mysql errno          file ending    xls     header info for browser header  Content-Type  application xls        header  Content-Disposition  attachment  filename  filename xls      header  Pragma  no-cache     header  Expires  0            Start of Formatting for Excel              define separator  defines columns in excel  amp  tabs in word   sep     t     tabbed character   start of printing column names as names of MySQL fields for   i   0   i  lt  mysql num fields  result    i      echo mysql field name  result  i      t     print   n          end of printing column names     start while loop to get data     while  row   mysql fetch row  result                  schema insert               for  j 0   j lt mysql num fields  result   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              gt    I think this may help you to resolve your problem

User · Answer

Try this code  It s definitly  working    lt  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       gt

User · Answer

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

User · Answer

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   gt   x value       echo      x             x value         r n        Change to  csv  which Excel instantly updates to  xls and there is no error upon loading   Use the comma as delimiter  Double quote the Key and Value to escape any commas in the data  I also prepended column headers to  results so the spreadsheet looked even nicer

[php] Export MySQL data to Excel in PHP

Examples related to php

Examples related to mysql

Examples related to excel

Examples related to export