Hướng dẫn dùng phpspreadsheet trong PHP

PhpSpreadsheet là thư viện phát triển để thay thế cho phpExcel, với các tính năng thừa hưởng từ phpexcel.

1. Yêu cầu hệ thống

• PHP hỗ trợ thư viện php_zip

• PHP hỗ trợ thư viện php_xml

• PHP hỗ trợ thư viện php_gd2

2. Cài đặt PhpSpreadsheet

Để cài đặt PhpSpreadsheet cho NukeViet bạn chỉ cần chạy lệnh

composer require phpoffice/phpspreadsheet

3. Sử dụng

5. Một số chú ý

Chú ý khi xuất dữ liệu ra excel

Thông thường ta chỉ cần sử dụng lệnh

$objWorksheet->setCellValue('A' . $i, $value);

Để ghi ra ô 'A' . $i. Cần chú ý một số điểm sau đối với $value.

• Nếu $value là kiểu số, kiểu text thông thường không có các ký tự đặc biệt ta chỉ cần xuất bình thường như trên.

• Nếu $value là giá trị từ trình soạn thảo, chứa các mã html thì cần dùng hàm strip_tags để lọc bỏ các mã html

• Nếu $value chứa khoảng trắng dạng

  $objWorksheet->setCellValue('A' . $i, $value);

  3 ta cần chuyển thành khoảng trắng space thông thường.

• Nếu $value chứa các mã html đặc biệt hãy chuyển nó sang ký tự thông thường bằng hàm

  $objWorksheet->setCellValue('A' . $i, $value);

  5

• Nếu $value bắt đầu bằng ký tự

  $objWorksheet->setCellValue('A' . $i, $value);

  7 hoặc $value là dạng một hàm ví dụ

$value = '=MAX(F2)';

Khi dùng lệnh xuất trên sẽ lỗi, cần ghi ra bằng lệnh

$objWorksheet->setCellValueExplicit('A' . $i, $value, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);

phpExcel

Chú ý: Hiện tại thư viện này đã dừng phát triển và chuyển sang PhpSpreadsheet do đó bạn nên sử dụng PhpSpreadsheet thay thế cho PHPExcel.

1. Điều kiện sử dụng PHPExcel

Để sử dụng được bộ thư viện viết bằng php này thì đòi hỏi cấu hình Server bạn phải như sau:

  PHP version 5.2.0 hoặc mới hơn
  PHP extension php_zip enabled 
  PHP extension php_xml enabled
  PHP extension php_gd2 enabled

2. Cài đặt PHPExcel

Mở

$objWorksheet->setCellValue('A' . $i, $value);

9 ở thư mục gốc website, thêm vào mục

$value = '=MAX(F2)';

0 giá trị

"phpoffice/phpexcel": "1.9.x-dev"

Chú ý: Hiện này nhóm phát triển phpexcel đã xóa nhánh 1.9 trên kho của họ do đó để cài đặt, bạn có thể sử dụng kho dự phòng của tác giả Phan Tấn Dũng tại https://github.com/hoaquynhtim99/phpexcel bằng cách mở

$objWorksheet->setCellValue('A' . $i, $value);

9 ở thư mục gốc website, thêm vào mục

$value = '=MAX(F2)';

0 giá trị

Trong bài viết này mình xin giới thiệu với các bạn một thư viện có tên là Maatwebsite để chúng ta có thể thao tác với Excel một cách dễ dàng(import, export).

2. Cài đặt

Yêu cầu

Để sử dụng được thư viện này chúng ta cần có những yêu cầu như :

• PHP: ^7.0
• Laravel: ^5.5
• PhpSpreadsheet: ^1.6
• Các PHP extension:

  php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config
  

  6,

  php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config
  

  7,

  php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config
  

  8,

  php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config
  

  9,

  php artisan make:export UsersExport --model=User
  

  0,

  php artisan make:export UsersExport --model=User
  

  1,

  php artisan make:export UsersExport --model=User
  

  2 được bật.

Cài đặt vào project

Chạy lệnh để cài đặt package cũng như

php artisan make:export UsersExport --model=User

3

