Sabtu, 10 Desember 2016

Cara Membuat Import Data Excel dengan PHP dan MySQL

     3 comments

Catatan kali ini akan membahas mengenai cara membuat import data dari file excel dengan menggunakan PHP dan database MySQL. Pada catatan ini, kita akan menggunakan sebuah librari yaitu PHPExcel. Librari ini yang nantinya akan kita gunakan untuk mebaca isi dari file excel yang akan kita import ke database. Ikuti langkah-langkahnya berikut ini.
Berikut adalah fitur-fitur yang akan kita buat:
  1. View Data dari Database.
  2. Form untuk Import Data.
  3. Preview Data yang akan diimport sebelum di simpan ke database.
  4. Validasi terhadap file yang dipilih oleh User pada form import. Hanya file Microsoft Office 2007 (.xlsx) yang diperbolehkan.
  5. Validasi terhadap input data. Jika ada data yang kosong / belum diisi, sistem akan memberitahu jumlah data yang kosong dan data mana saja.
DEMOSebelum masuk ke tutorialnya. Mungkin ada yang mau lihat demonya terlebih dahulu. Silahkan klik link berikut untuk melihat demonya : Lihat Demo.
STEP 1 – PERSIAPAN

Berikut ini adalah hal-hal yang harus dipersiapkan :

  1. Download Bootstrap, Klik link berikut : Download.
  2. Download Jquery, Klik link berikut : Download.
  3. Download librari PHPExcel nya, klik link berikut ini : Download.
  4. Download format import data excel, klik link berikut : Download. File ini berfungsi sebagai format excel yang digunakan untuk import data.
  5. Buat sebuah folder dengan nama import_php, lalu simpan pada folder xampp/htdocs/.
  6. Buat sebuah folder dengan nama tmp, lalu simpan pada folder xampp/htdocs/import_php/.
  7. Buat sebuah folder dengan nama css, lalu simpan pada folder xampp/htdocs/import_php/.
  8. Buat sebuah folder dengan nama js, lalu simpan pada folder xampp/htdocs/import_php/.
  9. Esktrak file bootstrap.7z yang telah didownload tadi.
  10. Copy and paste folder fonts dari folder bootstrap yang telah diekstrak tadi ke folder xampp/htdocs/import_php/.
  11. Copy and paste file bootstrap.min.css dari folder bootstrap/css/ yang telah diekstrak tadi ke folder xampp/htdocs/import_php/css/.
  12. Rename file JQUERY yang telah di downlaod tadi menjadi jquery.min.js, lalu copy and paste pada folder xampp/htdocs/import_php/js/.
  13. Ekstrak file librari PHPExcel.7z yang telah didownload tadi.
  14. Copy and paste folder PHPExcel hasil ekstrak file tadi ke folder xampp/htdocs/import_php/.
  15. Copy and paste file Format.xlsx yang telah didownload ke folder xampp/htdocs/import_php/.

STEP 2 – DATABASE

