Cara Membuat Import Data Excel dengan PHP dan MySQL
Rizaldi Maulidia Achmad
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.
PENTING, MOHON DIBACA TERLEBIH DAHULU
Tutorial ini menggunakan librari PHPExcel untuk proses import excelnya. Librari ini setau saya dan sudah saya tes hanya mendukung sampai PHP Versi 7.2.8. Jadi bagi kamu yang pakai PHP Versi diatas 7.2.8, sebaiknya downgrade dulu atau download Xampp dimana PHP nya masih versi 7.2.8 ke bawah. Jika Anda ingin melihat tutorial untuk PHP 7.2.8 ke atas, silahkan buka tutorial berikut ini :
Tutorial Import Data dari Excel untuk PHP 7.2.8 ke Atas :
Cara Membuat Import Data Excel dengan PhpSpreadsheet
Berikut ini untuk link download Xampp Versi 7.2.8 :
- Download Xampp PHP 7.2.8 ( Windows )
- Download Xampp PHP 7.2.8 ( Linux )
- Download Xampp PHP 7.2.8 ( Mac OS X )
Berikut adalah fitur-fitur yang akan kita buat:
- View Data dari Database.
- Form untuk Import Data.
- Preview Data yang akan diimport sebelum di simpan ke database.
- Validasi terhadap file yang dipilih oleh User pada form import. Hanya file Microsoft Office 2007 (.xlsx) yang diperbolehkan.
- Validasi terhadap input data. Jika ada data yang kosong / belum diisi, sistem akan memberitahu jumlah data yang kosong dan data mana saja.
DEMO
Sebelum 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 :
- Download Bootstrap, Klik link berikut : Download.
- Download Jquery, Klik link berikut : Download.
- Download librari PHPExcel nya, klik link berikut ini : Download.
- Download format import data excel, klik link berikut : Download. File ini berfungsi sebagai format excel yang digunakan untuk import data.
- Buat sebuah folder dengan nama import_php, lalu simpan pada folder xampp/htdocs/.
- Buat sebuah folder dengan nama tmp, lalu simpan pada folder xampp/htdocs/import_php/.
- Buat sebuah folder dengan nama css, lalu simpan pada folder xampp/htdocs/import_php/.
- Buat sebuah folder dengan nama js, lalu simpan pada folder xampp/htdocs/import_php/.
- Esktrak file bootstrap.7z yang telah didownload tadi.
- Copy and paste folder fonts dari folder bootstrap yang telah diekstrak tadi ke folder xampp/htdocs/import_php/.
- Copy and paste file bootstrap.min.css dari folder bootstrap/css/ yang telah diekstrak tadi ke folder xampp/htdocs/import_php/css/.
- Rename file JQUERY yang telah di downlaod tadi menjadi jquery.min.js, lalu copy and paste pada folder xampp/htdocs/import_php/js/.
- Ekstrak file librari PHPExcel.7z yang telah didownload tadi.
- Copy and paste folder PHPExcel hasil ekstrak file tadi ke folder xampp/htdocs/import_php/.
- 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 :
- Buat sebuah database dengan nama mynotescode.
- Buat sebuah tabel dengan nama siswa. Struktur tabelnya sebagai berikut :
CREATE TABLE IF NOT EXISTS `siswa` ( `nis` varchar(11) NOT NULL, `nama` varchar(50) NOT NULL, `jenis_kelamin` varchar(10) NOT NULL, `telp` varchar(15) NOT NULL, `alamat` text NOT NULL, PRIMARY KEY (`nis`) ) 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 :
<?php
$host = 'localhost'; // Nama hostnya
$username = 'root'; // Username
$password = ''; // Password (Isi jika menggunakan password)
$database = 'mynotescode'; // Nama databasenya
// Koneksi ke MySQL dengan PDO
$pdo = new PDO('mysql:host='.$host.';dbname='.$database, $username, $password);
?>
STEP 4 – VIEW DATA
Selanjutnya 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 :
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Import Data dengan PHP</title>
<!-- Load File bootstrap.min.css yang ada difolder css -->
<link href="css/bootstrap.min.css" rel="stylesheet">
<!-- Style untuk Loading -->
<style>
#loading{
background: whitesmoke;
position: absolute;
top: 140px;
left: 82px;
padding: 5px 10px;
border: 1px solid #ccc;
}
</style>
</head>
<body>
<!-- Membuat Menu Header / Navbar -->
<nav class="navbar navbar-inverse" role="navigation">
<div class="container-fluid">
<div class="navbar-header">
<a class="navbar-brand" href="#" style="color: white;"><b>Import Data dengan PHP</b></a>
</div>
<p class="navbar-text navbar-right hidden-xs" style="color: white;padding-right: 10px;">
FOLLOW US ON
<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>
<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>
<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>
</p>
</div>
</nav>
<!-- Content -->
<div style="padding: 0 15px;">
<!--
-- Buat sebuah tombol untuk mengarahkan ke form import data
-- Tambahkan class btn agar terlihat seperti tombol
-- Tambahkan class btn-success untuk tombol warna hijau
-- class pull-right agar posisi link berada di sebelah kanan
-->
<a href="form.php" class="btn btn-success pull-right">
<span class="glyphicon glyphicon-upload"></span> Import Data
</a>
<h3>Data Hasil Import</h3>
<hr>
<!-- Buat sebuah div dan beri class table-responsive agar tabel jadi responsive -->
<div class="table-responsive">
<table class="table table-bordered">
<tr>
<th>No</th>
<th>NIS</th>
<th>Nama</th>
<th>Jenis Kelamin</th>
<th>Telepon</th>
<th>Alamat</th>
</tr>
<?php
// Load file koneksi.php
include "koneksi.php";
// Buat query untuk menampilkan semua data siswa
$sql = $pdo->prepare("SELECT * FROM siswa");
$sql->execute(); // Eksekusi querynya
$no = 1; // Untuk penomoran tabel, di awal set dengan 1
while($data = $sql->fetch()){ // Ambil semua data dari hasil eksekusi $sql
echo "<tr>";
echo "<td>".$no."</td>";
echo "<td>".$data['nis']."</td>";
echo "<td>".$data['nama']."</td>";
echo "<td>".$data['jenis_kelamin']."</td>";
echo "<td>".$data['telp']."</td>";
echo "<td>".$data['alamat']."</td>";
echo "</tr>";
$no++; // Tambah 1 setiap kali looping
}
?>
</table>
</div>
</div>
</body>
</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.
echo "<tr>"; echo "<td>".$no."</td>"; echo "<td>".$data['nis']."</td>"; echo "<td>".$data['nama']."</td>"; echo "<td>".$data['jenis_kelamin']."</td>"; echo "<td>".$data['telp']."</td>"; echo "<td>".$data['alamat']."</td>"; 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 PREVIEW
Langkah 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 :
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Import Data Excel dengan PHP</title>
<!-- Load File bootstrap.min.css yang ada difolder css -->
<link href="css/bootstrap.min.css" rel="stylesheet">
<!-- Style untuk Loading -->
<style>
#loading{
background: whitesmoke;
position: absolute;
top: 140px;
left: 82px;
padding: 5px 10px;
border: 1px solid #ccc;
}
</style>
<!-- Load File jquery.min.js yang ada difolder js -->
<script src="js/jquery.min.js"></script>
<script>
$(document).ready(function(){
// Sembunyikan alert validasi kosong
$("#kosong").hide();
});
</script>
</head>
<body>
<!-- Membuat Menu Header / Navbar -->
<nav class="navbar navbar-inverse" role="navigation">
<div class="container-fluid">
<div class="navbar-header">
<a class="navbar-brand" href="#" style="color: white;"><b>Import Data Excel dengan PHP</b></a>
</div>
<p class="navbar-text navbar-right hidden-xs" style="color: white;padding-right: 10px;">
FOLLOW US ON
<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>
<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>
<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>
</p>
</div>
</nav>
<!-- Content -->
<div style="padding: 0 15px;">
<!-- Buat sebuah tombol Cancel untuk kemabli ke halaman awal / view data -->
<a href="index.php" class="btn btn-danger pull-right">
<span class="glyphicon glyphicon-remove"></span> Cancel
</a>
<h3>Form Import Data</h3>
<hr>
<!-- Buat sebuah tag form dan arahkan action nya ke file ini lagi -->
<form method="post" action="" enctype="multipart/form-data">
<a href="Format.xlsx" class="btn btn-default">
<span class="glyphicon glyphicon-download"></span>
Download Format
</a><br><br>
<!--
-- Buat sebuah input type file
-- class pull-left berfungsi agar file input berada di sebelah kiri
-->
<input type="file" name="file" class="pull-left">
<button type="submit" name="preview" class="btn btn-success btn-sm">
<span class="glyphicon glyphicon-eye-open"></span> Preview
</button>
</form>
<hr>
<!-- Buat Preview Data -->
<?php
// Jika user telah mengklik tombol Preview
if(isset($_POST['preview'])){
$nama_file_baru = 'data.xlsx';
// Cek apakah terdapat file data.xlsx pada folder tmp
if(is_file('tmp/'.$nama_file_baru)) // Jika file tersebut ada
unlink('tmp/'.$nama_file_baru); // Hapus file tersebut
$ext = pathinfo($_FILES['file']['name'], PATHINFO_EXTENSION); // Ambil ekstensi filenya apa
$tmp_file = $_FILES['file']['tmp_name'];
// Cek apakah file yang diupload adalah file Excel 2007 (.xlsx)
if($ext == "xlsx"){
// Upload file yang dipilih ke folder tmp
move_uploaded_file($tmp_file, 'tmp/'.$nama_file_baru);
// Load librari PHPExcel nya
require_once 'PHPExcel/PHPExcel.php';
$excelreader = new PHPExcel_Reader_Excel2007();
$loadexcel = $excelreader->load('tmp/'.$nama_file_baru); // Load file yang tadi diupload ke folder tmp
$sheet = $loadexcel->getActiveSheet()->toArray(null, true, true ,true);
// Buat sebuah tag form untuk proses import data ke database
echo "<form method='post' action='import.php'>";
// Buat sebuah div untuk alert validasi kosong
echo "<div class='alert alert-danger' id='kosong'>
Semua data belum diisi, Ada <span id='jumlah_kosong'></span> data yang belum diisi.
</div>";
echo "<table class='table table-bordered'>
<tr>
<th colspan='5' class='text-center'>Preview Data</th>
</tr>
<tr>
<th>NIS</th>
<th>Nama</th>
<th>Jenis Kelamin</th>
<th>Telepon</th>
<th>Alamat</th>
</tr>";
$numrow = 1;
$kosong = 0;
foreach($sheet as $row){ // Lakukan perulangan dari data yang ada di excel
// Ambil data pada excel sesuai Kolom
$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
// Cek jika semua data tidak diisi
if($nis == "" && $nama == "" && $jenis_kelamin == "" && $telp == "" && $alamat == "")
continue; // Lewat data pada baris ini (masuk ke looping selanjutnya / baris selanjutnya)
// Cek $numrow apakah lebih dari 1
// Artinya karena baris pertama adalah nama-nama kolom
// Jadi dilewat saja, tidak usah diimport
if($numrow > 1){
// Validasi apakah semua data telah diisi
$nis_td = ( ! empty($nis))? "" : " style='background: #E07171;'"; // Jika NIS kosong, beri warna merah
$nama_td = ( ! empty($nama))? "" : " style='background: #E07171;'"; // Jika Nama kosong, beri warna merah
$jk_td = ( ! empty($jenis_kelamin))? "" : " style='background: #E07171;'"; // Jika Jenis Kelamin kosong, beri warna merah
$telp_td = ( ! empty($telp))? "" : " style='background: #E07171;'"; // Jika Telepon kosong, beri warna merah
$alamat_td = ( ! empty($alamat))? "" : " style='background: #E07171;'"; // Jika Alamat kosong, beri warna merah
// Jika salah satu data ada yang kosong
if($nis == "" or $nama == "" or $jenis_kelamin == "" or $telp == "" or $alamat == ""){
$kosong++; // Tambah 1 variabel $kosong
}
echo "<tr>";
echo "<td".$nis_td.">".$nis."</td>";
echo "<td".$nama_td.">".$nama."</td>";
echo "<td".$jk_td.">".$jenis_kelamin."</td>";
echo "<td".$telp_td.">".$telp."</td>";
echo "<td".$alamat_td.">".$alamat."</td>";
echo "</tr>";
}
$numrow++; // Tambah 1 setiap kali looping
}
echo "</table>";
// Cek apakah variabel kosong lebih dari 0
// Jika lebih dari 0, berarti ada data yang masih kosong
if($kosong > 0){
?>
<script>
$(document).ready(function(){
// Ubah isi dari tag span dengan id jumlah_kosong dengan isi dari variabel kosong
$("#jumlah_kosong").html('<?php echo $kosong; ?>');
$("#kosong").show(); // Munculkan alert validasi kosong
});
</script>
<?php
}else{ // Jika semua data sudah diisi
echo "<hr>";
// Buat sebuah tombol untuk mengimport data ke database
echo "<button type='submit' name='import' class='btn btn-primary'><span class='glyphicon glyphicon-upload'></span> Import</button>";
}
echo "</form>";
}else{ // Jika file yang diupload bukan File Excel 2007 (.xlsx)
// Munculkan pesan validasi
echo "<div class='alert alert-danger'>
Hanya File Excel 2007 (.xlsx) yang diperbolehkan
</div>";
}
}
?>
</div>
</body>
</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:
- Validasi apakah file yang dipilih memiliki format microsoft excel 2007 (.xlsx) atau tidak
- 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)
- 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 :
<?php
/*
-- Source Code from My Notes Code (www.mynotescode.com)
--
-- Follow Us on Social Media
-- Facebook : http://facebook.com/mynotescode/
-- Twitter : http://twitter.com/mynotescode
-- Google+ : http://plus.google.com/118319575543333993544
--
-- Terimakasih telah mengunjungi blog kami.
-- Jangan lupa untuk Like dan Share catatan-catatan yang ada di blog kami.
*/
// Load file koneksi.php
include "koneksi.php";
if(isset($_POST['import'])){ // Jika user mengklik tombol Import
$nama_file_baru = 'data.xlsx';
// Load librari PHPExcel nya
require_once 'PHPExcel/PHPExcel.php';
$excelreader = new PHPExcel_Reader_Excel2007();
$loadexcel = $excelreader->load('tmp/'.$nama_file_baru); // Load file excel yang tadi diupload ke folder tmp
$sheet = $loadexcel->getActiveSheet()->toArray(null, true, true ,true);
$numrow = 1;
foreach($sheet as $row){
// Ambil data pada excel sesuai Kolom
$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
// Cek jika semua data tidak diisi
if($nis == "" && $nama == "" && $jenis_kelamin == "" && $telp == "" && $alamat == "")
continue; // Lewat data pada baris ini (masuk ke looping selanjutnya / baris selanjutnya)
// Cek $numrow apakah lebih dari 1
// Artinya karena baris pertama adalah nama-nama kolom
// Jadi dilewat saja, tidak usah diimport
if($numrow > 1){
// Proses simpan ke Database
// Buat query Insert
$sql = $pdo->prepare("INSERT INTO siswa VALUES(:nis,:nama,:jk,:telp,:alamat)");
$sql->bindParam(':nis', $nis);
$sql->bindParam(':nama', $nama);
$sql->bindParam(':jk', $jenis_kelamin);
$sql->bindParam(':telp', $telp);
$sql->bindParam(':alamat', $alamat);
$sql->execute(); // Eksekusi query insert
}
$numrow++; // Tambah 1 setiap kali looping
}
}
header('location: index.php'); // Redirect ke halaman awal
?>
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 Code
Untuk mengunduh source code nya, klik salah satu link dibawah ini
– Download versi PDO : Link download yang sesuai tutorial ini yakni menggunakan fungsi PDO untuk query ke databasenya
– Download versi MySQLi : Link download untuk Anda yang ingin source code versi MySQLi dari tutorial ini
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, Source Code import data excel dengan PHPExcel dan MySQL
POST YOUR COMMENTS