composer require maatwebsite/excel

Trong quá trình cài đặt package này nếu bạn gặp lỗi này tương tự mình

The requested PHP extension ext-gd * is missing from your system. Install or enable PHP's gd extension.

thì các bạn cài thêm extension

php artisan make:export UsersExport --model=User

4 là được, ở đây mình dùng php version là 7.4, bạn sửa tương tự theo version trên máy bạn.

sudo apt-get install php7.4-gd

các bạn đăng ký ServiceProvider trong

php artisan make:export UsersExport --model=User

5

'providers' => [
    /*
     * Package Service Providers...
     */
    Maatwebsite\Excel\ExcelServiceProvider::class,
]

chạy thêm lệnh

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config

để publish config được sinh ra trong

php artisan make:export UsersExport --model=User

6.

Như vậy là cài đặt cũng được hòm hòm rồi, giờ chúng ta sẽ đi vào tìm hiểu các chức năng hữu ích mà package cung cấp.

3. Tìm hiểu chức năng Export

3.1. Export cơ bản

Trước tiên chúng ta cần tạo một class Export được dùng để xử lý các tính năng liên quan đến export

php artisan make:export UsersExport --model=User

Flag

php artisan make:export UsersExport --model=User

7 để cho package hiểu là file này chúng ta sẽ thực thi với object nào, trong trường hợp này nó là

php artisan make:export UsersExport --model=User

8, nó sẽ tự động thêm đoạn code liên quan đến

php artisan make:export UsersExport --model=User

8 vào trong function

namespace App\Exports;

use App\User;
use Maatwebsite\Excel\Concerns\FromCollection;

class UsersExport implements FromCollection
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        return User::all();
    }
}

0. Nếu không có flag

namespace App\Exports;

use App\User;
use Maatwebsite\Excel\Concerns\FromCollection;

class UsersExport implements FromCollection
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        return User::all();
    }
}

1 này thì cũng chả sao nhé các anh chị em.

Câu lệnh trên sẽ giúp chúng ta một file là

namespace App\Exports;

use App\User;
use Maatwebsite\Excel\Concerns\FromCollection;

class UsersExport implements FromCollection
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        return User::all();
    }
}

2 với nội dung.

namespace App\Exports;

use App\User;
use Maatwebsite\Excel\Concerns\FromCollection;

class UsersExport implements FromCollection
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        return User::all();
    }
}

Có hàm

namespace App\Exports;

use App\User;
use Maatwebsite\Excel\Concerns\FromCollection;

class UsersExport implements FromCollection
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        return User::all();
    }
}

0 này giúp chúng ta bóc tách cái rows trong file excel ra dưới dạng collection để xử lý dễ dàng với laravel.

Tiếp theo cần phải tạo một

namespace App\Exports;

use App\User;
use Maatwebsite\Excel\Concerns\FromCollection;

class UsersExport implements FromCollection
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        return User::all();
    }
}

4 trong controller để xử lý việc download

namespace App\Http\Controllers;

use App\Exports\UsersExport;
use Maatwebsite\Excel\Facades\Excel;

class UsersController extends Controller 
{
    public function export() 
    {
        return Excel::download(new UsersExport, 'users.xlsx');
    }
}

Ở đây mình export một file excel dưới dạng extension là

namespace App\Exports;

use App\User;
use Maatwebsite\Excel\Concerns\FromCollection;

class UsersExport implements FromCollection
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        return User::all();
    }
}

5, tuy nhiên bạn có thể tùy chỉnh extension theo định dạng file mà bạn mong muốn như

namespace App\Exports;

use App\User;
use Maatwebsite\Excel\Concerns\FromCollection;

class UsersExport implements FromCollection
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        return User::all();
    }
}

6,

namespace App\Exports;

use App\User;
use Maatwebsite\Excel\Concerns\FromCollection;

class UsersExport implements FromCollection
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        return User::all();
    }
}

7,

namespace App\Exports;

use App\User;
use Maatwebsite\Excel\Concerns\FromCollection;

class UsersExport implements FromCollection
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        return User::all();
    }
}

8,

namespace App\Exports;

