Halaman Upload untuk Mengimpor Excel ke MySQL Dengan PhpSpreadsheet

Versi sebelumnya di Mengimpor Excel ke MySQL Dengan PhpSpreadsheet kita hanya mengambil file dari folder langsung, kali ini kita akan membuat halaman web dimana user bisa mengupload file Excel.
Buat folder untuk menyimpan script yang akan kita buat, anggap kita buat folder php-excel. Didalam folder php-excel jalankan composer berikut ini untuk menginstall PhpSpreadsheet

composer require phpoffice/phpspreadsheet

Buat file index.html, lalu copy paste script dibawah ini

<!doctype html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <title>Script Import File Excel</title>
</head>
<body>
    <div class="container">
        <h1>Script Import File Excel</h1>
        <form method="post" enctype="multipart/form-data" action="excel.php">
            Pilih File Excel: <br>
            <input name="fileexcel" type="file"><br><br>
            <input name="upload" type="submit" value="Import">
        </form>
    </div>
 
</body>
</html>

File excel yang akan diimport
file excel import php
agar terlihat berbeda kita gunakan data yang berbeda. Buat table baru di MySQL/MariaDB dengan data berikut

CREATE TABLE `contact` (
  `id` int(11) NOT NULL,
  `company` varchar(100) NOT NULL,
  `company_pic` varchar(40) NOT NULL,
  `phone_number` varchar(20) NOT NULL,
  `address` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
ALTER TABLE `contact`
  ADD PRIMARY KEY (`id`);
ALTER TABLE `contact`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
COMMIT;

Untuk mengolah file yang di upload kita gunakan PhpSpreadsheet

<?php
 
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
 
// validasi
if(empty($_FILES['fileexcel']['tmp_name'])) {
    exit("Tidak ada file diupload");
}
 
$file = pathinfo($_FILES['fileexcel']['name']);
 
// ekstensi yang diijinkan
$allowedExtension = ['xlsx', 'xls'];
if(! in_array($file['extension'],$allowedExtension)) {
    exit("Galat! file extension yang diijinkan .xlsx .xls");
 
}
 
$DB_SERVER = "localhost";
$DB_USER = "USER";
$DB_PASSWORD = "PASSWORD";
$DB_NAME = "tutorial";
$mysqli = new mysqli("$DB_SERVER","$DB_USER","$DB_PASSWORD","$DB_NAME");
 
if ($mysqli->connect_errno) {
  echo "Errno: " . $mysqli->connect_errno . "\n";
  echo "Error: " . $mysqli->connect_error . "\n";
  exit;
}
 
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$reader->setReadDataOnly(true);
// lokasi file excel dari file yang di upload
$spreadsheet = $reader->load($_FILES['fileexcel']['tmp_name']);
 
$worksheet = $spreadsheet->getActiveSheet();
$rows = $worksheet->toArray();
 
// hapus baris pertama
unset($rows[0]);
 
$tz = 'Asia/Jakarta';
$dt = new DateTime("now", new DateTimeZone($tz));
$timestamp = $dt->format('Y-m-d G:i:s');
 
$hitungSukses = 0;
$hitungGagal = 0;
 
foreach ($rows as $key => $value) {
    $query = "INSERT INTO contact VALUES (null, '$value[0]', '$value[1]', '$value[2]', '$value[3]', '$timestamp')";
    echo "$query" . "<br>";
    $result = $mysqli->query($query);
    if($result) {
        $hitungSukses++;
    } else {
        $hitungGagal++;
        var_dump(mysqli_error($mysqli));
    }
}
 
echo "<hr>";
echo "Sukses: " . $hitungSukses . "<br>";
echo "Gagal: " . $hitungGagal;

ubah credential diatas menjadi username/password yang digunakan database anda.

Jalankan server development php dari folder php-excel dengan

php -S localhost:9000

Di browser buka http://localhost:9000
interface upload excel php
pilih file Excel yang akan diupload, klik Import
log export excel ke mysql

Script tutorial bisa didownload dibawah ini. Struktur foldernya

php-excel
├── composer.json
├── composer.lock
├── contact.sql
├── contact.xlsx
├── excel.php
├── index.html
└── vendor

Download “php-excel-1.zip” php-excel-1.zip – Downloaded 9 times – 4 MB

Last Updated on 19 February 2020

Tulisan menarik lainnya

Reader Interactions

Leave a Reply Cancel reply