Mengimpor Excel ke MySQL Dengan PhpSpreadsheet

Dengan bantuan PhpSpreadsheet sangat mudah untuk mengimpor file Excel, lalu menyimpannya kedalam database MySQL. Contoh sederhana kita punya file excel sebagai berikut
contoh file excel

Buat tabel baru di database, beri nama excel. Untuk menyimpan data yang diimport dari Excel

CREATE TABLE `excel` (
  `id` INT(11) NOT NULL,
  `item` VARCHAR(100) NOT NULL,
  `jumlah` INT(11) NOT NULL,
  `satuan` VARCHAR(30) NOT NULL,
  `harga_satuan` INT(11) NOT NULL,
  `total` INT(11) NOT NULL,
  `tanggal` DATE NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP()
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
ALTER TABLE `excel`
  ADD PRIMARY KEY (`id`);
 
ALTER TABLE `excel`
  MODIFY `id` INT(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
COMMIT;

Buat satu folder tempat project ini, mari kita beri nama PHP-EXCEL.

Install PhpSpreadsheet dengan composer.

composer require phpoffice/phpspreadsheet

buat file index.php, isi dengan

<?php
 
require 'vendor/autoload.php';
 
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
 
$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
$spreadsheet = $reader->load("/home/tommy/Documents/belanja.xlsx");
 
$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) {
    // format tanggal
    $date = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value[5])->format('Y-m-d');
    $query = "INSERT INTO excel VALUES (null, '$value[0]', '$value[1]', '$value[2]', '$value[3]', '$value[4]', '$date', '$timestamp')";
    $result = $mysqli->query($query);
    if($result) {
        $hitungSukses++;
    } else {
        $hitungGagal++;
    }
}
 
echo "Sukses: " . $hitungSukses . "<br>";
echo "Gagal: " . $hitungGagal;

untuk bagian yang penting sudah diberi komentar singkat. Sekarang jalankan development server untuk test script diatas.

cd PHP-EXCEL
php -S localhost:9090

buka browser anda alamat diatas http://localhost:9090

Cek database anda, sudah terisi

Tulisan menarik lainnya

Leave a comment