Please enable JavaScript!
Bitte aktiviere JavaScript!
S'il vous plaît activer JavaScript!
Por favor,activa el JavaScript!
antiblock.org

 |  | 
Anda ingin membuat website?
Untuk Anda yang sedang mencari orang untuk jasa pembuatan website, Saya akan bantu buatkan aplikasi sesuai kebutuhan Anda. Segera hubungi : [email protected]. Selengkapnya tentang profil saya klik www.rizaldimaulidia.com.

PHP

Cara Membuat Import Data Excel dengan PhpSpreadsheet

img-responsive

Disini saya akan melanjutkan pembuatan tutorial tentang excel dengan librari PhpSpreadsheet, setelah sebelumnya saya buat tutorial cara export data ke excel dengan PhpSpreadsheet. Kenapa saya buat tutorial import data excel lagi? Karena pada tutorial import data excel sebelumnya, saya masih menggunakan Librari PHPExcel, dimana librari tersebut hanya mendukung sampai PHP Versi 7.2.8. Maka dari itu, tutorial ini dibuat agar teman-teman semua tetap bisa membuat improt data excel walaupun sudah pakai PHP versi terbaru sekalipun (PHP 8). Yuk simak step by stepnya dibawah ini.

PENTING, MOHON DIBACA TERLEBIH DAHULU
Tutorial ini menggunakan librari PhpSpreadsheet untuk proses import excelnya. Librari ini hanya mendukung PHP versi 7.2 ke atas. Bagi teman-teman yang menggunakan PHP versi 7.2 ke bawah, bisa baca tutorial ini : Cara Membuat Import Data Excel dengan PHP dan MySQL.


Berikut adalah fitur-fitur yang akan kita buat:

  1. View Data dari Database.
  2. Form untuk Import Data.
  3. Preview Data yang akan diimport sebelum di simpan ke database.
  4. Validasi terhadap file yang dipilih oleh User pada form import. Hanya file Microsoft Office 2007 (.xlsx) yang diperbolehkan.
  5. 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
Berikut ini adalah hal-hal yang harus dipersiapkan :

  1. Buat sebuah folder dengan nama import_php, lalu simpan pada folder xampp/htdocs/.
    Path xampp nya sesuaikan dengan yang ada di komputer teman-teman masing-masing.
  2. Buat sebuah folder dengan nama tmp, lalu simpan pada folder xampp/htdocs/import_php/.
  3. Buat sebuah folder dengan nama js, lalu simpan pada folder xampp/htdocs/import_php/.
  4. Download librari JQuery, Klik link berikut : Download.
  5. Download format import data excel, klik link berikut : Download. File ini berfungsi sebagai format excel yang digunakan untuk import data.
  6. Rename file JQuery yang telah di download tadi menjadi jquery.min.js, lalu copy and paste pada folder xampp/htdocs/import_php/js/.
  7. Copy and paste file Format.xlsx yang telah didownload ke folder xampp/htdocs/import_php/.

STEP 2 – Download PhpSpreadsheet
Pada langkah ini kita akan coba download librari PhpSpreadsheet nya dengan menggunakan Composer. Belum tau apa itu Composer? Cara installnya gimana? silahkan mampir dulu ke tutorial cara install composer berikut : Cara Install di Windows / Cara Install di Linux.

Setelah memastikan teman-teman sudah menginstall composernya, silahkan ikuti langkah berikut :

  1. Buka Command Prompt atau CMD.
  2. Ketik dan hit perintah berikut : cd C:\xampp\htdocs\export_excel.
    Silahkan sesuaikan path tempat teman-teman menyimpan xampp nya.
  3. Ketik dan hit perintah berikut : composer require phpoffice/phpspreadsheet. Tunggu proses download sampai selesai.
  4. Setelah teman-teman mengeksekusi perintah composer diatas, maka seharusnya di folder import_php nya terdapat file/folder baru berikut : folder vendor, file composer.json, file composer.lock.


