Skip to main content

Import Excel XLSX ke dalam Database Mysqli

Langkah kerja
1. Download dan Letakkan dalam folder htdocs
2. Buat file excel.xlsx dengan struktur tersebut, pada folder yang sama
3. Buat database dan tabel dalam mysql dengan struktur tersebut pula.

 Kode

<!DOCTYPE html>
<html>
<head>
  <title>Progress Bar</title>
</head>

<body>
<!-- Progress bar holder -->
<div id="progress" style="width:500px;border:1px solid #ccc;"></div>
<!-- Progress information -->
<div id="information" style="width"></div>


<?php
/**
 * PHPExcel - Excel data import to MySQL database script example
 * ==============================================================================
 *
 * @version v1.0: PHPExcel_excel_to_mysql_demo.php 2016/03/03
 * @copyright Copyright (c) 2016, http://www.ilovephp.net
 * @author Sagar Deshmukh <sagarsdeshmukh91@gmail.com>
 * @SourceOfPHPExcel https://github.com/PHPOffice/PHPExcel, https://sourceforge.net/projects/phpexcelreader/
 * ==============================================================================
 *
 */


set_time_limit(0);

require 'Classes/PHPExcel/IOFactory.php';

// Mysql database
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "simpeg";

$inputfilename = 'sdm.xlsx';
$exceldata = array();

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

//  Read your Excel workbook

try
{
    $inputfiletype = PHPExcel_IOFactory::identify($inputfilename);
    $objReader = PHPExcel_IOFactory::createReader($inputfiletype);
    $objPHPExcel = $objReader->load($inputfilename);
}
catch(Exception $e)
{
    die('Error loading file "'.pathinfo($inputfilename,PATHINFO_BASENAME).'": '.$e->getMessage());
}

//  Get worksheet dimensions
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();