use App\User;
use Maatwebsite\Excel\Concerns\FromCollection;

class UsersExport implements FromCollection
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        return User::all();
    }
}

9...

Cuối cùng là tạo thêm một

namespace App\Http\Controllers;

use App\Exports\UsersExport;
use Maatwebsite\Excel\Facades\Excel;

class UsersController extends Controller 
{
    public function export() 
    {
        return Excel::download(new UsersExport, 'users.xlsx');
    }
}

0 để hoàn thành việc download đơn giản một file excel

Route::get('users/export/', 'UsersController@export');

Kết quả

3.2. Thêm tiêu đề cho bảng

Để thêm tiêu đề từng column trong excel chúng ta chỉ cần thêm

namespace App\Http\Controllers;

use App\Exports\UsersExport;
use Maatwebsite\Excel\Facades\Excel;

class UsersController extends Controller 
{
    public function export() 
    {
        return Excel::download(new UsersExport, 'users.xlsx');
    }
}

1 concern để custom tiêu đề, và các tiêu đề sẽ được trả về dưới dạng array viết trong function

namespace App\Http\Controllers;

use App\Exports\UsersExport;
use Maatwebsite\Excel\Facades\Excel;

class UsersController extends Controller 
{
    public function export() 
    {
        return Excel::download(new UsersExport, 'users.xlsx');
    }
}

2

class UsersExport implements FromCollection, WithHeadings
{
    public function headings(): array {
      return [
        '#',
        'Name',
        'Email',
        'Phone',
        'Birthday'
      ];
    }
}

3.3. Xác định giá trị cho từng cột

Ở phần trên mình có giới thiệu về viết tiêu đề cho bảng. Nếu như viết ở trên thì kết quả trả về từ hàm

namespace App\Http\Controllers;

use App\Exports\UsersExport;
use Maatwebsite\Excel\Facades\Excel;

class UsersController extends Controller 
{
    public function export() 
    {
        return Excel::download(new UsersExport, 'users.xlsx');
    }
}

3 sẽ lần lượt

namespace App\Http\Controllers;

use App\Exports\UsersExport;
use Maatwebsite\Excel\Facades\Excel;

class UsersController extends Controller 
{
    public function export() 
    {
        return Excel::download(new UsersExport, 'users.xlsx');
    }
}

4 với từng cột tiêu đề mà chúng ta xác định ở trên.

Để có thể tự xác định riêng giá trị của từng cột chỉ cần

namespace App\Http\Controllers;

use App\Exports\UsersExport;
use Maatwebsite\Excel\Facades\Excel;

class UsersController extends Controller 
{
    public function export() 
    {
        return Excel::download(new UsersExport, 'users.xlsx');
    }
}

5 và viết thêm function

namespace App\Http\Controllers;

use App\Exports\UsersExport;
use Maatwebsite\Excel\Facades\Excel;

class UsersController extends Controller 
{
    public function export() 
    {
        return Excel::download(new UsersExport, 'users.xlsx');
    }
}

4 vô là được

The requested PHP extension ext-gd * is missing from your system. Install or enable PHP's gd extension.

0

3.4. Căn chỉnh chiều cao chiều rộng

Chắc chắn khi làm việc với dữ liệu lúc export ra excel, sẽ có những giá trị dài ngoằng ngoẵng khiến text bị che mất đi, mà giao diện download về nhìn cũng không đẹp đẽ gì. Để định dạng đẹp đẽ hơn chỉ cần

namespace App\Http\Controllers;

use App\Exports\UsersExport;
use Maatwebsite\Excel\Facades\Excel;

class UsersController extends Controller 
{
    public function export() 
    {
        return Excel::download(new UsersExport, 'users.xlsx');
    }
}

7, nó sẽ tự động điều chỉnh độ rộng của cột sao cho phù hợp với giá trị tương ứng.

The requested PHP extension ext-gd * is missing from your system. Install or enable PHP's gd extension.

1

Hoặc nếu chúng ta muốn tự kiểm soát động rộng của cột thì hãy

namespace App\Http\Controllers;