Pada step ini, kita akan membuat databasenya. ikuti langkah-langkah berikut ini :
  1. Buat sebuah database dengan nama mynotescode.
  2. Buat sebuah tabel dengan nama siswa. Struktur tabelnya sebagai berikut :
    1. CREATE TABLE IF NOT EXISTS `siswa` (
    2. `nis` varchar(11) NOT NULL,
    3. `nama` varchar(50) NOT NULL,
    4. `jenis_kelamin` varchar(10) NOT NULL,
    5. `telp` varchar(15) NOT NULL,
    6. `alamat` text NOT NULL,
    7. PRIMARY KEY (`nis`)
    8. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

STEP 3 – KONEKSI DATABASE

Pada step ini, kita akan membuat file php yang berfungsi untuk menghubungkan dengan database MySQL. Silahkan buat file koneksi.php, lalu simpan di folder xampp/htdocs/import_php/. Berikut ini kodenya :
  1. <?php
  2. $host = 'localhost'; // Nama hostnya
  3. $username = 'root'; // Username
  4. $password = ''; // Password (Isi jika menggunakan password)
  5. $database = 'mynotescode'; // Nama databasenya
  7. // Koneksi ke MySQL dengan PDO
  8. $pdo = new PDO('mysql:host='.$host.';dbname='.$database, $username, $password);
  9. ?>
STEP 4 – VIEW DATASelanjutnya kita akan membuat sebuah file php yang berfungsi untuk menampilkan data hasil import data dari database. Buat sebuah file baru dengan nama index.php, lalu simpan pada folder xampp/htdocs/import_php/. Berikut ini tampilan dan kodenya :
  1. <!DOCTYPE html>
  2. <html lang="en">
  3. <head>
  4. <meta charset="utf-8">
  5. <meta http-equiv="X-UA-Compatible" content="IE=edge">
  6. <meta name="viewport" content="width=device-width, initial-scale=1">
  7. <title>Import Data dengan PHP</title>
  9. <!-- Load File bootstrap.min.css yang ada difolder css -->
  10. <link href="css/bootstrap.min.css" rel="stylesheet">
  12. <!-- Style untuk Loading -->
  13. <style>
  14. #loading{
  15. background: whitesmoke;
  16. position: absolute;
  17. top: 140px;
  18. left: 82px;
  19. padding: 5px 10px;
  20. border: 1px solid #ccc;
  21. }
  22. </style>
  23. </head>
  24. <body>
  25. <!-- Membuat Menu Header / Navbar -->
  26. <nav class="navbar navbar-inverse" role="navigation">
  27. <div class="container-fluid">
  28. <div class="navbar-header">
  29. <a class="navbar-brand" href="#" style="color: white;"><b>Import Data dengan PHP</b></a>
  30. </div>
  31. <p class="navbar-text navbar-right hidden-xs" style="color: white;padding-right: 10px;">
  32. FOLLOW US ON
  33. <a target="_blank" style="background: #3b5998; padding: 0 5px; border-radius: 4px; color: #f7f7f7; text-decoration: none;" href="https://www.facebook.com/mynotescode">Facebook</a>
  34. <a target="_blank" style="background: #00aced; padding: 0 5px; border-radius: 4px; color: #ffffff; text-decoration: none;" href="https://twitter.com/code_notes">Twitter</a>
  35. <a target="_blank" style="background: #d34836; padding: 0 5px; border-radius: 4px; color: #ffffff; text-decoration: none;" href="https://plus.google.com/118319575543333993544">Google+</a>
  36. </p>
  37. </div>
  38. </nav>
  40. <!-- Content -->
  41. <div style="padding: 0 15px;">
  42. <!--
  43. -- Buat sebuah tombol untuk mengarahkan ke form import data
  44. -- Tambahkan class btn agar terlihat seperti tombol
  45. -- Tambahkan class btn-success untuk tombol warna hijau
  46. -- class pull-right agar posisi link berada di sebelah kanan
  47. -->
  48. <a href="form.php" class="btn btn-success pull-right">
  49. <span class="glyphicon glyphicon-upload"></span> Import Data
  50. </a>
  52. <h3>Data Hasil Import</h3>
  54. <hr>
  56. <!-- Buat sebuah div dan beri class table-responsive agar tabel jadi responsive -->
  57. <div class="table-responsive">
  58. <table class="table table-bordered">
  59. <tr>
  60. <th>No</th>
  61. <th>NIS</th>
  62. <th>Nama</th>
  63. <th>Jenis Kelamin</th>
  64. <th>Telepon</th>
  65. <th>Alamat</th>
  66. </tr>
  67. <?php
  68. // Load file koneksi.php
  69. include "koneksi.php";
  71. // Buat query untuk menampilkan semua data siswa
  72. $sql = $pdo->prepare("SELECT * FROM siswa");
  73. $sql->execute(); // Eksekusi querynya
  75. $no = 1; // Untuk penomoran tabel, di awal set dengan 1
  76. while($data = $sql->fetch()){ // Ambil semua data dari hasil eksekusi $sql
  77. echo "<tr>";
  78. echo "<td>".$no."</td>";
  79. echo "<td>".$data['nis']."</td>";
  80. echo "<td>".$data['nama']."</td>";
  81. echo "<td>".$data['jenis_kelamin']."</td>";
  82. echo "<td>".$data['telp']."</td>";
  83. echo "<td>".$data['alamat']."</td>";
  84. echo "</tr>";
  86. $no++; // Tambah 1 setiap kali looping
  87. }
  88. ?>
  89. </table>
  90. </div>
  91. </div>
  92. </body>
  93. </html>
