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

Menggenerate nomor id

<?php //config $database = "dopo"; $tabel = "po"; $kolom_generate ="id"; $kolom_referensi = "id"; /* Database connection start */ $servername = "localhost"; $username = "root"; $password = ""; $dbname = $database; $mysqli = mysqli_connect($servername, $username, $password, $dbname) or die("Connection failed: " . mysqli_connect_error()); $q1 = "select * from $tabel order by $kolom_referensi DESC"; /* Database connection end */ echo '<table>'; // connect to the database // number of results to show per page $per_page = 10000; // figure out the total pages in the database if ($result = $mysqli->query($q1)) { if ($result->num_rows != 0) { $total_results = $result->num_rows; // ceil() returns the next highest integer value by rounding up value if necessary $total_pages = ceil($total_results / $per_page); // check if the 'page' variab...
Post a Comment
Read more

Membuat Codeigniter PDF di CPANEL

1. Download FPDF dan copykan di folder application/thirdparty/pdf/ 2. Buat file Fpdf_gen.php di libraries. <?php if ( ! defined('BASEPATH')) exit('No direct script access allowed'); class Fpdf_gen { public function __construct() {  require_once APPPATH.'third_party/fpdf/fpdf-1.8.php'; define('FPDF_FONTPATH', APPPATH.'third_party/fpdf/font/'); $pdf = new FPDF(); $pdf->AddPage(); $CI =& get_instance(); $CI->fpdf = $pdf; } public function Footer() { $this->fpdf->SetY(-15); $this->fpdf->SetFont('Arial','I',8); $this->fpdf->SetTextColor(128); $this->fpdf->Cell(0,10,'Page ',0,0,'C'); } } 3. Buat file pdf.php di controller <?php if ( ! defined('BASEPATH')) exit('No direct script access allowed'); defined('BASEPATH') OR exit('No direct script access allowed'); class Pdf extends CI_Controller {   public f...
Post a Comment
Read more