Buat Database

Buat database bernama ci_phpexcel, lalu eksekusi script berikut :

CREATE TABLE IF NOT EXISTS `data` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `nama` varchar(100) NOT NULL,
  `waktu_absen` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Saya sengaja menggunakan database yang ada field datetimenya, supaya bisa dilihat bahwa hasil import database dengan menggunakan PHPExcel akurat.

Setelah dibuat, jangan lupa sesuaikan confignya.

Import Data Menggunakan Codeigniter – PHPExcel

Buat controller bernama Php_excel.php. Lalu masukkan script berikut :

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Php_excel extends CI_Controller {

    public $nama_tabel = 'data';//Nama Tabel

    public function __construct()
    {
        parent::__construct();
        $this->load->library("PHPExcel");
        $this->load->model("phpexcel_model");
    }

    public function index(){
        $this->load->view('welcome_message');
    }

    public function import($success=""){
        $data['judul_besar'] = 'PHPExcel';
        $data['judul_kecil'] = 'Import';
        $data['output'] = "<h4>Sebelum mengupload, pastikan file anda berformat <strong>.xls/.xlsx</strong></h4>";
        $data['output'] .= form_open_multipart('php_excel/do_upload');
        $form = array(
                    'name'        => 'userfile',
                    'style'       => 'position:absolute;z-index:2;top:0;left:0;filter: alpha(opacity=0);-ms-filter:progid:DXImageTransform.Microsoft.Alpha(Opacity=0);opacity:0;background-color:transparent;color:transparent;',
                    'onchange'  => "$('#upload-file-info').html($(this).val());"
                );
        $data['output'] .= "<div style='position:relative;'>";
        $data['output'] .= "<a class='btn btn-primary' href='javascript:;'>";
        $data['output'] .= "Browse… ".form_upload($form);
        $data['output'] .= "</a>";
        $data['output'] .= "&nbsp;";
        $data['output'] .= "<span class='label label-info' id='upload-file-info'></span>";
        $data['output'] .= "</div>";
        $data['output'] .= "<br>";
        $data['output'] .= form_submit('name', 'Go !', 'class = "btn btn-default"');
        $data['output'] .= form_close();
        if ($success) {
            $data['pesan'] = '<div class="alert alert-success alert-dismissible">';
            $data['pesan'] .= '<button type="button" class="close" data-dismiss="alert" aria-hidden="true">×</button>';
            $data['pesan'] .= '<h4><i class="icon fa fa-check"></i> Alert!</h4>';
            $data['pesan'] .= 'Success alert preview. This alert is dismissable.';
            $data['pesan'] .= '</div>';
        }

        $this->load->view('welcome_message', $data, FALSE);
    }

    public function do_upload(){
        $config['upload_path'] = './assets/uploads/';
        $config['allowed_types'] = 'xlsx|xls';
        
        $this->load->library('upload', $config);
        
        if ( ! $this->upload->do_upload()){
            $error = array('error' => $this->upload->display_errors());
        }
        else{
            $data = array('upload_data' => $this->upload->data());
            $upload_data = $this->upload->data(); //Mengambil detail data yang di upload
            $filename = $upload_data['file_name'];//Nama File
            $this->phpexcel_model->upload_data($filename);
            unlink('./assets/uploads/'.$filename);
            redirect('php_excel/import/success','refresh');
        }
    }

}

/* End of file Phpexcel.php */
/* Location: ./application/controllers/Phpexcel.php */

Jika sudah, buat Model bernama Phpexcel_model.php, lalu masukkan script berikut :

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Phpexcel_model extends CI_Model {

    public function upload_data($filename){
        ini_set('memory_limit', '-1');
        $inputFileName = './assets/uploads/'.$filename;
        try {
        $objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
        } catch(Exception $e) {
        die('Error loading file :' . $e->getMessage());
        }

        $worksheet = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
        $numRows = count($worksheet);

        for ($i=1; $i < ($numRows+1) ; $i++) { 
            $tgl_asli = str_replace('/', '-', $worksheet[$i]['B']);
            $exp_tgl_asli = explode('-', $tgl_asli);
            $exp_tahun = explode(' ', $exp_tgl_asli[2]);
            $tgl_sql = $exp_tahun[0].'-'.$exp_tgl_asli[0].'-'.$exp_tgl_asli[1].' '.$exp_tahun[1];

            $ins = array(
                    "nama"          => $worksheet[$i]["A"],
                    "waktu_absen"   => $tgl_sql
                   );

            $this->db->insert('data', $ins);
        }
    }

}

/* End of file Phpexcel_model.php */
/* Location: ./application/models/Phpexcel_model.php */

Selesai ! Sekarang coba panggil dari browser.

Leave a Reply

Your email address will not be published.