Saya akan coba jelaskan sedikit mengenai kode diatas.
include “koneksi.php”;
Kode tersebut berfungsi untuk me-load file koneksi.php.
$sql = $pdo->prepare(“SELECT * FROM siswa”);
$sql->execute();
Kode tersebut berfungsi untuk melakukan query ke database dan mengeksekusinya. Pada cotoh diatas, kita akan melakukan query untuk menampilkan semua data dari tabel siswa.
$data = $sql->fetch()
Kode tersebut berfungsi untuk mengambil semua data hasil query dan menampung data-data tersebut di dalam sebuah array lalu menyimpannya ke dalam variabel $data. Kode tersebut saya simpan di dalam sebuah kode while(….) yang berfungsi untuk melakukan proses perulangan sampai data terkahir dengan tujuan agar kita bisa menampilkan datanya satu per satu.
  1. echo "<tr>";
  2. echo "<td>".$no."</td>";
  3. echo "<td>".$data['nis']."</td>";
  4. echo "<td>".$data['nama']."</td>";
  5. echo "<td>".$data['jenis_kelamin']."</td>";
  6. echo "<td>".$data['telp']."</td>";
  7. echo "<td>".$data['alamat']."</td>";
  8. echo "</tr>";
Pada kode diatas terdapat variabel $data digunakan untuk mengambil isi dari field-field yang ada pada tabel siswa di database mynotescode. Pada kode diatas juga ada kode yang berada pada tanda [‘……’], kode tersebut harus sama dengan nama field yang ada pada tabel siswa.
STEP 5 – FORM DAN PREVIEWL

