Cara konvert Database Ke file Excell Menggunakan Php

//lh4.googleusercontent.com/-eAXyxy2JJKA/AAAAAAAAAAI/AAAAAAAAGS4/OHgzqHzCvtQ/s512-c/photo.jpg
August 27, 2018




Kali ini kami akan share bagaimana Cara konvert Database Ke file Excell Menggunakan Php. Sebelum kita memulai ada beberapa hal yang mesti kita persiapkan di antaranya buat file dengan nama.



1. config.php
2. index.php
3. genrate-excel.php

Tetapi sebelumnya kita harus mempersiapkan database terlebih dahulu, Buat database dengan nama student, Kemudian masukan tabel dibawah ini.


 -- phpMyAdmin SQL Dump
-- version 4.7.7
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Aug 27, 2018 at 02:39 PM
-- Server version: 10.0.35-MariaDB
-- PHP Version: 5.6.30

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `armg3295_user`
--

-- --------------------------------------------------------

--
-- Table structure for table `tblstudent`
--

CREATE TABLE `tblstudent` (
  `id` int(11) NOT NULL,
  `fullName` varchar(120) NOT NULL,
  `emailId` varchar(150) NOT NULL,
  `phoneNumber` int(11) NOT NULL,
  `major` varchar(100) NOT NULL,
  `registDate` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tblstudent`
--

INSERT INTO `tblstudent` (`id`, `fullName`, `emailId`, `phoneNumber`, `major`, `registDate`) VALUES
(1, 'Annisa Dayumi', 'annisa@gmail.com', 81563786, 'INformatics Engineering', '2018-05-01'),
(2, 'Dede Ahmad', 'dedeahmad@gmail.com', 815243731, 'Perminyakan', '2018-08-12'),
(3, 'Lawang', 'lawan@gmail.com', 8156372, 'Manajemen', '2018-08-01'),
(4, 'Abidzar', 'abidzar@gmail.com', 12335, 'Akuntansi', '2018-07-01'),
(5, 'Arman', 'abi@gmail.com', 81452453, 'Informatics Engineering', '2018-05-01');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tblstudent`
--
ALTER TABLE `tblstudent`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tblstudent`
--
ALTER TABLE `tblstudent`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
 
Kedua buat file dengan nama config.php tujuannya sebagai koneksi kedatabse.

 <?php
// conection
define('DB_SERVER','localhost');
define('DB_USER','root');
define('DB_PASS' ,'');
define('DB_NAME', 'student');
$con = mysqli_connect(DB_SERVER,DB_USER,DB_PASS,DB_NAME);
// Check connection
if (mysqli_connect_errno())
{
 echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
?> 

Berikutnya buat file dengan nama index.php kemudian masukkan kodingan di bawah ini. File index berfungsi sebagai form untuk menampilkan data yang ada pada database.

 <?php
include('config.php');
?>
<!DOCTYPE html>
<html>
<head>
  <title>Export File</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
  
  <!--Script CSS-->
  <link type="text/css" href='https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css' rel='stylesheet'>
  <link type="text/css" href='https://cdn.datatables.net/responsive/2.2.1/css/responsive.dataTables.min.css' rel='stylesheet'>
  <link type="text/css" href='https://cdn.datatables.net/buttons/1.5.1/css/buttons.dataTables.min.css' rel='stylesheet'>
  
</head>
<body>
  <br /><br />
  <div class="container">
   <nav class="navbar navbar-inverse">
    <div class="container-fluid">
     <div class="navbar-header">
      <a class="navbar-brand" href="#">EXPORT FILE FROM DATABASE TO EXCEL USING PHP</a>
    </div>
  </div>
</nav>
<br />
<!--h2 align="center">EXPORT FILE</h2-->
<br />
<form method="post">
 <div class="form-group">
   
  <!--Script untuk memanggil Javascript-->
  <table id="example" class="display responsive nowrap" style="width:100%">
    <thead>
      <a class="btn btn-info" href="genrate-excel.php" role="button">Export To Excel</a>
      <br/><p/>
      <tr>
        <th>Sr.</th>
        <th>Name</th>
        <th>Email id</th>
        <th>Phone Number</th>
        <th>Department</th>
        <th>Joining Date</th>
      </tr>
    </thead>
    <tbody>
      <?php
      $query=mysqli_query($con,"select * from tblstudent");
      $cnt=1;
      while ($row=mysqli_fetch_array($query)) {

        ?>

        <tr>
          <td><?php echo $cnt;  ?></td>
          <td><?php echo $row['fullName']?></td>
          <td><?php echo $row['emailId']?></td>
          <td><?php echo $row['phoneNumber']?></td>
          <td><?php echo $row['major']?></td>
          <td><?php echo $row['registDate']?></td>
        </tr>
        <?php 
        $cnt++;
      } ?>
    </tbody>
  </table>
</div>
<div class="form-group">
</form>
</div>

<!--Script Javascript-->
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/responsive/2.2.1/js/dataTables.responsive.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.5.1/js/dataTables.buttons.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.colVis.min.js"></script>
<script>
 $(document).ready(function() {
  $('#example').DataTable( {
    dom: 'Bfrtip',
    buttons: [
    'colvis'
    ]
  } );
} );
</script>

</body>
</html> 

Berikutnya adalah buat file dengan nama genrate-excel.php file ini yang berfungsi mengenerate file yang ada pada database ke file excel. Pada file genrate ini koneksi databsenya memanggil nama tabel tertentu.

 <?php
/***** EDIT BELOW LINES *****/
  $DB_Server = "localhost"; // MySQL Server
  $DB_Username = "root"; // MySQL Username
  $DB_Password = ""; // MySQL Password
  $DB_DBName = "student"; // MySQL Database Name
  $DB_TBLName = "tblstudent"; // MySQL Table Name
  $xls_filename = 'export_'.date('Y-m-d').'.xls'; // Define Excel (.xls) file name
  
  /***** DO NOT EDIT BELOW LINES *****/
  // Create MySQL connection
  $sql = "Select * from $DB_TBLName";
  $Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Failed to connect to MySQL:<br />" . mysql_error() . "<br />" . mysql_errno());
  // Select database
  $Db = @mysql_select_db($DB_DBName, $Connect) or die("Failed to select database:<br />" . mysql_error(). "<br />" . mysql_errno());
  // Execute query
  $result = @mysql_query($sql,$Connect) or die("Failed to execute query:<br />" . mysql_error(). "<br />" . mysql_errno());
  
  // Header info settings
  header("Content-Type: application/xls");
  header("Content-Disposition: attachment; filename=$xls_filename");
  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<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<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";
  }
  ?> 


Sekian share kali ini, saya tidak menjelaskan satu per satu di karenakan setiap  kodingan di atas sudah mempunyai penjelasan tersendiri, tetapi jika kalian ingin bertanya silahkan tinggalkan komentar di bawah dan mulai berdiskusi dengan kami.


Related Post

SUBSCRIBE TO OUR NEWSLETTER

closed