Cara Membuat Import Data Dari Excel dengan Codeigniter dan PHPExcel
Rizaldi Maulidia Achmad
Sudah lama tidak update catatan, pada kesempatan ini. Saya akan coba berbagi kembali catatan/tutorial yang mudah-mudahan bisa bermanfaat yakni Cara Membuat Import Data Dari Excel dengan menggunakan Framework Codeigniter 3, librari PHPExcel (berguna untuk membantu kita membaca isi dari file excel yang akan diimport datanya), dan Database MySQL.
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. Untuk tutorial PHP Versi 7.2.8 ke atas akan saya buatkan, jadi tunggu saja update tutorial selanjutnya.
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
- Buat sebuah folder dengan nama import_excel_ci lalu simpan pada folder xampp/htdocs/
- Buat sebuah folder dengan nama excel lalu simpan pada folder xampp/htdocs/import_excel_ci/
- Buat sebuah folder dengan nama js, lalu simpan pada folder xampp/htdocs/import_excel_ci/.
- Download plugin PHPExcel, klik link berikut : Download.
- Download Framework Codeigniter, klik link berikut : Download.
- Download Format Excel untuk proses import, klik link berikut : Download.
- Download Librari jQuery, Klik link berikut : Download.
- Copy and paste file codeigniter_v3.7z yang telah di download tadi ke folder xampp/htdocs/import_excel_ci/.
- Ekstrak file codeigniter_v3.7z nya.
- Ekstrak file PHPExcel.7z yang telah di download tadi, lalu copy and paste folder PHPExcel nya ke folder xampp/htdocs/import_excel_ci/application/third_party/.
- Rename file format_ci.xlsx yang telah didownload tadi menjadi format.xlsx, lalu copy and paste file tersebut ke folder xampp/htdocs/import_excel_ci/excel/.
- Copy and paste file jquery.min.js yang telah di download tadi ke folder xampp/htdocs/import_excel_ci/js/.
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 `siswa` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `nis` varchar(11) NOT NULL, `nama` varchar(50) NOT NULL, `jenis_kelamin` varchar(10) NOT NULL, `alamat` text NOT NULL )
STEP 3 – KONFIGURASI
Karena dibuat dengan Codeigniter, pertama kita harus melakukan konfigurasi terlebih dahulu pada framework codeigniternya.
- Buka folder xampp/htdocs/import_excel_ci/application/config/
- Buka file config.php
Cari kode berikut$config['base_url'] = '';Ubah kode tersebut jadi seperti ini :$config['base_url'] = 'http://localhost/import_excel_ci/';
Kode diatas digunakan untuk menset baseurlnya.
Lalu simpan file tersebut. - Buka file autoload.php
Cari kode berikut ini :$autoload['libraries'] = array(); $autoload['helper'] = array();
Ubah jadi seperti ini :
$autoload['libraries'] = array('database'); $autoload['helper'] = array('url');Kode diatas digunakan untuk memuat (menload) class database dan url.
Lalu simpan file tersebut. - Buka file routes.php
Cari kode berikut ini :$route['default_controller'] = 'welcome';
Ubah jadi seperti ini :
$route['default_controller'] = 'siswa';
Kode diatas digunakan untuk menset controller mana yang akan diload pertama kali. Secara default, Codeigniter telah menset default controller yaitu welcome. Disini kita set default controller menjadi siswa.
Lalu simpan file tersebut. - Buka file database.php
Cari kode berikut ini :'hostname' => 'localhost', 'username' => '', 'password' => '', 'database' => '',
Ubah jadi seperti ini :
'hostname' => 'localhost', // Nama host 'username' => 'root', // Username 'password' => '', // Jika menggunakan password isi, jika tidak kosongkan saja 'database' => 'mynotescode', // Nama databasenya
Kode diatas digunakan untuk koneksi ke database.
Lalu simpan file tersebut.
STEP 4 – BUAT MODEL
Langkah selanjutnya adalah membuat modelnya. Buat sebuah file dengan nama SiswaModel.php, Lalu simpan pada folder xampp/htdocs/import_excel_ci/application/models/. Berikut kodenya :
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class SiswaModel extends CI_Model {
public function view(){
return $this->db->get('siswa')->result(); // Tampilkan semua data yang ada di tabel siswa
}
// Fungsi untuk melakukan proses upload file
public function upload_file($filename){
$this->load->library('upload'); // Load librari upload
$config['upload_path'] = './excel/';
$config['allowed_types'] = 'xlsx';
$config['max_size'] = '2048';
$config['overwrite'] = true;
$config['file_name'] = $filename;
$this->upload->initialize($config); // Load konfigurasi uploadnya
if($this->upload->do_upload('file')){ // Lakukan upload dan Cek jika proses upload berhasil
// Jika berhasil :
$return = array('result' => 'success', 'file' => $this->upload->data(), 'error' => '');
return $return;
}else{
// Jika gagal :
$return = array('result' => 'failed', 'file' => '', 'error' => $this->upload->display_errors());
return $return;
}
}
// Buat sebuah fungsi untuk melakukan insert lebih dari 1 data
public function insert_multiple($data){
$this->db->insert_batch('siswa', $data);
}
}
Pada kode diatas, kita membuat sebuah fungsi view(). dimana didalamnya terdapat kode return $this->db->get(‘siswa’)->result(). Kode tersebut berfungsi untuk menampilkan semua data pada tabel siswa. Struktur dasar penulisannya seperti ini : return $this->db->get(‘nama_tabel‘)->result(). Lalu pada kode diatas juga ada kode return, kode tersebut digunakan untuk mengeluarkan hasil dari sebuah fungsi. Pada kasus diatas, hasil yang dikeluarkan oleh return yaitu data-data siswa.
Lalu terdapata function upload_file($filename), fungsi ini berfungsi untuk melakukan upload file excel yang akan di import datanya. Ada beberapa konfigurasi dalam uploadnya antara lain :
- $config[‘upload_path’] = ‘./excel/’;
Berfungsi untuk menentukan folder untuk menyimpan file yang akan diupload. Dalam catatn ini, kita akan menguploadnya di folder excel yang berada di root direktori web (xampp/htdocs/import_excel_ci/excel). - $config[‘allowed_types’] = ‘xlsx’;
Berfungsi untuk memvalidasi file tipe apa saja yang boleh diupload. - $config[‘max_size’] = ‘2048’;
Berfungsi untuk menentukan maksimal ukuran file yang boleh diupload dalam satuan Kb. Disini saya tentukan 2048 Kb = 2 MB. - $config[‘overwrite’] = true;
Berfungsi untuk me-replace file apabila di dalam folder (tempat menyimpan file uploadnya) sudah ada file dengan nama tersebut. - $config[‘file_name’] = $filename;
Berfungsi untuk menentukan dan me-rename file yang akan diupload sesuai keinginan. Dalam catatan ini, kita set nama filenya menjadi import_data.xlsx - Untuk lebih lengkapnya mengenai konfiurasi apa saja yang bisa kita gunakan, Anda bisa lihat pada dokumentasi Codeigniternya langsung disini.
$this->upload->data()
Berfungsi untuk mengambil output dari hasil proses upload. outputnya berupa array seperti contoh berikut :
Array ( [file_name] => mypic.jpg [file_type] => image/jpeg [file_path] => /path/to/your/upload/ [full_path] => /path/to/your/upload/jpg.jpg [raw_name] => mypic [orig_name] => mypic.jpg [client_name] => mypic.jpg [file_ext] => .jpg [file_size] => 22.2 [is_image] => 1 [image_width] => 800 [image_height] => 600 [image_type] => jpeg [image_size_str] => width="800" height="200" )
$this->upload->display_errors()
Berfungsi untuk mengambil pesan error apabila proses upload gagal.
$this->db->insert_batch(‘siswa’, $data);
Berfungsi untuk melakukan insert lebih dari 1 data sekaligus. Terdapat 2 parameter yang dibutuhkan oleh fungsi ini yakni :
- Parameter 1 : diisi dengan nama tabel
- Parameter 2 : diisi dengan array data yang akan di insert, contoh array nya :
array( array('field1'=>'isi_field1','field2'=>'isi_field2'), // Data ke 1 array('field1'=>'isi_field1','field2'=>'isi_field2') // Data ke 2 )
Untuk lebih jelasnya mengenai insert_batch, silahkan anda lihat di dokumentasi nya langsung : klik disini.
STEP 5 – BUAT CONTROLLER
Pada tahap ini, kita akan membuat controllernya. Buat sebuah file dengan nama Siswa.php, lalu simpan pada folder xampp/htdocs/import_excel_ci/application/controllers/. Berikut ini kodenya :
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Siswa extends CI_Controller {
private $filename = "import_data"; // Kita tentukan nama filenya
public function __construct(){
parent::__construct();
$this->load->model('SiswaModel');
}
public function index(){
$data['siswa'] = $this->SiswaModel->view();
$this->load->view('view', $data);
}
public function form(){
$data = array(); // Buat variabel $data sebagai array
if(isset($_POST['preview'])){ // Jika user menekan tombol Preview pada form
// lakukan upload file dengan memanggil function upload yang ada di SiswaModel.php
$upload = $this->SiswaModel->upload_file($this->filename);
if($upload['result'] == "success"){ // Jika proses upload sukses
// Load plugin PHPExcel nya
include APPPATH.'third_party/PHPExcel/PHPExcel.php';
$excelreader = new PHPExcel_Reader_Excel2007();
$loadexcel = $excelreader->load('excel/'.$this->filename.'.xlsx'); // Load file yang tadi diupload ke folder excel
$sheet = $loadexcel->getActiveSheet()->toArray(null, true, true ,true);
// Masukan variabel $sheet ke dalam array data yang nantinya akan di kirim ke file form.php
// Variabel $sheet tersebut berisi data-data yang sudah diinput di dalam excel yang sudha di upload sebelumnya
$data['sheet'] = $sheet;
}else{ // Jika proses upload gagal
$data['upload_error'] = $upload['error']; // Ambil pesan error uploadnya untuk dikirim ke file form dan ditampilkan
}
}
$this->load->view('form', $data);
}
public function import(){
// Load plugin PHPExcel nya
include APPPATH.'third_party/PHPExcel/PHPExcel.php';
$excelreader = new PHPExcel_Reader_Excel2007();
$loadexcel = $excelreader->load('excel/'.$this->filename.'.xlsx'); // Load file yang telah diupload ke folder excel
$sheet = $loadexcel->getActiveSheet()->toArray(null, true, true ,true);
// Buat sebuah variabel array untuk menampung array data yg akan kita insert ke database
$data = array();
$numrow = 1;
foreach($sheet as $row){
// Cek $numrow apakah lebih dari 1
// Artinya karena baris pertama adalah nama-nama kolom
// Jadi dilewat saja, tidak usah diimport
if($numrow > 1){
// Kita push (add) array data ke variabel data
array_push($data, array(
'nis'=>$row['A'], // Insert data nis dari kolom A di excel
'nama'=>$row['B'], // Insert data nama dari kolom B di excel
'jenis_kelamin'=>$row['C'], // Insert data jenis kelamin dari kolom C di excel
'alamat'=>$row['D'], // Insert data alamat dari kolom D di excel
));
}
$numrow++; // Tambah 1 setiap kali looping
}
// Panggil fungsi insert_multiple yg telah kita buat sebelumnya di model
$this->SiswaModel->insert_multiple($data);
redirect("Siswa"); // Redirect ke halaman awal (ke controller siswa fungsi index)
}
}
Saya akan sedikit jelaskan mengenai kode diatas.
$excelreader = new PHPExcel_Reader_Excel2007();
$loadexcel = $excelreader->load(‘excel/’.$this->filename.’.xlsx’);
$sheet = $loadexcel->getActiveSheet()->toArray(null, true, true ,true);
Kode ini berfungsi untuk membaca isi dari file excel yang telah kita upload sebelumnya. Hasilnya nanti akan menjadi array yang akan kit looping menggunakan foreach pada file form.php.
array_push($data, [
‘nis’=>$row[‘A’],
‘nama’=>$row[‘B’],
‘jenis_kelamin’=>$row[‘C’],
‘alamat’=>$row[‘D’],
]);
Kode ini berfungsi untuk menambahkan array baru ke dalam variabel $data. Nantinya variabel data akan terisi array seperti berikut :
array(
array('nis'=>'isi dari nis 1', 'nama'=>'isi dari nama 1', 'jenis_kelamin'=>'isi dari jenis kelamin 1', 'alamat'=>'isi dari alamat 1'),
array('nis'=>'isi dari nis 2', 'nama'=>'isi dari nama 2', 'jenis_kelamin'=>'isi dari jenis kelamin 2', 'alamat'=>'isi dari alamat 2'),
array('nis'=>'isi dari nis 3', 'nama'=>'isi dari nama 3', 'jenis_kelamin'=>'isi dari jenis kelamin 3', 'alamat'=>'isi dari alamat 3'),
)
STEP 6 – VIEW
Langkah selanjutnya adalah membuat file view untuk menampilkan interface / antarmukanya. Buat sebuah file dengan nama view.php, lalu simpan pada folder xampp/htdocs/import_excel_ci/application/views/. Berikut tampilan dan kodenya :
<html>
<head>
<title>IMPORT EXCEL CI 3</title>
</head>
<body>
<h1>Data Siswa</h1><hr>
<a href="<?php echo base_url("index.php/siswa/form"); ?>">Import Data</a><br><br>
<table border="1" cellpadding="8">
<tr>
<th>NIS</th>
<th>Nama</th>
<th>Jenis Kelamin</th>
<th>Alamat</th>
</tr>
<?php
if( ! empty($siswa)){ // Jika data pada database tidak sama dengan empty (alias ada datanya)
foreach($siswa as $data){ // Lakukan looping pada variabel siswa dari controller
echo "<tr>";
echo "<td>".$data->nis."</td>";
echo "<td>".$data->nama."</td>";
echo "<td>".$data->jenis_kelamin."</td>";
echo "<td>".$data->alamat."</td>";
echo "</tr>";
}
}else{ // Jika data tidak ada
echo "<tr><td colspan='4'>Data tidak ada</td></tr>";
}
?>
</table>
</body>
</html>
Pada kode diatas terdapat kode foreach($siswa as $data){, kode tersebut akan manampilkan satu per satu data siswa sampai data siswa yang terakhir.
echo “<td>”.$data->nis.”</td>”;
echo “<td>”.$data->nama.”</td>”;
echo “<td>”.$data->jenis_kelamin.” </td>”;
echo “<td>”.$data->alamat.”</td>”;
Pada kode diatas, yang saya beri tanda merah. Itu harus sama dengan nama kolom / field yang ada di database tabel siswa.
STEP 7 – FORM
Terakhir, kita akan buat form untuk import datanya. Buat sebuah file dengan nama form.php, lalu simpan pada folder xampp/htdocs/import_excel_ci/application/views/. Berikut tampilan dan kodenya :
<html>
<head>
<title>Form Import</title>
<!-- Load File jquery.min.js yang ada difolder js -->
<script src="<?php echo base_url('js/jquery.min.js'); ?>"></script>
<script>
$(document).ready(function(){
// Sembunyikan alert validasi kosong
$("#kosong").hide();
});
</script>
</head>
<body>
<h3>Form Import</h3>
<hr>
<a href="<?php echo base_url("excel/format.xlsx"); ?>">Download Format</a>
<br>
<br>
<!-- Buat sebuah tag form dan arahkan action nya ke controller ini lagi -->
<form method="post" action="<?php echo base_url("index.php/Siswa/form"); ?>" enctype="multipart/form-data">
<!--
-- Buat sebuah input type file
-- class pull-left berfungsi agar file input berada di sebelah kiri
-->
<input type="file" name="file">
<!--
-- BUat sebuah tombol submit untuk melakukan preview terlebih dahulu data yang akan di import
-->
<input type="submit" name="preview" value="Preview">
</form>
<?php
if(isset($_POST['preview'])){ // Jika user menekan tombol Preview pada form
if(isset($upload_error)){ // Jika proses upload gagal
echo "<div style='color: red;'>".$upload_error."</div>"; // Muncul pesan error upload
die; // stop skrip
}
// Buat sebuah tag form untuk proses import data ke database
echo "<form method='post' action='".base_url("index.php/Siswa/import")."'>";
// Buat sebuah div untuk alert validasi kosong
echo "<div style='color: red;' id='kosong'>
Semua data belum diisi, Ada <span id='jumlah_kosong'></span> data yang belum diisi.
</div>";
echo "<table border='1' cellpadding='8'>
<tr>
<th colspan='5'>Preview Data</th>
</tr>
<tr>
<th>NIS</th>
<th>Nama</th>
<th>Jenis Kelamin</th>
<th>Alamat</th>
</tr>";
$numrow = 1;
$kosong = 0;
// Lakukan perulangan dari data yang ada di excel
// $sheet adalah variabel yang dikirim dari controller
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
$alamat = $row['D']; // Ambil data alamat
// Cek jika semua data tidak diisi
if($nis == "" && $nama == "" && $jenis_kelamin == "" && $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
$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 $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".$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 "<a href='".base_url("index.php/Siswa")."'>Cancel</a>";
}
echo "</form>";
}
?>
</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.
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 link berikut ini : Download.
SUMBER & REFERENSI
Dokumentasi Codeigniter : https://www.codeigniter.com/user_guide
Dokumentasi PHPExcel : https://github.com/PHPOffice/PHPExcel/wiki/User%20Documentation
Tutorial membuat import data dari excel dengan codeigniter dan phpexcel, tutorial import excel dengan codeigniter 3 dan mysql, cara mudah membuat import data dari excel dengan codeignite 3 dan phpexcel, cara mudah membuat import excel dengan codeigniter 3 dan mysql.
POST YOUR COMMENTS