angkah selanjutnya kita akan membuat sebuah form untuk import data excelnya. Buat sebuah file baru dengan nama form.php, lalu simpan pada folder xampp/htdocs/import_php/. Berikut tampilan dan kodenya :
  1. <!DOCTYPE html>
  2. <html lang="en">
  3. <head>
  4. <meta charset="utf-8">
  5. <meta http-equiv="X-UA-Compatible" content="IE=edge">
  6. <meta name="viewport" content="width=device-width, initial-scale=1">
  7. <title>Import Data Excel dengan PHP</title>
  9. <!-- Load File bootstrap.min.css yang ada difolder css -->
  10. <link href="css/bootstrap.min.css" rel="stylesheet">
  12. <!-- Style untuk Loading -->
  13. <style>
  14. #loading{
  15. background: whitesmoke;
  16. position: absolute;
  17. top: 140px;
  18. left: 82px;
  19. padding: 5px 10px;
  20. border: 1px solid #ccc;
  21. }
  22. </style>
  24. <!-- Load File jquery.min.js yang ada difolder js -->
  25. <script src="js/jquery.min.js"></script>
  27. <script>
  28. $(document).ready(function(){
  29. // Sembunyikan alert validasi kosong
  30. $("#kosong").hide();
  31. });
  32. </script>
  33. </head>
  34. <body>
  35. <!-- Membuat Menu Header / Navbar -->
  36. <nav class="navbar navbar-inverse" role="navigation">
  37. <div class="container-fluid">
  38. <div class="navbar-header">
  39. <a class="navbar-brand" href="#" style="color: white;"><b>Import Data Excel dengan PHP</b></a>
  40. </div>
  41. <p class="navbar-text navbar-right hidden-xs" style="color: white;padding-right: 10px;">
  42. FOLLOW US ON
  43. <a target="_blank" style="background: #3b5998; padding: 0 5px; border-radius: 4px; color: #f7f7f7; text-decoration: none;" href="https://www.facebook.com/mynotescode">Facebook</a>
  44. <a target="_blank" style="background: #00aced; padding: 0 5px; border-radius: 4px; color: #ffffff; text-decoration: none;" href="https://twitter.com/code_notes">Twitter</a>
  45. <a target="_blank" style="background: #d34836; padding: 0 5px; border-radius: 4px; color: #ffffff; text-decoration: none;" href="https://plus.google.com/118319575543333993544">Google+</a>
  46. </p>
  47. </div>
  48. </nav>
  50. <!-- Content -->
  51. <div style="padding: 0 15px;">
  52. <!-- Buat sebuah tombol Cancel untuk kemabli ke halaman awal / view data -->
  53. <a href="index.php" class="btn btn-danger pull-right">
  54. <span class="glyphicon glyphicon-remove"></span> Cancel
  55. </a>
  57. <h3>Form Import Data</h3>
  58. <hr>
  60. <!-- Buat sebuah tag form dan arahkan action nya ke file ini lagi -->
  61. <form method="post" action="" enctype="multipart/form-data">
  62. <a href="Format.xlsx" class="btn btn-default">
  63. <span class="glyphicon glyphicon-download"></span>
  64. Download Format
  65. </a><br><br>
  67. <!--
  68. -- Buat sebuah input type file
  69. -- class pull-left berfungsi agar file input berada di sebelah kiri
  70. -->
  71. <input type="file" name="file" class="pull-left">
  73. <button type="submit" name="preview" class="btn btn-success btn-sm">
  74. <span class="glyphicon glyphicon-eye-open"></span> Preview
  75. </button>
  76. </form>
  78. <hr>
  80. <!-- Buat Preview Data -->
  81. <?php
  82. // Jika user telah mengklik tombol Preview
  83. if(isset($_POST['preview'])){
  84. //$ip = ; // Ambil IP Address dari User
  85. $nama_file_baru = 'data.xlsx';
  87. // Cek apakah terdapat file data.xlsx pada folder tmp
  88. if(is_file('tmp/'.$nama_file_baru)) // Jika file tersebut ada
  89. unlink('tmp/'.$nama_file_baru); // Hapus file tersebut
  91. $tipe_file = $_FILES['file']['type']; // Ambil tipe file yang akan diupload
  92. $tmp_file = $_FILES['file']['tmp_name'];
  94. // Cek apakah file yang diupload adalah file Excel 2007 (.xlsx)
  95. if($tipe_file == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"){
  96. // Upload file yang dipilih ke folder tmp
  97. // dan rename file tersebut menjadi data{ip_address}.xlsx
  98. // {ip_address} diganti jadi ip address user yang ada di variabel $ip
  99. // Contoh nama file setelah di rename : data127.0.0.1.xlsx
  100. move_uploaded_file($tmp_file, 'tmp/'.$nama_file_baru);
  102. // Load librari PHPExcel nya
  103. require_once 'PHPExcel/PHPExcel.php';
  105. $excelreader = new PHPExcel_Reader_Excel2007();
  106. $loadexcel = $excelreader->load('tmp/'.$nama_file_baru); // Load file yang tadi diupload ke folder tmp
  107. $sheet = $loadexcel->getActiveSheet()->toArray(null, true, true ,true);
  109. // Buat sebuah tag form untuk proses import data ke database
  110. echo "<form method='post' action='import.php'>";
  112. // Buat sebuah div untuk alert validasi kosong
  113. echo "<div class='alert alert-danger' id='kosong'>
  114. Semua data belum diisi, Ada <span id='jumlah_kosong'></span> data yang belum diisi.
  115. </div>";
  117. echo "<table class='table table-bordered'>
  118. <tr>
  119. <th colspan='5' class='text-center'>Preview Data</th>
  120. </tr>
  121. <tr>
  122. <th>NIS</th>
  123. <th>Nama</th>
  124. <th>Jenis Kelamin</th>
  125. <th>Telepon</th>
  126. <th>Alamat</th>
  127. </tr>";
  129. $numrow = 1;
  130. $kosong = 0;
  131. foreach($sheet as $row){ // Lakukan perulangan dari data yang ada di excel
  132. // Ambil data pada excel sesuai Kolom
  133. $nis = $row['A']; // Ambil data NIS
  134. $nama = $row['B']; // Ambil data nama
  135. $jenis_kelamin = $row['C']; // Ambil data jenis kelamin
  136. $telp = $row['D']; // Ambil data telepon
  137. $alamat = $row['E']; // Ambil data alamat
  139. // Cek jika semua data tidak diisi
  140. if(empty($nis) && empty($nama) && empty($jenis_kelamin) && empty($telp) && empty($alamat))
  141. continue; // Lewat data pada baris ini (masuk ke looping selanjutnya / baris selanjutnya)
  143. // Cek $numrow apakah lebih dari 1
  144. // Artinya karena baris pertama adalah nama-nama kolom
  145. // Jadi dilewat saja, tidak usah diimport
  146. if($numrow > 1){
  147. // Validasi apakah semua data telah diisi
  148. $nis_td = ( ! empty($nis))? "" : " style='background: #E07171;'"; // Jika NIS kosong, beri warna merah
  149. $nama_td = ( ! empty($nama))? "" : " style='background: #E07171;'"; // Jika Nama kosong, beri warna merah
  150. $jk_td = ( ! empty($jenis_kelamin))? "" : " style='background: #E07171;'"; // Jika Jenis Kelamin kosong, beri warna merah
  151. $telp_td = ( ! empty($telp))? "" : " style='background: #E07171;'"; // Jika Telepon kosong, beri warna merah
  152. $alamat_td = ( ! empty($alamat))? "" : " style='background: #E07171;'"; // Jika Alamat kosong, beri warna merah
  154. // Jika salah satu data ada yang kosong
  155. if(empty($nis) or empty($nama) or empty($jenis_kelamin) or empty($telp) or empty($alamat)){
  156. $kosong++; // Tambah 1 variabel $kosong
  157. }
  159. echo "<tr>";
  160. echo "<td".$nis_td.">".$nis."</td>";
  161. echo "<td".$nama_td.">".$nama."</td>";
  162. echo "<td".$jk_td.">".$jenis_kelamin."</td>";
  163. echo "<td".$telp_td.">".$telp."</td>";
  164. echo "<td".$alamat_td.">".$alamat."</td>";
  165. echo "</tr>";
  166. }
  168. $numrow++; // Tambah 1 setiap kali looping
  169. }
  171. echo "</table>";
  173. // Cek apakah variabel kosong lebih dari 1
  174. // Jika lebih dari 1, berarti ada data yang masih kosong
  175. if($kosong > 1){
  176. ?>
  177. <script>
  178. $(document).ready(function(){
  179. // Ubah isi dari tag span dengan id jumlah_kosong dengan isi dari variabel kosong
  180. $("#jumlah_kosong").html('<?php echo $kosong; ?>');
  182. $("#kosong").show(); // Munculkan alert validasi kosong
  183. });
  184. </script>
  185. <?php
  186. }else{ // Jika semua data sudah diisi
  187. echo "<hr>";
  189. // Buat sebuah tombol untuk mengimport data ke database
  190. echo "<button type='submit' name='import' class='btn btn-primary'><span class='glyphicon glyphicon-upload'></span> Import</button>";
  191. }
  193. echo "</form>";
  194. }else{ // Jika file yang diupload bukan File Excel 2007 (.xlsx)
  195. // Munculkan pesan validasi
  196. echo "<div class='alert alert-danger'>
  197. Hanya File Excel 2007 (.xlsx) yang diperbolehkan
  198. </div>";
  199. }
  200. }
  201. ?>
  202. </div>
  203. </body>
  204. </html>