//  Loop through each row of the worksheet in turn


       
for ($row = 2; $row <= $highestRow; $row++) //baris ke 2 (tanpa judul kolom)
{
    // Calculate the percentation http://stackoverflow.com/questions/15298071/progress-bar-with-mysql-query-with-php
    $percent = intval($row/$highestRow * 100)."%";

    // Javascript for updating the progress bar and information
  echo '<script language="javascript">
  document.getElementById("progress").innerHTML="<div style=\"width:'.$percent.';background-color:#ddd;\">&nbsp;</div>";
  document.getElementById("information").innerHTML="'.$row.' data pegawai sedang diproses.";
  </script>';

  // This is for the buffer achieve the minimum size in order to flush data
  echo str_repeat(' ',1024*64);

  // Send output to browser immediately
  flush();

  // Sleep one second so we can see the delay
  sleep(0);
 
    //  Read a row of data into an array
    $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
   
    //  Insert row data array into your database of choice here

    $sql = "REPLACE INTO `sdm` (`id`, `nip`, `nama`, `status_kepegawaian`, `jenis_pegawai`, `lokasi_kerja`, `kelompok`, `unit_kontrak`, `unit_kerja`, `bagian`, `nama_tampa_gelar`, `gelar_depan`, `gelar_belakang`, `kd_kelamin`, `lahir_tempat`, `lahir_tgl`, `kd_agama`, `kd_gol_darah`, `telponhp`, `kd_pendidikan`, `kd_jenis_pendidikan`, `alamat`, `kd_jenis_pegawai`, `kd_status_pegawai`, `kd_kelompok_pegawai`, `kd_pendidikan_masuk`, `kd_status_pendidikan_masuk`, `kd_unit1`, `kd_unit2`, `unitkerja`, `kd_unit3`, `bagian1`, `kd_nikah`, `jml_keluarga`, `jml_anak`, `jml_ditanggung`, `flag_pensiun`, `kd_status_hidup`, `tgl_input`, `user_input`, `tgl_upDATE`, `user_upDATE`, `no_ktp`, `lokasi_kerja1`, `tgl_pensiun`, `nip_kopertis`, `status_data`, `kd_fungsional`, `kd_ruang`, `kd_golongan`, `sk_pertama`, `tgl_sk_pertama`, `masa_kerja_awal`, `masa_kerja_tahun`, `masa_kerja_bulan`, `flag_mengajar`, `flag_sekolah`, `flag_sekantor`, `kd_golongan_kopertis`, `kd_ruang_kopertis`, `instansi_asal_ptt`, `instansi_asal_ket`, `tmt_pangkat`, `tmt_fungsional`, `tmt_pangkat_kopertis`, `kd_fungsional_kopertis`, `tmt_fungsional_kopertis`, `masa_kerja_total_tahun`, `masa_kerja_total_bulan`, `nip_suami_istri`, `nidn`, `no_karpeg`)
   
    VALUES
            (
           
            '".mysqli_real_escape_string($conn, $rowData[0][0])."',
            '".mysqli_real_escape_string($conn, $rowData[0][1])."',
            '".mysqli_real_escape_string($conn, $rowData[0][2])."',
            '".mysqli_real_escape_string($conn, $rowData[0][3])."',
            '".mysqli_real_escape_string($conn, $rowData[0][4])."',
            '".mysqli_real_escape_string($conn, $rowData[0][5])."',
            '".mysqli_real_escape_string($conn, $rowData[0][6])."',
            '".mysqli_real_escape_string($conn, $rowData[0][7])."',
            '".mysqli_real_escape_string($conn, $rowData[0][8])."',
            '".mysqli_real_escape_string($conn, $rowData[0][9])."',
            '".mysqli_real_escape_string($conn, $rowData[0][10])."',

            '".mysqli_real_escape_string($conn, $rowData[0][11])."',
            '".mysqli_real_escape_string($conn, $rowData[0][12])."',
            '".mysqli_real_escape_string($conn, $rowData[0][13])."',
            '".mysqli_real_escape_string($conn, $rowData[0][14])."',
           
            '". date('Y-m-d', strtotime(strtr($rowData[0][15], '/', '-'))) ."',

           
            '".mysqli_real_escape_string($conn, $rowData[0][16])."',
            '".mysqli_real_escape_string($conn, $rowData[0][17])."',
            '".mysqli_real_escape_string($conn, $rowData[0][18])."',
            '".mysqli_real_escape_string($conn, $rowData[0][19])."',
            '".mysqli_real_escape_string($conn, $rowData[0][20])."',

           
            '".mysqli_real_escape_string($conn, $rowData[0][21])."',
            '".mysqli_real_escape_string($conn, $rowData[0][22])."',
            '".mysqli_real_escape_string($conn, $rowData[0][23])."',
            '".mysqli_real_escape_string($conn, $rowData[0][24])."',
            '".mysqli_real_escape_string($conn, $rowData[0][25])."',
            '".mysqli_real_escape_string($conn, $rowData[0][26])."',
            '".mysqli_real_escape_string($conn, $rowData[0][27])."',
            '".mysqli_real_escape_string($conn, $rowData[0][28])."',
            '".mysqli_real_escape_string($conn, $rowData[0][29])."',
            '".mysqli_real_escape_string($conn, $rowData[0][30])."',

            '".mysqli_real_escape_string($conn, $rowData[0][31])."',
            '".mysqli_real_escape_string($conn, $rowData[0][32])."',
            '".mysqli_real_escape_string($conn, $rowData[0][33])."',
            '".mysqli_real_escape_string($conn, $rowData[0][34])."',
            '".mysqli_real_escape_string($conn, $rowData[0][35])."',
            '".mysqli_real_escape_string($conn, $rowData[0][36])."',
            '".mysqli_real_escape_string($conn, $rowData[0][37])."',
            '".date('Y-m-d', strtotime(strtr($rowData[0][38], '/', '-')))."',
            '".mysqli_real_escape_string($conn, $rowData[0][39])."',
            '".date('Y-m-d', strtotime(strtr($rowData[0][40], '/', '-')))."',

            '".mysqli_real_escape_string($conn, $rowData[0][41])."',
            '".mysqli_real_escape_string($conn, $rowData[0][42])."',
            '".mysqli_real_escape_string($conn, $rowData[0][43])."',
            '".date('Y-m-d', strtotime(strtr($rowData[0][44], '/', '-')))."',
            '".mysqli_real_escape_string($conn, $rowData[0][45])."',
            '".mysqli_real_escape_string($conn, $rowData[0][46])."',
            '".mysqli_real_escape_string($conn, $rowData[0][47])."',
            '".mysqli_real_escape_string($conn, $rowData[0][48])."',
            '".mysqli_real_escape_string($conn, $rowData[0][49])."',
            '".mysqli_real_escape_string($conn, $rowData[0][50])."',

            '".date('Y-m-d', strtotime(strtr($rowData[0][51], '/', '-')))."',
            '".mysqli_real_escape_string($conn, $rowData[0][52])."',
            '".mysqli_real_escape_string($conn, $rowData[0][53])."',
            '".mysqli_real_escape_string($conn, $rowData[0][54])."',
            '".mysqli_real_escape_string($conn, $rowData[0][55])."',
            '".mysqli_real_escape_string($conn, $rowData[0][56])."',
            '".mysqli_real_escape_string($conn, $rowData[0][57])."',
            '".mysqli_real_escape_string($conn, $rowData[0][58])."',
            '".mysqli_real_escape_string($conn, $rowData[0][59])."',
            '".mysqli_real_escape_string($conn, $rowData[0][60])."',

            '".mysqli_real_escape_string($conn, $rowData[0][61])."',
            '".mysqli_real_escape_string($conn, $rowData[0][62])."',
            '".mysqli_real_escape_string($conn, $rowData[0][63])."',
            '".mysqli_real_escape_string($conn, $rowData[0][64])."',
            '".mysqli_real_escape_string($conn, $rowData[0][65])."',
            '".mysqli_real_escape_string($conn, $rowData[0][66])."',
            '".mysqli_real_escape_string($conn, $rowData[0][67])."',
            '".mysqli_real_escape_string($conn, $rowData[0][68])."',
            '".mysqli_real_escape_string($conn, $rowData[0][69])."',
            '".mysqli_real_escape_string($conn, $rowData[0][70])."',
           
           
            '".mysqli_real_escape_string($conn, $rowData[0][71])."'
           
            )";
           

           
    if (mysqli_query($conn, $sql)) {

        $exceldata[] = $rowData[0];
    } else {
        echo "Error: " . $sql . "<br>" . mysqli_error($conn);
    }
}