use App\Exports\UsersExport;
use Maatwebsite\Excel\Facades\Excel;

class UsersController extends Controller 
{
    public function export() 
    {
        return Excel::download(new UsersExport, 'users.xlsx');
    }
}

8 và viết thêm hàm

namespace App\Http\Controllers;

use App\Exports\UsersExport;
use Maatwebsite\Excel\Facades\Excel;

class UsersController extends Controller 
{
    public function export() 
    {
        return Excel::download(new UsersExport, 'users.xlsx');
    }
}

9 với giả trị trả về là một mảng các cột và động rộng tương ứng

The requested PHP extension ext-gd * is missing from your system. Install or enable PHP's gd extension.

2

3.5. Định dạng style

Với việc

Route::get('users/export/', 'UsersController@export');

0 sẽ giúp chúng ta dễ dàng định dạng giá trị được hiển thị trong excel

The requested PHP extension ext-gd * is missing from your system. Install or enable PHP's gd extension.

3

Ngoài ra về phần Export hỗ trợ chúng ta rất nhiều trong việc thiết kế file excel cũng như xử lý sao cho mượt mà nhất, ở đây mình chỉ giới thiệu vài thứ cơ bản để các bạn có thể đọc qua là hiểu và áp dụng được luôn.

4. Tìm hiểu chức năng Import

4.1. Import cơ bản

Tương tự như chức năng

Route::get('users/export/', 'UsersController@export');

1 chúng ta cần tạo một file để xử lý việc

Route::get('users/export/', 'UsersController@export');

2.

The requested PHP extension ext-gd * is missing from your system. Install or enable PHP's gd extension.

4

Sau câu lệnh này sẽ tạo cho chúng ta file

Route::get('users/export/', 'UsersController@export');

3 nằm trong

Route::get('users/export/', 'UsersController@export');

4 với nội dung

The requested PHP extension ext-gd * is missing from your system. Install or enable PHP's gd extension.

5

Muốn lấy dữ liệu để đọc trong file excel theo tiêu đề cột thì phải

Route::get('users/export/', 'UsersController@export');

5 để nó hiểu được là chúng ta đang sử dụng tiêu đề là key.

The requested PHP extension ext-gd * is missing from your system. Install or enable PHP's gd extension.

6

Mặc định thì

Route::get('users/export/', 'UsersController@export');

6 sử dụng helper

Route::get('users/export/', 'UsersController@export');

7 để format lại key.

Ví dụ tiêu đề cột của bạn là "Họ và tên", thì khi gọi tới key này sẽ là

Route::get('users/export/', 'UsersController@export');

8. Ngoài ta chúng ta cũng có thể tự động fornat lại định dạng key nếu muốn.

Để import file từ dưới local lên thì bạn thêm vào trong controller xử lí việc

Route::get('users/export/', 'UsersController@export');

2

The requested PHP extension ext-gd * is missing from your system. Install or enable PHP's gd extension.

7

Ở đây chúng ta sử dụng

class UsersExport implements FromCollection, WithHeadings
{
    public function headings(): array {
      return [
        '#',
        'Name',
        'Email',
        'Phone',
        'Birthday'
      ];
    }
}

0 và

class UsersExport implements FromCollection, WithHeadings
{
    public function headings(): array {
      return [
        '#',
        'Name',
        'Email',
        'Phone',
        'Birthday'
      ];
    }
}

1 truyền vào bên trong 2 tham số vào, tham số thứ nhất là object

Route::get('users/export/', 'UsersController@export');

3 xử lý các thao tác liên quan đến excel, và tham số thứ hai là

class UsersExport implements FromCollection, WithHeadings
{
    public function headings(): array {
      return [
        '#',
        'Name',
        'Email',
        'Phone',
        'Birthday'
      ];
    }
}

3 mà bạn muốn upload lên server.

Cuối cùng là tạo thêm một

namespace App\Http\Controllers;

use App\Exports\UsersExport;
use Maatwebsite\Excel\Facades\Excel;

class UsersController extends Controller 
{
    public function export() 
    {
        return Excel::download(new UsersExport, 'users.xlsx');
    }
}