STEP 3 – DATABASE
Pada step ini, kita akan membuat databasenya. ikuti langkah-langkah berikut ini :

  1. Buat sebuah database dengan nama mynotescode.
  2. Buat sebuah tabel dengan nama siswa. Struktur tabelnya sebagai berikut :
    Struktur Tabel - Cara Membuat Import Data Excel dengan PHP dan MySQL

    CREATE TABLE IF NOT EXISTS `siswa` (    
      `nis` varchar(11) NOT NULL,    
      `nama` varchar(50) NOT NULL,    
      `jenis_kelamin` varchar(10) NOT NULL,    
      `telp` varchar(15) NOT NULL,    
      `alamat` text NOT NULL,    
      PRIMARY KEY (`nis`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

STEP 4 – KONEKSI DATABASE
Pada step ini, kita akan membuat file php yang berfungsi untuk menghubungkan dengan database MySQL. Silahkan buat file koneksi.php, lalu simpan di folder xampp/htdocs/import_php/. Berikut ini kodenya :

<?php
$host = "localhost"; // Nama hostnya
$username = "root"; // Username
$password = ""; // Password (Isi jika menggunakan password)
$database = "mynotescode"; // Nama databasenya

$connect = mysqli_connect($host, $username, $password, $database); // Koneksi ke MySQL
?>

STEP 5 – VIEW DATA
Selanjutnya kita akan membuat sebuah file php yang berfungsi untuk menampilkan data hasil import data dari database. Buat sebuah file baru dengan nama index.php, lalu simpan pada folder xampp/htdocs/import_php/. Berikut ini tampilan dan kodenya :

View - Cara Membuat Import Data Excel dengan PhpSpreadsheet

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">

    <title>Import Data Excel dengan PhpSpreadsheet</title>

</head>

<body>
    <h3>Data Siswa Hasil Import</h3>

    <a href="form.php">Import Data</a><br><br>

    <table border="1" cellpadding="5">
        <tr>
            <th>No</th>
            <th>NIS</th>
            <th>Nama</th>
            <th>Jenis Kelamin</th>
            <th>Telepon</th>
            <th>Alamat</th>
        </tr>
        <?php
        // Load file koneksi.php
        include "koneksi.php";

        // Buat query untuk menampilkan semua data siswa
        $sql = mysqli_query($connect, "SELECT * FROM siswa");

        $no = 1; // Untuk penomoran tabel, di awal set dengan 1
        while ($data = mysqli_fetch_array($sql)) { // Ambil semua data dari hasil eksekusi $sql
            echo "<tr>";
            echo "<td>" . $no . "</td>";
            echo "<td>" . $data['nis'] . "</td>";
            echo "<td>" . $data['nama'] . "</td>";
            echo "<td>" . $data['jenis_kelamin'] . "</td>";
            echo "<td>" . $data['telp'] . "</td>";
            echo "<td>" . $data['alamat'] . "</td>";
            echo "</tr>";

            $no++; // Tambah 1 setiap kali looping
        }
        ?>
    </table>
</body>

</html>

Saya akan coba jelaskan sedikit mengenai kode diatas.

include “koneksi.php”;
Kode tersebut berfungsi untuk me-load file koneksi.php.

$sql = mysqli_query($connect, “SELECT * FROM siswa”);
Kode tersebut berfungsi untuk melakukan query ke database dan mengeksekusinya. Pada cotoh diatas, kita akan melakukan query untuk menampilkan semua data dari tabel siswa.
$data = mysqli_fetch_array($sql)
Kode tersebut berfungsi untuk mengambil semua data hasil query dan menampung data-data tersebut di dalam sebuah array lalu menyimpannya ke dalam variabel $data. Kode tersebut saya simpan di dalam sebuah kode while(….) yang berfungsi untuk melakukan proses perulangan sampai data terkahir dengan tujuan agar kita bisa menampilkan datanya satu per satu.
echo "<tr>";
echo "<td>".$no."</td>";
echo "<td>".$data['nis']."</td>";
echo "<td>".$data['nama']."</td>";
echo "<td>".$data['jenis_kelamin']."</td>";
echo "<td>".$data['telp']."</td>";
echo "<td>".$data['alamat']."</td>";
echo "</tr>";

Pada kode diatas terdapat variabel $data digunakan untuk mengambil isi dari field-field yang ada pada tabel siswa di database mynotescode. Pada kode diatas juga ada kode yang berada pada tanda [‘……’], kode tersebut harus sama dengan nama field yang ada pada tabel siswa.


STEP 6 – FORM DAN PREVIEW
Langkah selanjutnya kita akan membuat sebuah form untuk import data excelnya. Buat sebuah file baru dengan nama form.php, lalu simpan pada folder xampp/htdocs/import_php/. Berikut tampilan dan kodenya :

Form - Cara Membuat Import Data Excel dengan PhpSpreadsheet

<?php
// Load file autoload.php
require 'vendor/autoload.php';

// Include librari PhpSpreadsheet
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
?>

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">

    <title>Import Data Excel dengan PhpSpreadsheet</title>

    <!-- Load File jquery.min.js yang ada difolder js -->
    <script src="js/jquery.min.js"></script>

    <script>
        $(document).ready(function() {
            // Sembunyikan alert validasi kosong
            $("#kosong").hide();
        });
    </script>
</head>

<body>
    <h3>Form Import Data</h3>

    <form method="post" action="form.php" enctype="multipart/form-data">
        <a href="Format.xlsx">Download Format</a> &nbsp;|&nbsp;
        <a href="index.php">Kembali</a>
        <br><br>

        <input type="file" name="file">
        <button type="submit" name="preview">Preview</button>
    </form>
    <hr>

    <?php
    // Jika user telah mengklik tombol Preview
    if (isset($_POST['preview'])) {
        $tgl_sekarang = date('YmdHis'); // Ini akan mengambil waktu sekarang dengan format yyyymmddHHiiss
        $nama_file_baru = 'data' . $tgl_sekarang . '.xlsx';

        // Cek apakah terdapat file data.xlsx pada folder tmp
        if (is_file('tmp/' . $nama_file_baru)) // Jika file tersebut ada
            unlink('tmp/' . $nama_file_baru); // Hapus file tersebut

        $ext = pathinfo($_FILES['file']['name'], PATHINFO_EXTENSION); // Ambil ekstensi filenya apa
        $tmp_file = $_FILES['file']['tmp_name'];

        // Cek apakah file yang diupload adalah file Excel 2007 (.xlsx)
        if ($ext == "xlsx") {
            // Upload file yang dipilih ke folder tmp
            // dan rename file tersebut menjadi data{tglsekarang}.xlsx
            // {tglsekarang} diganti jadi tanggal sekarang dengan format yyyymmddHHiiss
            // Contoh nama file setelah di rename : data20210814192500.xlsx
            move_uploaded_file($tmp_file, 'tmp/' . $nama_file_baru);

            $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
            $spreadsheet = $reader->load('tmp/' . $nama_file_baru); // Load file yang tadi diupload ke folder tmp
            $sheet = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);

            // Buat sebuah tag form untuk proses import data ke database
            echo "<form method='post' action='import.php'>";

            // Disini kita buat input type hidden yg isinya adalah nama file excel yg diupload
            // ini tujuannya agar ketika import, kita memilih file yang tepat (sesuai yg diupload)
            echo "<input type='hidden' name='namafile' value='" . $nama_file_baru . "'>";

            // Buat sebuah div untuk alert validasi kosong
            echo "<div id='kosong' style='color: red;margin-bottom: 10px;'>
          Semua data belum diisi, Ada <span id='jumlah_kosong'></span> data yang belum diisi.
                </div>";

            echo "<table border='1' cellpadding='5'>
          <tr>
            <th colspan='5' class='text-center'>Preview Data</th>
          </tr>
          <tr>
            <th>NIS</th>
            <th>Nama</th>
            <th>Jenis Kelamin</th>
            <th>Telepon</th>
            <th>Alamat</th>
          </tr>";

            $numrow = 1;
            $kosong = 0;
            foreach ($sheet as $row) { // Lakukan perulangan dari data yang ada di excel
                // 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
                $telp = $row['D']; // Ambil data telepon
                $alamat = $row['E']; // Ambil data alamat

                // Cek jika semua data tidak diisi
                if ($nis == "" && $nama == "" && $jenis_kelamin == "" && $telp == "" && $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
                    $telp_td = (!empty($telp)) ? "" : " style='background: #E07171;'"; // Jika Telepon 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 $telp == "" 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" . $telp_td . ">" . $telp . "</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 "</form>";
        } else { // Jika file yang diupload bukan File Excel 2007 (.xlsx)
            // Munculkan pesan validasi
            echo "<div style='color: red;margin-bottom: 10px;'>
          Hanya File Excel 2007 (.xlsx) yang diperbolehkan
                </div>";
        }
    }
    ?>
</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:

  1. Validasi apakah file yang dipilih memiliki format microsoft excel 2007 (.xlsx) atau tidak
  2. 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)
  3. 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.


STEP 7 – PROSES IMPORT
Terakhir, kita akan membuat file php untuk memproses import data ke databasenya. Buat sebuah file baru dengan nama import.php, lalu simpan pada folder xampp/htdocs/import_php/. Berikut ini kodenya :

<?php
// Load file koneksi.php
include "koneksi.php";

// Load file autoload.php
require 'vendor/autoload.php';

// Include librari PhpSpreadsheet
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;

if(isset($_POST['import'])){ // Jika user mengklik tombol Import
  $nama_file_baru = $_POST['namafile'];
    $path = 'tmp/' . $nama_file_baru; // Set tempat menyimpan file tersebut dimana

    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
    $spreadsheet = $reader->load($path); // Load file yang tadi diupload ke folder tmp
    $sheet = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);

  $numrow = 1;
  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
    $telp = $row['D']; // Ambil data telepon
    $alamat = $row['E']; // Ambil data alamat

    // Cek jika semua data tidak diisi
    if($nis == "" && $nama == "" && $jenis_kelamin == "" && $telp == "" && $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){
      // Buat query Insert
      $query = "INSERT INTO siswa VALUES('" . $nis . "','" . $nama . "','" . $jenis_kelamin . "','" . $telp . "','" . $alamat . "')";

      // Eksekusi $query
      mysqli_query($connect, $query);
    }

    $numrow++; // Tambah 1 setiap kali looping
  }

    unlink($path); // Hapus file excel yg telah diupload, ini agar tidak terjadi penumpukan file
}

