Kembali lagi bersama saya, salam hangat sehat-sehat terus ya. Kali ini kita akan belajar pemrograman PHP Codeigniter bagaimana cara mengimport file exce dan menyimpan datanya di database, ok lanjut saja silahkan teman-teman simak kode import excel dibawah ini.
- Download Library
- Library
- Setting File Autoload
- Controller langkah selanjutnya anda buat file controller dengan
- Buat File View
- Kode
index.php - Kode
create.php
Silahkan download library file Excel : Disini
Silahkan anda extrak file librarinya dan paste di folder libraries dan hasil extrak akan seperti gambar berikut
Silahkan edit file autoload.php seperti dibawah ini dan silahkan tuliskan nama libararinya dengan "excel".
Import.php
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Import extends CI_Controller {
public function index()
{
$data['title'] = 'Import Excel';
$data['mahasiswa'] = $this->db->get('mahasiswa')->result();
$this->load->view('import/index', $data);
}
public function create()
{
$data['title'] = "Upload File Excel";
$this->load->view('import/create', $data);
}
public function excel()
{
if(isset($_FILES["file"]["name"])){
// upload
$file_tmp = $_FILES['file']['tmp_name'];
$file_name = $_FILES['file']['name'];
$file_size =$_FILES['file']['size'];
$file_type=$_FILES['file']['type'];
// move_uploaded_file($file_tmp,"uploads/".$file_name); // simpan filenya di folder uploads
$object = PHPExcel_IOFactory::load($file_tmp);
foreach($object->getWorksheetIterator() as $worksheet){
$highestRow = $worksheet->getHighestRow();
$highestColumn = $worksheet->getHighestColumn();
for($row=4; $row<=$highestRow; $row++){
$nim = $worksheet->getCellByColumnAndRow(0, $row)->getValue();
$nama = $worksheet->getCellByColumnAndRow(1, $row)->getValue();
$angkatan = $worksheet->getCellByColumnAndRow(2, $row)->getValue();
$data[] = array(
'nim' => $nim,
'nama' =>$nama,
'angkatan' =>$angkatan,
);
}
}
$this->db->insert_batch('mahasiswa', $data);
$message = array(
'message'=>'<div class="alert alert-success">Import file excel berhasil disimpan di database</div>',
);
$this->session->set_flashdata($message);
redirect('import');
}
else
{
$message = array(
'message'=>'<div class="alert alert-danger">Import file gagal, coba lagi</div>',
);
$this->session->set_flashdata($message);
redirect('import');
}
}
}
/* End of file Import.php */
/* Location: ./application/controllers/Import.php */
Pada langkah ini silahkan buat sebuah folder dengan nama import dan buat 2 file didalamnya index.php dan create.php
Silahkan kopi kode file view index.php dibawah :
<!DOCTYPE html>
<html lang="in">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Import Excel</title>
<link href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
<nav class="navbar navbar-expand-md navbar-light bg-white shadow-sm">
<div class="container">
<a class="navbar-brand" href="#"></a>
<button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarSupportedContent" aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="{{ __('Toggle navigation') }}">
<span class="navbar-toggler-icon"></span>
</button>
<div class="collapse navbar-collapse" id="navbarSupportedContent">
<ul class="navbar-nav ml-auto">
<li class="nav-item">
<a class="nav-link" href="<?= site_url('import') ?>"><i class="fa fa-users"></i>Data Mahasiswa</a>
</li>
<li class="nav-item">
<a class="nav-link" href="<?= site_url('import/create') ?>"><i class="fa fa-upload"></i>Upload File</a>
</li>
</ul>
</div>
</div>
</nav>
<div class="container mt-3">
<div class="row justify-content-center">
<div class="col-md-8">
<div class="card">
<div class="card-header">
Data Mahasiswa
</div>
<div class="card-body">
<?= $this->session->flashdata('message');?>
<a href="<?= site_url('import/create') ?>" class="btn btn-primary mb-3">Import</a>
<table class="table table-bordered table-striped">
<tr>
<th>Nim</th>
<th>Nama</th>
<th>Angkatan</th>
</tr>
<?php if (count($mahasiswa) > 0) {
foreach ($mahasiswa as $row): ?>
<tr>
<td><?= $row->nim ?></td>
<td><?= $row->nama ?></td>
<td><?= $row->angkatan ?></td>
</tr>
<?php endforeach ?>
<?php }else{ ?>
<tr>
<td colspan="3" class="text-center">Tidak ada data</td>
</tr>
<?php } ?>
</table>
</div>
<div class="card-footer">
Page
</div>
</div>
</div>
</div>
</div>
</body>
</html>
Silahkan kopi kode file view create.php dibawah :
<!DOCTYPE html>
<html lang="in">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Import Excel</title>
<link href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
<nav class="navbar navbar-expand-md navbar-light bg-white shadow-sm">
<div class="container">
<a class="navbar-brand" href="#"></a>
<button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarSupportedContent" aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="{{ __('Toggle navigation') }}">
<span class="navbar-toggler-icon"></span>
</button>
<div class="collapse navbar-collapse" id="navbarSupportedContent">
<ul class="navbar-nav ml-auto">
<li class="nav-item">
<a class="nav-link" href="<?= site_url('import') ?>"><i class="fa fa-home"></i>Data Mahasiswa</a>
</li>
<li class="nav-item">
<a class="nav-link" href="<?= site_url('import/create') ?>"><i class="fa fa-tasks"></i>Upload File</a>
</li>
</ul>
</div>
</div>
</nav>
<div class="container mt-3">
<div class="row justify-content-center">
<div class="col-md-8">
<div class="card">
<div class="card-header">
Upload File Excel
</div>
<form method="POST" action="<?= site_url('import/excel') ?>" enctype="multipart/form-data">
<div class="card-body">
<div class="row">
<div class="col-md-12">
<div class="form-group row">
<div class="col-md-12">
<label class="col-form-label text-md-left">Upload File</label>
<input type="file" class="form-control" name="file" accept=".xls, .xlsx" required>
<div class="mt-1">
<span class="text-secondary">File yang harus diupload : .xls, xlsx</span>
</div>
<?= form_error('file','<div class="text-danger">','</div>') ?>
</div>
</div>
</div>
</div>
</div>
<div class="card-footer text-right">
<div class="form-group mb-0">
<button type="submit" name="import" class="btn btn-primary"><i class="fas fa-upload mr-1"></i>Upload</button>
</div>
</div>
</form>
</div>
</div>
</div>
</div>
</body>
</html>
bang mau nanya untuk kalau format tanggal gmna yah bang
ReplyDeleteuntuk menyimpan format tanggal, kalau misal di excelnya pisah2 colomnya berati digabungin saja menjadi 1 di controllernya, misal :
Delete$tgl = $worksheet->getCellByColumnAndRow(1, $row)->getValue();
$bln = $worksheet->getCellByColumnAndRow(2, $row)->getValue();
$thn = $worksheet->getCellByColumnAndRow(3, $row)->getValue();
'tanggal' =>date('Y', strtotime($thn)) . "-".date('m', strtotime($bln)) . "-".date('d', strtotime($tgl)),
kalo tidak di pisah ??
Deletebang gimana cara mengatasi value kosong saat import ikut tertambah
ReplyDeleteBuatkan kondisi seperti ini :
Delete$sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestDataRow(); $highestColumn = $sheet->getHighestDataColumn(); for ($row = 2; $row <= $highestRow; $row++){ $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row,NULL,TRUE,FALSE); if(isEmptyRow(reset($rowData))) { continue; }
// skip empty row
}
function isEmptyRow($row) { foreach($row as $cell)
{ if (null !== $cell) return false; } return true; }
Di continue saja jika valuenya kosong atau null
Deletebang saya mau bikin kondisi ketika dia kalo data nya belum ada dia INSERT, kalo data nya sudah ada, dia UPDATE, itu gimana ya bang?
ReplyDelete'PHPExcel_IOFactory' not found ... itu gimana ya bang?
ReplyDelete