Import/Export dữ liệu từ file excel sử dụng thư viện PHPExcel

Đã bao giờ bạn làm việc với khách hàng mà họ yêu cầu mình import 1 lượng dữ liệu lớn vào database hoặc export dữ liệu ra file excel chưa? Hôm nay mình sẽ hướng dẫn các bạn làm điều đó. Ngày trước mình có làm việc với 1 khách hàng họ giao cho mình 1 lượng lớn dữ liệu về địa chỉ của Nhật và yêu cầu mình làm import vào DB làm master data. Nhìn sơ qua thì có khoảng 17.000 rows dữ liệu cần được import và đương nhiên mình sẽ nghĩ ngay đến việc dùng code để import. Thư viện mình đã dùng là PHPOffice/PHPExcel. Bắt đầu thôi nào...

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, migrat 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ình 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

Ở trên mình đã import dữ liệu từ file excel vào DB bây giờ mình sẽ làm điều ngược lại. Để tăng độ khó bài toán mình 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 đó. Mình sẽ 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à

Như vậy mình đã trình bày xong cách export từ database ra 1 file excel. Cảm ơn các bạn đã đọc.

Tham khảo

• PHPExcel
• Source Code