Wednesday, August 26, 2015

How to : Export Data From Mysql To Excel (Spreadsheet) With PHP

Peace be upon you, This tutorial will demonstrate how to export / convert our PHP data set from MySQL into Excel spreadsheet document. I hope this tutorial knowledge will be useful for us. Without delay our time, let go through with our example. First we need to create a database, called "my_kripto_db" with one table called "users" and 3 column which is idusers, username and password. Just import and execute SQL file below:
CREATE DATABASE  IF NOT EXISTS `my_kripto_db`;
USE `my_kripto_db`;

DROP TABLE IF EXISTS `users`;

CREATE TABLE `users` (
  `idusers` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(45) DEFAULT NULL,
  `password` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`idusers`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

Lets insert a couple of dumb data into this database table :
INSERT INTO `users` VALUES (1,'Sera','nasikLemak1'),(2,'Mika','tehtarikkopi0');

We a done with database, and lets get our hand dirty by wrote our PHP code which I named as "data.php" which later will be use to query and display this data into tables when requested from web server by users.
<html>
<head>
 <title></title>
</head>
<body>
<table border="1">
    <tr>
     <th>No</th>
  <th>Username</th>
  <th>Password</th>
 </tr>
 <?php
 //connection to mysql
 mysql_connect("localhost", "root", ""); //server , username , password
 mysql_select_db("my_kripto_db");
 
 //query get data
 $sql = mysql_query("SELECT * FROM users ORDER BY idusers ASC");
 $no = 1;
 while($data = mysql_fetch_assoc($sql)){
  echo '
  <tr>
   <td>'.$no.'</td>
   <td>'.$data['username'].'</td>
   <td>'.$data['password'].'</td>
  </tr>
  ';
  $no++;
 }
 ?>
</table></body>
</html>

From this "data.php" table, we will export it into Excel spreadsheet document by sending a header function and let users download the file when this "data-set.php" are executed:
<?php
// Sending raw excel header 
header("Content-type: application/vnd-ms-excel");
 
// Defines the name of the export file "kriptonium-data.xls"
header("Content-Disposition: attachment; filename=kriptonium-data.xls");
 
// Add data table
include 'data.php';
?> 

Done and this is our result :

No comments:

Post a Comment