header('location: index.php'); // Redirect ke halaman awal

Sedikit tambahan penjelasan dari skrip diatas :

$nis = $row[‘A‘]; // Ambil data NIS
$nama = $row[‘B‘]; // Ambil data nama
$jenis_kelamin = $row[‘C‘]; // Ambil data jenis kelamin
$telp = $row[‘D‘]; // Ambil data telepon
$alamat = $row[‘E‘]; // Ambil data alamat
Yang saya beri warna merah pada skrip diatas harus sama dengan nama kolom yang ada d file excel yang akan diimport. Lihat gambar dibawah ini untuk lebih jelasnya :

Format Import - Cara Membuat Import Data Excel dengan PhpSpreadsheet


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 salah satu link dibawah ini
– Download versi MySQLi tanpa Bootstrap (Link download yang sesuai tutorial ini)
– Download versi MySQLi dengan Bootstrap 3
– Download versi MySQLi dengan Bootstrap 4
– 
Download versi PDO tanpa Bootstrap
– Download versi PDO dengan Bootstrap 3
– Download versi PDO dengan Bootstrap 4


Sumber & Referensi
Dokumentasi PhpSpreadsheet : https://phpspreadsheet.readthedocs.io

Tutorial import data dari excel dengan phpspreadsheet, php dan mysql, Tutorial import data dari excel dengan phpspreadsheet, Cara import data excel dengan phpspreadsheet, php dan mysql, import data excel dengan phpspreadsheet, import data dari excel dengan phpspreadsheet, php dan mysql, Source Code import data excel dengan phpspreadsheet dan MySQL

