PHPExcel – Import và Export xử lý Excel

Đôi lúc chúng ta sẽ cần phải truy – xuất dữ liệu bằng file Excel như: xuất dữ liệu thống kê ra cho người dùng, hoặc import nhiều dữ liệu từ file excel vào Database. Thư viện được sử dụng nhiều nhất hiện nay là PHPOffice/PHPExcel.

Đọc và ghi file excel bằng PHP thuần

Đọc file excel

Các bạn tải file data mẫu excel này mà mình đã tạo sẵn, có nội dung hình dưới và đặt nó nằm cùng cấp với thư mục Classes:

Tiếp theo, bạn sẽ tiến hành đọc file này bằng cách tạo thêm 1 file mới tên là docfile.php cùng cấp với thư mục Classes, và gõ theo nội dung bên dưới:

<?php
//  Include thư viện PHPExcel_IOFactory vào
include 'Classes/PHPExcel/IOFactory.php';

$inputFileName = 'product.xlsx';

//  Tiến hành đọc file excel
try {
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objPHPExcel = $objReader->load($inputFileName);
} catch(Exception $e) {
    die('Lỗi không thể đọc file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
}

//  Lấy thông tin cơ bản của file excel

// Lấy sheet hiện tại
$sheet = $objPHPExcel->getSheet(0); 

// Lấy tổng số dòng của file, trong trường hợp này là 6 dòng
$highestRow = $sheet->getHighestRow(); 

// Lấy tổng số cột của file, trong trường hợp này là 4 dòng
$highestColumn = $sheet->getHighestColumn();

// Khai báo mảng $rowData chứa dữ liệu

//  Thực hiện việc lặp qua từng dòng của file, để lấy thông tin
for ($row = 1; $row <= $highestRow; $row++){ 
    // Lấy dữ liệu từng dòng và đưa vào mảng $rowData
    $rowData[] = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE,FALSE);
}

//In dữ liệu của mảng
echo "<pre>";
print_r($rowData);
echo "</pre>";

Tiếp theo bạn tiến hành thực thi file docfile.php này, sẽ thấy kết quả in ra màn hình là một mảng chứa tất cả thông tin của file excel product.xlsx.

Array
(
    [0] => Array
        (
            [0] => Array
                (
                    [0] => STT
                    [1] => product_name
                    [2] => quantity
                    [3] => price
                )

        )

    [1] => Array
        (
            [0] => Array
                (
                    [0] => 1
                    [1] => php ebook
                    [2] => 2
                    [3] => 100
                )

        )

    [2] => Array
        (
            [0] => Array
                (
                    [0] => 2
                    [1] => java ebook
                    [2] => 1
                    [3] => 50
                )

        )

    [3] => Array
        (
            [0] => Array
                (
                    [0] => 3
                    [1] => laravel 5
                    [2] => 3
                    [3] => 120
                )

        )

    [4] => Array
        (
            [0] => Array
                (
                    [0] => 4
                    [1] => angularjs
                    [2] => 5
                    [3] => 30
                )

        )

    [5] => Array
        (
            [0] => Array
                (
                    [0] => 5
                    [1] => python
                    [2] => 4
                    [3] => 60
                )

        )

)

từ đó bạn có thể sử dụng mảng này cho mục đích thích hợp của bạn, như lưu vào cơ sở dữ liệu chẳng hạn.

Ghi dữ liệu ra file excel

Tương tự như file excel, chúng ta sẽ tiếp tục dùng thư viện PHPExcel để ghi dữ liệu ra file.

Đầu tiên, các bạn tạo 1 file mới, đặt tên là ghifile.php và 1 file tên là product_import.xlsx (file này tạo ra và để trống, ko cần điền nội dung, vì chúng ta sẽ điền nội dung vào bằng thư viện PHPExcel) và đặt 2 file này cùng cấp với file docfile.php. Các bạn mở file docfile.php lên và gõ nội dung như sau :

<?php
//  Include thư viện PHPExcel_IOFactory vào
include 'Classes/PHPExcel/IOFactory.php';

// Loại file cần ghi là file excel phiên bản 2007 trở đi
$fileType = 'Excel2007';
// Tên file cần ghi
$fileName = 'product_import.xlsx';

