How do I export MySQL table to Excel with PHP?

Question

I am currently developing an App for my school to record Class Cleanliness Results for each class, so I need to convert the results collated in MySQL table into Microsoft Excel using PHP, preferably also able to be opened by a Android OS Phone.

I used the following PHP code:

<?PHP

$mysqli_user = "(user)";
$mysqli_password = "(password)";
$mysqli_host = "(host)";
$mysqli_database = "(database)";


  $filename = "grading_results_" . time() . ".xls";

  header("Content-Disposition: attachment; filename=\"$filename\"");
  header("Content-Type: application/vnd.ms-excel");

$link = mysqli_connect($mysqli_host,$mysqli_user,$mysqli_password,$mysqli_database);
$query = 'SELECT * FROM (table_name)';

$result = mysqli_query($link, $query);

while ($row = mysqli_fetch_row($result)){
 print implode("\t", $row) . "\n";
}
mysqli_close($link);
?>

This is how my table looks like in phpMyAdmin:

https://www.dropbox.com/s/7pr3gh06zta5d8u/Snip20150618_2.png?dl=0

This is how the Excel file looks like after I used this code to convert.

https://www.dropbox.com/s/571m9lfj64tklpc/Snip20150618_3.png?dl=0

Why is there no columns and rows? I need the Excel file to be exactly the same formatting and style as the table in phpMyAdmin. Can anyone help edit my code instead of providing me a brand new code?

Thanks in advance for your answers!

Deke · Accepted Answer · 2015-08-28 19:02:04Z

Manual

Run tour localhost and log in to phpMyAdmin
Click on your database then click on the table which you want to get Excel.

then

then press GO button

With Code

define ("DB_HOST", "localhost");
define ("DB_USER", "root");
define ("DB_PASS","");
define ("DB_NAME","DATABASE_NAME");

$link = mysql_connect(DB_HOST, DB_USER, DB_PASS) or die("Couldn't make connection.");
$db = mysql_select_db(DB_NAME, $link) or die("Couldn't select database");

then

$setCounter = 0;

$setExcelName = "download_excal_file";

$setSql = "YOUR SQL QUERY GOES HERE";

$setRec = mysql_query($setSql);

$setCounter = mysql_num_fields($setRec);

for ($i = 0; $i < $setCounter; $i++) {
    $setMainHeader .= mysql_field_name($setRec, $i)."\t";
}

while($rec = mysql_fetch_row($setRec))  {
  $rowLine = '';
  foreach($rec as $value)       {
    if(!isset($value) || $value == "")  {
      $value = "\t";
    }   else  {
//It escape all the special charactor, quotes from the data.
      $value = strip_tags(str_replace('"', '""', $value));
      $value = '"' . $value . '"' . "\t";
    }
    $rowLine .= $value;
  }
  $setData .= trim($rowLine)."\n";
}
  $setData = str_replace("\r", "", $setData);

if ($setData == "") {
  $setData = "no matching records found";
}

$setCounter = mysql_num_fields($setRec);



//This Header is used to make data download instead of display the data
 header("Content-type: application/octet-stream");

header("Content-Disposition: attachment; filename=".$setExcelName."_Report.xls");
header("Pragma: no-cache");
header("Expires: 0");

//It will print all the Table row as Excel file row with selected column name as header.
echo ucwords($setMainHeader)."\n".$setData."\n";

More About Code

Thanks, but I need to have a PHP link to download the Excel file, without phpMyAdmin
Thanks, but it doesn't work. dropbox.com/s/w4kyh9m3jqh1jdu/Snip20150619_3.png?dl=0

Krishna Mohan · Accepted Answer · 2015-06-18 06:19:41Z

0

SELECT id, name, email INTO OUTFILE '/tmp/ram.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ‘\\’
LINES TERMINATED BY '\n'
FROM users WHERE id=1

Here /tmp/ is address where u want to store the csv

edited Jun 18, 2015 at 6:19

Krishna Mohan

1,5233 gold badges22 silver badges29 bronze badges

answered Jun 18, 2015 at 5:57

ram singh

1496 bronze badges

1 Comment

Panda Over a year ago

Thanks, but I need an Excel file (.xls)

Patel Sameer · Accepted Answer · 2018-02-23 13:27:47Z

  <?php
    header( "Content-Type: application/vnd.ms-excel" );
    header( "Content-disposition: attachment; filename=spreadsheet.xls" );

    // print your data here. note the following:
    // - cells/columns are separated by tabs ("\t")
    // - rows are separated by newlines ("\n")

    // for example:
    echo 'First Name' . "\t" . 'Last Name' . "\t" . 'Phone' . "\n";
    echo 'John' . "\t" . 'Doe' . "\t" . '555-5555' . "\n";
?>

// You can fetch the data from the database and display in the table. Then put the table in echo to get Excel file.

That does not return an excel file, but a CSV file with the wrong content type

Collectives™ on Stack Overflow

How do I export MySQL table to Excel with PHP?

3 Answers 3

3 Comments

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

1 Comment

Related