Setactivesheetindex phpspreadsheet

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

Đọc và ghi tệp excel bằng PHP thuần

Đọc file excel

Các bạn tải file excel mẫu dữ liệu này do mình tạo sẵn, có cấu hình bên dưới và đặt nó cùng cấp với thư mục Classes

Setactivesheetindex phpspreadsheet
Setactivesheetindex phpspreadsheet

Setactivesheetindex phpspreadsheet
Setactivesheetindex phpspreadsheet

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

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 "
";
print_r($rowData);
echo "
";

Tiếp theo bạn tiến hành thực thi tệp docfile. php này, sẽ thấy kết quả trong màn hình là một mảng chứa tất cả thông tin của tệp sản phẩm excel. 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 có giới hạn

  10 điều bạn cần biết về PHP7

Ghi dữ liệu ra file excel

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

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

 array('name' => 'Hieu', 'email' => '[email protected]', 'phone' => '0123456789', 'address' => 'address 1'),
					1 => array('name' => 'Nam', 'email' => '[email protected]', 'phone' => '0124567892', 'address' => 'address 2'),
					2 => array('name' => 'Tuan', 'email' => '[email protected]', 'phone' => '09764346789', 'address' => 'address 3'),
					3 => array('name' => 'Mai', 'email' => '[email protected]', 'phone' => '09876543356', 'address' => 'address 4'),
					4 => array('name' => 'Thao', 'email' => '[email protected]', '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);

Last running file ghifile. php và mở tệp 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 với dòng dữ liệu trong mảng $array_data nhé

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

Nhập 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. tỉnh, huyện, phường

Setactivesheetindex phpspreadsheet
Setactivesheetindex phpspreadsheet

Trong bài demo này mình sẽ sử dụng Laravel và sử dụng Seed để nhập dữ liệu. Mình đã tạo dự án, di chuyển các bạn có thể xem trong mã nguồn. Data used to demo is 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 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ách làm là đọc dữ liệu từng sheet một và chèn vào các bảng tương ứng. Hiện thực hóa ý tưởng tưởng tượng trên ta là kết quả

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ỉ dùng các hàm đơn giản của PHPExcel để đọc dữ liệu ra, mình 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 tài liệu tại đây

  10 Framework tốt nhất hiện nay cho PHP

Xuất dữ liệu từ DB ra tệp excel

At on used data from file excel to DB, now will doing backback. Để tăng độ khó bài toán ta sẽ không xuất ra file giống như cũ mà giả sử khách hàng yêu cầu mình xuất dữ liệu ra file excel có định dạng như sau