Giới thiệu
Như chúng ta đã biết trong PHP có một thư viện mà mỗi lập trình viên PHP đều nghĩ tới khi làm việc với excel là phpoffice / phpexcel. Trong quá trình sử dụng thực tế, việc phân tích một hoặc vài trăm dòng bản ghi dữ liệu trong Excel cũng không có vấn đề gì. Nhưng khi gặp file với hàng chục nghìn thậm chí hàng trăm ngàn hay hàng triệu bản ghi thì việc sử dụng bộ nhớ của phpexcel sẽ tăng vọt, có thể dễ dàng vượt quá 128M hoặc hơn. Điều này dẫn tới quá trình xử lý sẽ lâu hơn và đem lại hiệu năng không cao. Vì vậy hôm này mình sẽ sử dụng một thư viện của PHP khác giúp cho việc xử lý dữ liệu lớn với Excel trở nên dễ dàng hơn đó là Box Spout. Và sau đây mình sẽ thực hiện demo nhé@@
Bắt đầu !
Bước 1: Tạo project laravel mới
composer create-project --prefer-dist laravel/laravel import-200k-record-excelBước 2: Tạo database
Tạo database có tên là: import-excel và sau đó config trong file .env
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=import-excel
DB_USERNAME=root
DB_PASSWORD=Bước 3: Tạo bảng imports bằng migration
Tạo 1 bảng có tên là: imports trong bảng này sẽ có 10 trường dữ liệu tương ứng, cái này tùy dự án của các bạn nhé.
php artisan make:migration create_imports_table<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateImportsTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('imports', function (Blueprint $table) {
$table->increments('id');
$table->string('column1');
$table->string('column2');
$table->string('column3');
$table->string('column4');
$table->string('column5');
$table->string('column6');
$table->string('column7');
$table->string('column8');
$table->string('column9');
$table->string('column10');
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('imports');
}
}
Chạy migrate
php artisan migrateBước 4: Cài thư viện Box spout
Mình sẽ cài thư viện bằng cách chạy artisan command
composer require box/spoutBước 5: Đọc file excel và lưu vào database
Vì khi chạy không thông qua giao diện nên mình sẽ tạo 1 command excel:import như sau:
php artisan make:command ImportData<?php
namespace App\Console\Commands;
use Illuminate\Console\Command;
// import thư viện
use Box\Spout\Reader\Common\Creator\ReaderEntityFactory;
use DB;
class ImportData extends Command
{
/**
* The name and signature of the console command.
*
* @var string
*/
protected $signature = 'import:excel';
/**
* The console command description.
*
* @var string
*/
protected $description = 'Import excel';
/**
* Create a new command instance.
*
* @return void
*/
public function __construct()
{
parent::__construct();
}
/**
* Execute the console command.
*
* @return mixed
*/
public function handle()
{
// Thời gian bắt đầu
$this->info('Time start: ' . date("Y-m-d H:i:s"));
// Đọc file excel
$filePath = public_path().'/200k_record.xlsx';
$reader = ReaderEntityFactory::createReaderFromFile($filePath);
$reader->open($filePath);
$data = [];
foreach ($reader->getSheetIterator() as $sheet) {
foreach ($sheet->getRowIterator() as $i => $row) {
ini_set('memory_limit', '-1');
set_time_limit(0);
if ($i == 1)
continue;
$item = $row->toArray();
$created_at = $updated_at = date('Y-m-d H:i:s');
$data[] = [
'column1' => $item[0] ?? '',
'column2' => $item[1] ?? '',
'column3' => $item[2] ?? '',
'column4' => $item[3] ?? '',
'column5' => $item[4] ?? '',
'column6' => $item[5] ?? '',
'column7' => $item[6] ?? '',
'column8' => $item[7] ?? '',
'column9' => $item[8] ?? '',
'column10' => $item[9] ?? '',
'created_at' => $created_at,
'updated_at' => $updated_at
];
// 100 bản ghi insert 1 lần
if ($i % 100 == 0) {
DB::table('imports')->insert($data);
$data = [];
}
}
DB::table('imports')->insert($data);
}
$reader->close();
// Done
$this->info('Time end: ' . date("Y-m-d H:i:s"));
}
}
Bước 6: Tải file mẫu chứa 200k bản ghi
Tải về và lưu ở thư mục public với tên 200k_record. Tải file tại đây
Kết quả
Chạy lệnh
php artisan import:excelVà đây là kết quả sau khi chạy
Time start: 2020-12-30 13:36:45.000000
Time end: 2020-12-30 13:37:36.000000Như vậy có thể thấy gần 200k bản ghi dữ liệu mình import chưa tới 1 phút bao gồm cả đọc file , nạp row vào mảng và thực thi sql.
Thư viện Box spout: https://github.com/box/spout
NGUYỄN DUY




Duy
Cảm ơn bạn <3