Cara Membuat Import Data Excel dengan PhpSpreadsheet
Rizaldi Maulidia Achmad
Disini saya akan melanjutkan pembuatan tutorial tentang excel dengan librari PhpSpreadsheet, setelah sebelumnya saya buat tutorial cara export data ke excel dengan PhpSpreadsheet. Kenapa saya buat tutorial import data excel lagi? Karena pada tutorial import data excel sebelumnya, saya masih menggunakan Librari PHPExcel, dimana librari tersebut hanya mendukung sampai PHP Versi 7.2.8. Maka dari itu, tutorial ini dibuat agar teman-teman semua tetap bisa membuat improt data excel walaupun sudah pakai PHP versi terbaru sekalipun (PHP 8). Yuk simak step by stepnya dibawah ini.
PENTING, MOHON DIBACA TERLEBIH DAHULU
Tutorial ini menggunakan librari PhpSpreadsheet untuk proses import excelnya. Librari ini hanya mendukung PHP versi 7.2 ke atas. Bagi teman-teman yang menggunakan PHP versi 7.2 ke bawah, bisa baca tutorial ini : Cara Membuat Import Data Excel dengan PHP dan MySQL.
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 :
- Buat sebuah folder dengan nama import_php, lalu simpan pada folder xampp/htdocs/.
Path xampp nya sesuaikan dengan yang ada di komputer teman-teman masing-masing. - Buat sebuah folder dengan nama tmp, lalu simpan pada folder xampp/htdocs/import_php/.
- Buat sebuah folder dengan nama js, lalu simpan pada folder xampp/htdocs/import_php/.
- Download librari JQuery, Klik link berikut : Download.
- Download format import data excel, klik link berikut : Download. File ini berfungsi sebagai format excel yang digunakan untuk import data.
- Rename file JQuery yang telah di download tadi menjadi jquery.min.js, lalu copy and paste pada folder xampp/htdocs/import_php/js/.
- Copy and paste file Format.xlsx yang telah didownload ke folder xampp/htdocs/import_php/.
STEP 2 – Download PhpSpreadsheet
Pada langkah ini kita akan coba download librari PhpSpreadsheet nya dengan menggunakan Composer. Belum tau apa itu Composer? Cara installnya gimana? silahkan mampir dulu ke tutorial cara install composer berikut : Cara Install di Windows / Cara Install di Linux.
Setelah memastikan teman-teman sudah menginstall composernya, silahkan ikuti langkah berikut :
- Buka Command Prompt atau CMD.
- Ketik dan hit perintah berikut : cd C:\xampp\htdocs\export_excel.
Silahkan sesuaikan path tempat teman-teman menyimpan xampp nya. - Ketik dan hit perintah berikut : composer require phpoffice/phpspreadsheet. Tunggu proses download sampai selesai.
- Setelah teman-teman mengeksekusi perintah composer diatas, maka seharusnya di folder import_php nya terdapat file/folder baru berikut : folder vendor, file composer.json, file composer.lock.
STEP 3 – 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 4 – 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 $connect = mysqli_connect($host, $username, $password, $database); // Koneksi ke MySQL ?>
STEP 5 – 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">
<title>Import Data Excel dengan PhpSpreadsheet</title>
</head>
<body>
<h3>Data Siswa Hasil Import</h3>
<a href="form.php">Import Data</a><br><br>
<table border="1" cellpadding="5">
<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 = mysqli_query($connect, "SELECT * FROM siswa");
$no = 1; // Untuk penomoran tabel, di awal set dengan 1
while ($data = mysqli_fetch_array($sql)) { // 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>
</body>
</html>
Saya akan coba jelaskan sedikit mengenai kode diatas.
include “koneksi.php”;
Kode tersebut berfungsi untuk me-load file koneksi.php.
Kode tersebut berfungsi untuk melakukan query ke database dan mengeksekusinya. Pada cotoh diatas, kita akan melakukan query untuk menampilkan semua data dari tabel siswa.
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 6 – 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 :
<?php
// Load file autoload.php
require 'vendor/autoload.php';
// Include librari PhpSpreadsheet
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<title>Import Data Excel dengan PhpSpreadsheet</title>
<!-- 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>
<h3>Form Import Data</h3>
<form method="post" action="form.php" enctype="multipart/form-data">
<a href="Format.xlsx">Download Format</a> |
<a href="index.php">Kembali</a>
<br><br>
<input type="file" name="file">
<button type="submit" name="preview">Preview</button>
</form>
<hr>
<?php
// Jika user telah mengklik tombol Preview
if (isset($_POST['preview'])) {
$tgl_sekarang = date('YmdHis'); // Ini akan mengambil waktu sekarang dengan format yyyymmddHHiiss
$nama_file_baru = 'data' . $tgl_sekarang . '.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
// dan rename file tersebut menjadi data{tglsekarang}.xlsx
// {tglsekarang} diganti jadi tanggal sekarang dengan format yyyymmddHHiiss
// Contoh nama file setelah di rename : data20210814192500.xlsx
move_uploaded_file($tmp_file, 'tmp/' . $nama_file_baru);
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$spreadsheet = $reader->load('tmp/' . $nama_file_baru); // Load file yang tadi diupload ke folder tmp
$sheet = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
// Buat sebuah tag form untuk proses import data ke database
echo "<form method='post' action='import.php'>";
// Disini kita buat input type hidden yg isinya adalah nama file excel yg diupload
// ini tujuannya agar ketika import, kita memilih file yang tepat (sesuai yg diupload)
echo "<input type='hidden' name='namafile' value='" . $nama_file_baru . "'>";
// Buat sebuah div untuk alert validasi kosong
echo "<div id='kosong' style='color: red;margin-bottom: 10px;'>
Semua data belum diisi, Ada <span id='jumlah_kosong'></span> data yang belum diisi.
</div>";
echo "<table border='1' cellpadding='5'>
<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'>Import</button>";
}
echo "</form>";
} else { // Jika file yang diupload bukan File Excel 2007 (.xlsx)
// Munculkan pesan validasi
echo "<div style='color: red;margin-bottom: 10px;'>
Hanya File Excel 2007 (.xlsx) yang diperbolehkan
</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 7 – 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
// Load file koneksi.php
include "koneksi.php";
// Load file autoload.php
require 'vendor/autoload.php';
// Include librari PhpSpreadsheet
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
if(isset($_POST['import'])){ // Jika user mengklik tombol Import
$nama_file_baru = $_POST['namafile'];
$path = 'tmp/' . $nama_file_baru; // Set tempat menyimpan file tersebut dimana
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$spreadsheet = $reader->load($path); // Load file yang tadi diupload ke folder tmp
$sheet = $spreadsheet->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){
// Buat query Insert
$query = "INSERT INTO siswa VALUES('" . $nis . "','" . $nama . "','" . $jenis_kelamin . "','" . $telp . "','" . $alamat . "')";
// Eksekusi $query
mysqli_query($connect, $query);
}
$numrow++; // Tambah 1 setiap kali looping
}
unlink($path); // Hapus file excel yg telah diupload, ini agar tidak terjadi penumpukan file
}
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 :
Mungkin sekian untuk catatan kali ini. Semoga bisa bermanfaat. Jika ada yang kurang dipahami, langsung tanyakan pada form komentar dibawah ini. Jangan lupa LIKE dan SHARE nya, Terimakasih banyak.
Happy Coding ^_^
SOURCE CODE
Untuk mengunduh source code nya, klik salah satu link dibawah ini
– Download versi MySQLi tanpa Bootstrap (Link download yang sesuai tutorial ini)
– Download versi MySQLi dengan Bootstrap 3
– Download versi MySQLi dengan Bootstrap 4
– Download versi PDO tanpa Bootstrap
– Download versi PDO dengan Bootstrap 3
– Download versi PDO dengan Bootstrap 4
Sumber & Referensi
Dokumentasi PhpSpreadsheet : https://phpspreadsheet.readthedocs.io
Tutorial import data dari excel dengan phpspreadsheet, php dan mysql, Tutorial import data dari excel dengan phpspreadsheet, Cara import data excel dengan phpspreadsheet, php dan mysql, import data excel dengan phpspreadsheet, import data dari excel dengan phpspreadsheet, php dan mysql, Source Code import data excel dengan phpspreadsheet dan MySQL
POST YOUR COMMENTS