// Load file product_import.xlsx lên để tiến hành ghi file
$objPHPExcel = PHPExcel_IOFactory::load("product_import.xlsx");

// Giả sử chúng ta có mảng dữ liệu cần ghi như sau
$array_data = array(
					0 => array('name' => 'Hieu', 'email' => 'hieu@gmail.com', 'phone' => '0123456789', 'address' => 'address 1'),
					1 => array('name' => 'Nam', 'email' => 'nam@gmail.com', 'phone' => '0124567892', 'address' => 'address 2'),
					2 => array('name' => 'Tuan', 'email' => 'tuan@gmail.com', 'phone' => '09764346789', 'address' => 'address 3'),
					3 => array('name' => 'Mai', 'email' => 'mai@gmail.com', 'phone' => '09876543356', 'address' => 'address 4'),
					4 => array('name' => 'Thao', 'email' => 'thao@gmail.com', 'phone' => '0975458979', 'address' => 'address 5'),
				);

// Thiết lập tên các cột dữ liệu
$objPHPExcel->setActiveSheetIndex(0)
                            ->setCellValue('A1', "STT")
                            ->setCellValue('B1', "Name")
                            ->setCellValue('C1', "Email")
                            ->setCellValue('D1', "Phone")
                            ->setCellValue('E1', "Address");

// Lặp qua các dòng dữ liệu trong mảng $array_data và tiến hành ghi dữ liệu vào file excel
$i = 2;
foreach ($array_data as $value) {
	$objPHPExcel->setActiveSheetIndex(0)
								->setCellValue("A$i", "$i")
								->setCellValue("B$i", $value['name'])
	                            ->setCellValue("C$i", $value['email'])
	                            ->setCellValue("D$i", $value['phone'])
	                            ->setCellValue("E$i", $value['address']);
	$i++;
}
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $fileType);
// Tiến hành ghi file
$objWriter->save($fileName);

Cuối cùng chạy file ghifile.php và mở file product_import.xlsx lên, các bạn sẽ thấy dữ liệu đã được ghi vào bao gồm 5 dòng tương ứng số dòng dữ liệu trong array $array_data nhé.

Đọc và ghi file excel bằng Laravel

Import dữ liệu từ file excel

Giả sử bài toán là 1 lượng lớn dữ liệu địa chỉ trong đó có phân cấp Tỉnh/Thành Phố, Quận/Huyện, Xã /Phường. Và ta đã biết 1 Tỉnh/Thành Phố có nhiều Quận/Huyện, 1 Quận/Huyện có nhiều Xã/Phường. Như vậy ta sẽ thiết kế DB với 3 bảng như sau: provinces, districts, wards.

Trong bài demo này mình sẽ sử dụng Laravel và dùng Seed để import dữ liệu. Mình đã tạo project, migrate các bạn có thể xem trong source code. Dữ liệu dùng để demo là Tổng hợp địa giới hành chính Việt Nam.

Trước hết ta cài đặt thư viện vào project Laravel bằng dòng lệnh:

composer require phpoffice/phpexcel

Nhìn vào dữ liệu của file excel trên ta thấy có 3 sheet tỉnh, huyện, xã đã được khai báo khá rõ ràng nên ta có thể tưởng tượng ra được cách làm là đọc dữ liệu từng sheet một và insert vào các bảng tương ứng. Hiện thực hóa ý tưởng trên ta được kết quả:

<?php

use IlluminateDatabaseSeeder;
use AppProvince;
use AppDistrict;
use AppWard;