0 để gọi tới controller xử lý việc import

The requested PHP extension ext-gd * is missing from your system. Install or enable PHP's gd extension.

8

4.2. Dòng bắt đầu của tiêu đề

Trong một số trường hợp,

Route::get('users/export/', 'UsersController@export');

6 của chúng ta không nằm ở dòng đầu tiêu, mà nằm ở dòng số 2 chả hạn thì viết thêm hàm

class UsersExport implements FromCollection, WithHeadings
{
    public function headings(): array {
      return [
        '#',
        'Name',
        'Email',
        'Phone',
        'Birthday'
      ];
    }
}

6 để xử lý vấn đề này

The requested PHP extension ext-gd * is missing from your system. Install or enable PHP's gd extension.

9

4.3. Cập nhật hay tạo mới

Mặc định khi bạn import 1 file lên nó sẽ là việc tạo mới record. Tuy nhiên vấn đề này khá là bất cập khi có những trường đã tồn tại rồi thay vì tạo thêm 1 bản ghi nữa chúng ta chỉ muốn update nó thôi thì

class UsersExport implements FromCollection, WithHeadings
{
    public function headings(): array {
      return [
        '#',
        'Name',
        'Email',
        'Phone',
        'Birthday'
      ];
    }
}

7 ngay để giải quyết việc này.

Ví dụ nếu như một email trong CSDL đã tồn tại rồi thì nó sẽ update chứ không phải tạo mới

sudo apt-get install php7.4-gd

0

Khi viết như trên các bạn có thể hiểu là chúng ta đang unique một column trong CSDL, ý là chỉ có thể có một giá trị như này trong một cột thôi.

4.4. Batch inserts

Khi import dữ liệu không tránh khỏi những lúc dữ liệu trả về quá nhiều dẫn đến việc quá tải, để xử lý vấn đề này thì chúng ta có thể nghĩ đến một cách là trong một thời điểm chỉ cho phép tạo bao nhiêu dữ liệu thôi. Nếu như bạn nghĩ đến cách này thì đây là phần viết dành cho bạn =))

class UsersExport implements FromCollection, WithHeadings
{
    public function headings(): array {
      return [
        '#',
        'Name',
        'Email',
        'Phone',
        'Birthday'
      ];
    }
}

8 cho phép bạn giới hạn lượng query được thực hiện

sudo apt-get install php7.4-gd

1

Cũng tương tự với

class UsersExport implements FromCollection, WithHeadings
{
    public function headings(): array {
      return [
        '#',
        'Name',
        'Email',
        'Phone',
        'Birthday'
      ];
    }
}

9 là chúng ta sẽ

class UsersExport implements FromCollection, WithHeadings
{
    public function headings(): array {
      return [
        '#',
        'Name',
        'Email',
        'Phone',
        'Birthday'
      ];
    }
}

7 thay vì

The requested PHP extension ext-gd * is missing from your system. Install or enable PHP's gd extension.

01

4.5. Chunk reading

Cũng hơi giống với

The requested PHP extension ext-gd * is missing from your system. Install or enable PHP's gd extension.

02 thì chúng ta còn một cách khác để giải quyết việc quá tải khi dữ liệu được import quá lớn đó là

The requested PHP extension ext-gd * is missing from your system. Install or enable PHP's gd extension.

03 và thêm hàm

The requested PHP extension ext-gd * is missing from your system. Install or enable PHP's gd extension.

04 để thư viện lấy lần lượt số lượng rows được định nghĩa ra để xử lý chứ không phải lấy tất cả các bản ghi cùng một lúc sẽ dẫn đến việc server bị quá tải

sudo apt-get install php7.4-gd

2

Ở trường hợp này, mỗi lần server sẽ chỉ đọc 200 bản ghi trước để xử lý rồi mới tiếp tục lấy thêm.

Kết luận

Ngoài ra còn rất nhiều các inteface được tạo ra giúp chúng ta thao tác với việc import với excel rất dễ dàng mình không thể viết hết trong một bài viết được mà chỉ liệt kê những tính năng cơ bản và tiêu biểu của package này đem giới thiệu với các bạn.