Import Data

(Total : 175 viewers, 8 viewers today)
import-excel-phpspreadsheet-php-mysql

ABOUT THE AUTHOR

Interested in android programming, long time focused on web development. Visit My Profile Site at www.rizaldimaulidia.com

POST YOUR COMMENTS

Your email address will not be published. Required fields are marked *

Name *

Email *

Website

4 Comments

  1. arif amanu

    jika diatas tabel ada baris tambahan berupa judul (misal kelas, tanggal)
    bagaimana caranya untuk import data di cell tersebut?

    • menurut saya, lebih baik tambahan tersebut di simpan di form importnya saja. jadi form importnya tuh bukan cuman pilih file excel, tapi plus inputan tambahan yg om maksud. jd biar excelnya ga ada tambahan-tambahan seperti itu. saya biasanya seperti ini kalau ada kasus pengen tambahan kaya om

      • arif amanu

        Sangat membantu informasinya, bisa di buat dengan cara begitu, namun saya ingin tahu, ada tidak cara mengambil data di cell tertentu bukan yang dalam tabel saja, mohon pencerahan.

        • Tentunya ada. coba om perhatikan di file form.php atau import.php, ada skrip berikut :
          $sheet = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);

          Skrip diatas untuk get semua data dalam cell excelnya. coba setelah skrip diatas, tambahkan skrip berikut :
          echo "<pre>";
          print_r($sheet);
          echo "</pre>";
          die;

          Setelah dijalankan, bisa dilihat bahwa isi dari $sheet itu array. dimana dari sini kita bisa akses cell tertentu yg kita inginkan. misalkan saya mau get value dari cell B2. cara get nya seperti ini :
          $sheet['2']['B'];

          ['2'] < -- angka ini menunjukkan baris berapa [‘B’] <-- huruf ini menunjukkan kolom apa

NOTE ARCHIVES