// Print excel data
/* echo "<table>";
foreach ($exceldata as $index => $excelraw)
{
    echo "<tr>";
    foreach ($excelraw as $excelcolumn)
    {
        echo "<td>".$excelcolumn."</td>";
    }
    echo "</tr>";
}
echo "</table>"; */

mysqli_close($conn);

// Tell user that the process is completed

echo '<script language="javascript">document.getElementById("information").innerHTML="Proses selesai"</script>';
?>
</body>
</html>

 Download Source Code

Comments

Post a Comment

Popular posts from this blog

Layar Biru versi PHP Bagian 1 (file prefil_dbf.php)

file config.php <?php $db_uname = 'root'; $db_passwd = ''; $db_name = 'layar_biru'; //database yang dipilih $db_host = 'localhost'; $xbase_dir = 'D:\ACADEMIC\htdocs\layar_biru\files'; $die_on_mysql_error = false; // when investigating errors, set this to true $from_encoding=""; //Encoding of database, e.g. CP866 or empty, if convert is not required     file prefil.dbf   <?php include "config.php";            // please copy the config.sample.php and edit the correct fields include "classes/XBase/Table.php"; include "classes/XBase/Column.php"; include "classes/XBase/Record.php"; include "classes/DBFhandler.php"; use XBase\Table;  // Initializing vars ini_set( 'memory_limit', '2048M' ); set_time_limit( 0 ); $time_start = time(); $files = scandir($xbase_dir) or die ("Error! Could not open directory '$xbase_dir'."); $conn = new mysqli($db_host,...
Post a Comment
Read more

FPDF dengan CodeIgniter

Cetak Surat Keputusan Controller: <?php //File in controller named surat_keputusan.php defined('BASEPATH') OR exit('No direct script access allowed'); class Cetak_surat_keputusan extends CI_Controller { public function __construct()     {         parent::__construct();         $this->load->helper('url');         $this->load->database();                $this->db->select();         $this->db->from('surat.config_sk');                $query = $this->db->get();                 return $query->result();             } public function index() {      ...
1 comment
Read more

Converter dbf ke MYSQL

Banyak tool yang dipakai dan direkomendasikan orang untuk mengkonversi database , khususnya DBF ke MySQL. Ada yang menggunakan / menyarankan Navicat, CDBF for Linux , dbf2mysql, MySQL Migration Toolkit , dll. Bahkan ada pula yang mau bersusah-payah melakukannya dengan cara tradisionil dan ribet, yaitu dengan menggunakan MS Access: ekspor file ini, ganti syntax-nya menjadi sql, bla..bla..bla… dsb. Setelah menyimak semuanya, endingnya ternyata gak bikin happy. Meski begitu saya hargai effort mereka. Karena dari upaya mereka itulah kita menjadi tahu bahwa tool yang ini dan itu tidak cocok  dipakai. Tool yang saya pakai adalah Full Convert Enterprise (FCE). Komen saya terhadap tool ini cuma satu, keren! Kenapa? Karena bukan hanya source DBF saja yang bisa di-convert, tapi juga source lainnya (Interbase/Firebird, Oracle , Paradox, SQL Server, dll). Download: Full Convert Enterprise Full Version di sini (size 4.7 MB) Password: bayusibond Berikut adalah langkah-langkah m...
Post a Comment
Read more