CODEWITHSUNDEEP
phpmysqlexcel
Panda
Panda

Reputation: 6896

How do I export MySQL table to Excel with PHP?

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!

Upvotes: 0

Views: 9247

Answers (3)

Patel Sameer
Patel Sameer

Reputation: 1

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

Upvotes: -1

Abdulla Nilam
Abdulla Nilam

Reputation: 38584

Manual

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

then

  1. enter image description here
  2. 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

Upvotes: 2

ram singh
ram singh

Reputation: 140

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

Upvotes: 0

Related Questions