Saya tidak akan menjelaskan soal skrip diatas, karena sudah saya jelaskan per masing-masing skrip (dengan komentar). Tapi saya akan jelaskan alur form import diatas. Pertama, User harus mendownload Format File Excel yang sudah kita buat sebelumnya. Kedua, User megisi data yang akan diimport pada file excel yang telah di download tadi. Ketiga, user memilih file excel yang akan diimport datanya lalu klik tombol preview. Keempat, Ketika user mengklik tombol preview, sistem akan melakukan:
  1. Validasi apakah file yang dipilih memiliki format microsoft excel 2007 (.xlsx) atau tidak
  2. Validasi apakah user telah mengisi semua data yang diperlukan pada file excel tersebut. Jika masih ada yang kosong / belum diisi, sistem akan memberi informasi berupa jumlah data yang masih kosong dan memberi tahu user data mana saja yang kosong dengan cara memberi background merah pada kolom / baris yang kosong (pada tabel preview)
  3. Apabila lolos dari kedua validasi yang saya sebutkan diatas, sistem akan memunculkan tombol Import untuk melakukan proses import data. Tapi apabila ternyata tidak lolos validasi, tombol import tidak akan muncul.
STEP 5 – PROSES IMPORT

Terakhir, kita akan membuat file php untuk memproses import data ke databasenya. Buat sebuah file baru dengan nama import.php, lalu simpan pada folder xampp/htdocs/import_php/. Berikut ini kodenya :
  1. <?php
  2. // Load file koneksi.php
  3. include "koneksi.php";
  5. if(isset($_POST['import'])){ // Jika user mengklik tombol Import
  6. $nama_file_baru = 'data.xlsx';
  8. // Load librari PHPExcel nya
  9. require_once 'PHPExcel/PHPExcel.php';
  11. $excelreader = new PHPExcel_Reader_Excel2007();
  12. $loadexcel = $excelreader->load('tmp/'.$nama_file_baru); // Load file excel yang tadi diupload ke folder tmp
  13. $sheet = $loadexcel->getActiveSheet()->toArray(null, true, true ,true);
  15. // Buat query Insert
  16. $sql = $pdo->prepare("INSERT INTO siswa VALUES(:nis,:nama,:jk,:telp,:alamat)");
  18. $numrow = 1;
  19. foreach($sheet as $row){
  20. // Ambil data pada excel sesuai Kolom
  21. $nis = $row['A']; // Ambil data NIS
  22. $nama = $row['B']; // Ambil data nama
  23. $jenis_kelamin = $row['C']; // Ambil data jenis kelamin
  24. $telp = $row['D']; // Ambil data telepon
  25. $alamat = $row['E']; // Ambil data alamat
  27. // Cek jika semua data tidak diisi
  28. if(empty($nis) && empty($nama) && empty($jenis_kelamin) && empty($telp) && empty($alamat))
  29. continue; // Lewat data pada baris ini (masuk ke looping selanjutnya / baris selanjutnya)
  31. // Cek $numrow apakah lebih dari 1
  32. // Artinya karena baris pertama adalah nama-nama kolom
  33. // Jadi dilewat saja, tidak usah diimport
  34. if($numrow > 1){
  35. // Proses simpan ke Database
  36. $sql->bindParam(':nis', $nis);
  37. $sql->bindParam(':nama', $nama);
  38. $sql->bindParam(':jk', $jenis_kelamin);
  39. $sql->bindParam(':telp', $telp);
  40. $sql->bindParam(':alamat', $alamat);
  41. $sql->execute(); // Eksekusi query insert
  42. }
  44. $numrow++; // Tambah 1 setiap kali looping
  45. }
  46. }
  48. header('location: index.php'); // Redirect ke halaman awal
  49. ?>
Sedikit tambahan penjelasan dari skrip diatas :
$nis = $row[‘A‘]; // Ambil data NIS
$nama = $row[‘B‘]; // Ambil data nama
$jenis_kelamin = $row[‘C‘]; // Ambil data jenis kelamin
$telp = $row[‘D‘]; // Ambil data telepon
$alamat = $row[‘E‘]; // Ambil data alamat

Yang saya beri warna merah pada skrip diatas harus sama dengan nama kolom yang ada d file excel yang akan diimport. Lihat gambar dibawah ini untuk lebih jelasnya :


Source CodeUntuk download source codenya, silahkan klik link berikut : Download.
Bingung cara downloadnya? klik disini untuk mengetahui caranya.
Sumber & Referensi
Dokumentasi Bootstrap : http://getbootstrap.com
Dokumentasi PHPExcel : https://github.com/PHPOffice/PHPExcel/wiki/User%20Documentation
Tutorial import data dari excel dengan php dan mysql, Tutorial import data dari excel dengan PHPExcel, Cara import data excel dengan php dan mysql, import data excel dengan PHPExcel, import data dari excel dengan php dan mysql