class AddressSeeder extends Seeder
{
   /**
    * Run the database seeds.
    *
    * @return void
    */
   public function run()
   {
       $objPHPExcel = PHPExcel_IOFactory::load(base_path('addresses.xls')); // load file ra object PHPExcel
       $provinceSheet = $objPHPExcel->setActiveSheetIndex(0); // Set sheet sẽ được đọc dữ liệu
       $highestRow    = $provinceSheet->getHighestRow(); // Lấy số row lớn nhất trong sheet
       for ($row = 2; $row <= $highestRow; $row++) { // For chạy từ 2 vì row 1 là title
           Province::create([
               'id' => $provinceSheet->getCellByColumnAndRow(0, $row)->getValue(), // lấy dữ liệu từng ô theo col và row
               'name' => $provinceSheet->getCellByColumnAndRow(1, $row)->getValue(),
               'type' => $provinceSheet->getCellByColumnAndRow(2, $row)->getValue(),
           ]);
       }
       $districtSheet = $objPHPExcel->setActiveSheetIndex(1);
       $highestRow    = $districtSheet->getHighestRow();
       for ($row = 2; $row <= $highestRow; $row++) {
           District::create([
               'id' => $districtSheet->getCellByColumnAndRow(0, $row)->getValue(),
               'name' => $districtSheet->getCellByColumnAndRow(1, $row)->getValue(),
               'type' => $districtSheet->getCellByColumnAndRow(2, $row)->getValue(),
               'location' => $districtSheet->getCellByColumnAndRow(3, $row)->getValue(),
               'province_id' => $districtSheet->getCellByColumnAndRow(4, $row)->getValue(),
           ]);
       }
       $wardSheet = $objPHPExcel->setActiveSheetIndex(2);
       $highestRow    = $wardSheet->getHighestRow();
       for ($row = 2; $row <= $highestRow; $row++) {
           Ward::create([
               'id' => $wardSheet->getCellByColumnAndRow(0, $row)->getValue(),
               'name' => $wardSheet->getCellByColumnAndRow(1, $row)->getValue(),
               'type' => $wardSheet->getCellByColumnAndRow(2, $row)->getValue(),
               'location' => $wardSheet->getCellByColumnAndRow(3, $row)->getValue(),
               'district_id' => $wardSheet->getCellByColumnAndRow(4, $row)->getValue(),
           ]);
       }
   }
}

Ở đây mới chỉ sử dụng các hàm đơn giản của PHPExcel để đọc dữ liệu ra, mình có comment code rất dễ hiểu. Ngoài các hàm đơn giản trên thì PHPExcel hỗ trợ rất nhiều hàm liên quan đến việc đọc dữ liệu các bạn có thể xem document ở đây.

Export dữ liệu từ DB ra file excel

Ở trên đã import dữ liệu từ file excel vào DB, bây giờ sẽ làm điều ngược lại. Để tăng độ khó bài toán ta sẽ không export ra file giống như cũ mà giả sử khách hàng yêu cầu mình export dữ liệu ra file excel có format như sau:

• Tạo các sheet tương ứng với tên các tỉnh/thành phố.
• Tương ứng với mỗi sheet ghi rõ tên quận/huyện và các xã/phường thuộc quận/huyện đó.

Tạo 1 artisan command để làm việc này. Nội dung như sau:

<?php

namespace AppConsoleCommands;

use AppProvince;
use IlluminateConsoleCommand;

class ExportExcel extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'db:export_to_excel';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Export address to excel';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $provinces = Province::with('districts')->get();
        $objPHPExcel = new PHPExcel();
        foreach ($provinces as $key => $province) {
            $objPHPExcel->createSheet(); // tạo 1 sheet mới
            $activeSheet = $objPHPExcel->setActiveSheetIndex($key);
            $activeSheet->setTitle($province->name); // đặt tên sheet là tên tỉnh
            $activeSheet->setCellValue('A1', 'Quận/Huyện')
                ->setCellValue('B1', 'Xã/Phường')
                ->setCellValue('C1', 'Kinh độ, vĩ độ'); // set title cho dòng đầu tiên
            $i = 2;
            $j = 2;
            foreach ($province->districts as $district) {
                $activeSheet->setCellValue("A$i", $district->type . ' ' . $district->name); // set tên quận/huyện
                foreach ($district->wards as $ward) {
                    $activeSheet->setCellValue("B$j", $ward->type . ' ' . $ward->name); // tương ứng mỗi quận huyện set tên xã/phường
                    $activeSheet->setCellValue("C$j", $ward->location);
                    $j++;
                }
                $rowMerge = $j - 1;
                if ($i < $rowMerge) {
                    $activeSheet->mergeCells("A$i:A$rowMerge"); // merge các cell có cùng 1 quận/huyện
                }
                $i = $j;
            }
            foreach (range('A', 'C') as $columnId) {
                $activeSheet->getColumnDimension($columnId)->setAutoSize(true);
            }
        }
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save(base_path('result.xlsx'));
    }
}

Kết quả nhận được là: