Toidicode.com

Toidicode.com

BASIC TO ADVANCE

Đọc ghi dữ liệu file excel với PHPExcel

Download Source

Trong thực tế, đôi lúc chúng ta sẽ cần phải truy - xuất dữ liệu bằng file excel (Microsoft 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à bài hôm nay mình sẽ hướng dẫn mọi người có thể làm được điều đó với thư viện PHPExcel.

1, Tổng quan.

-PHPExcel là thư viện giúp cho chúng ta có thể truy xuất dữ liệu từ file excel một cách đơn giản, và để sử dụng được nó thì server của các bạn phải đáp ứng đủ các yêu cầu sau:

  • PHP phiên bản từ 5.2.0 trở lên.
  • PHP extension php_zip enabled [^phpzip_footnote].
  • PHP extension php_xml enabled.
  • PHP extension php_gd2 enabled (if not compiled in).

-Sau khi đã đáp ứng đủ các yêu cầu trên thì việc tiếp theo chúng ta cần là download thư viện PHPexcel về máy để thực hiện các bước tiếp theo (download).

2, Ghi dữ liệu vào file excel.

-Đầu tiên muốn sử dụng được thư viện thì các bạn cần phải require file PHPExcel.php (trong thư mục Classes) vào nơi muốn sử dụng. Và dưới đây mình sẽ demo cho các bạn một ví dụ về ghi dữ liệu vào file excel (giải thích trong code luôn):

<?php
require "Classes/PHPExcel.php";
$data = [
    ['Nguyễn Khánh Linh', 'Nữ', '500k'],
    ['Ngọc Trinh', 'Nữ', '700k'],
    ['Tùng Sơn', 'Không xác định', 'Miễn phí'],
    ['Kenny Sang', 'Không xác định', 'Miễn phí']
];
//Khởi tạo đối tượng
$excel = new PHPExcel();
//Chọn trang cần ghi (là số từ 0->n)
$excel->setActiveSheetIndex(0);
//Tạo tiêu đề cho trang. (có thể không cần)
$excel->getActiveSheet()->setTitle('demo ghi dữ liệu');

//Xét chiều rộng cho từng, nếu muốn set height thì dùng setRowHeight()
$excel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
$excel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$excel->getActiveSheet()->getColumnDimension('C')->setWidth(30);

//Xét in đậm cho khoảng cột
$excel->getActiveSheet()->getStyle('A1:C1')->getFont()->setBold(true);
//Tạo tiêu đề cho từng cột
//Vị trí có dạng như sau:
/**
 * |A1|B1|C1|..|n1|
 * |A2|B2|C2|..|n1|
 * |..|..|..|..|..|
 * |An|Bn|Cn|..|nn|
 */
$excel->getActiveSheet()->setCellValue('A1', 'Tên');
$excel->getActiveSheet()->setCellValue('B1', 'Giới Tính');
$excel->getActiveSheet()->setCellValue('C1', 'Đơn giá(/shoot)');
// thực hiện thêm dữ liệu vào từng ô bằng vòng lặp
// dòng bắt đầu = 2
$numRow = 2;
foreach ($data as $row) {
    $excel->getActiveSheet()->setCellValue('A' . $numRow, $row[0]);
    $excel->getActiveSheet()->setCellValue('B' . $numRow, $row[1]);
    $excel->getActiveSheet()->setCellValue('C' . $numRow, $row[2]);
    $numRow++;
}
// Khởi tạo đối tượng PHPExcel_IOFactory để thực hiện ghi file
// ở đây mình lưu file dưới dạng excel2007
PHPExcel_IOFactory::createWriter($excel, 'Excel2007')->save('data.xlsx');

-Nếu như các bạn muốn xuất ra cửa sổ download thì chỉ cần sửa dòng cuối cùng thành:

header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="data.xls"');
PHPExcel_IOFactory::createWriter($excel, 'Excel2007')->save('php://output');

-Và đây là kết quả chúng ta thu được:

phpexcel toidicode.com

3, Đọc dữ liệu từ file excel.

-Mình sẽ demo đọc file data.xlsx chúng ta vừa tạo ở phần trên và giải thích luôn trong code.

<?php
//Nhúng file PHPExcel
require_once 'Classes/PHPExcel.php';

//Đường dẫn file
$file = 'data.xlsx';
//Tiến hành xác thực file
$objFile = PHPExcel_IOFactory::identify($file);
$objData = PHPExcel_IOFactory::createReader($objFile);

//Chỉ đọc dữ liệu
$objData->setReadDataOnly(true);

// Load dữ liệu sang dạng đối tượng
$objPHPExcel = $objData->load($file);

//Lấy ra số trang sử dụng phương thức getSheetCount();
// Lấy Ra tên trang sử dụng getSheetNames();

//Chọn trang cần truy xuất
$sheet = $objPHPExcel->setActiveSheetIndex(0);

//Lấy ra số dòng cuối cùng
$Totalrow = $sheet->getHighestRow();
//Lấy ra tên cột cuối cùng
$LastColumn = $sheet->getHighestColumn();

//Chuyển đổi tên cột đó về vị trí thứ, VD: C là 3,D là 4
$TotalCol = PHPExcel_Cell::columnIndexFromString($LastColumn);

//Tạo mảng chứa dữ liệu
$data = [];

//Tiến hành lặp qua từng ô dữ liệu
//----Lặp dòng, Vì dòng đầu là tiêu đề cột nên chúng ta sẽ lặp giá trị từ dòng 2
for ($i = 2; $i <= $Totalrow; $i++) {
    //----Lặp cột
    for ($j = 0; $j < $TotalCol; $j++) {
        // Tiến hành lấy giá trị của từng ô đổ vào mảng
        $data[$i - 2][$j] = $sheet->getCellByColumnAndRow($j, $i)->getValue();;
    }
}
//Hiển thị mảng dữ liệu
echo '<pre>';
var_dump($data);

 -Sau khi chạy file, bạn sẽ thu được kết quả như sau:

Đọc file excel vơi PHPExcel

 

-Ở trên mình chỉ demo và var_dump() biến data, còn các bạn sử dụng biến data để thêm vào database hay dùng để hiển thị kiểu khác thì các bạn có thể tùy chỉnh nhé!

3, Lời kết.

-Phần này mình chỉ demo chức năng đọc, ghi cơ bản còn các bạn muốn nâng cao hơn thì có thể tham khảo thêm các ví dụ tại đây.

Chúc các bạn một ngày vui vẻ!

Đăng ký nhận tin.

Chúng tôi chỉ gửi tối đa 2 lần trên 1 tháng. Tuyên bố không spam mail!

Vũ Thanh Tài

About author
The best way to learn is to share
Xem tất cả bài đăng

29 Comments

tuyệt vời cám ơn bài hướng dẫn của bạn nhé

Nguyễn Đức

7 năm trước

Cảm ơn bạn!

Toidicode.com

7 năm trước

xóa và sữa data như thế nào ạ

Nam

6 năm trước

anh oi có cách nào kiểm tra hay so sánh 1 cột trong DB với 1 cột 1 cột trong excel vd như 1 mã khách hàng có trong DB rồi thì bỏ qua ko insert vào DB nữa nếu chưa có thì insert vao

Hoàng

5 năm trước

Bạn ơi cho mình hỏi, nếu mình muốn đọc file excel rồi lưu vào database thì làm thế nào. Xin cảm ơn

Hong Nhi Le

7 năm trước

Bạn xem ở phần đọc file đó, mình đã xuất ra thành mảng dữ liệu rồi, bạn chỉ cần duyệt mảng và insert từng phần tử vào database là được mà.

Toidicode.com

7 năm trước

Bạn ơi, để giữ nguyên các định dạng đã được định dạng trong file excel khi import vào CSDL thì phải làm sao vậy bạn?

Nga

5 năm trước

a ơi sao e làm như ví dụ 1 của a mà cứ lỗi này là sao ạ

Warning: require(./Classes/PHPExcel.php): failed to open stream: No such file or directory in /home/sys_sk2a07/public_html/luuchudulie.php on line 2

Fatal error: require(): Failed opening required './Classes/PHPExcel.php' (include_path='.:/usr/share/pear:/usr/share/php') in /home/sys_sk2a07/public_html/luuchudulie.php on line 2

無名

5 năm trước

Toàn cái tên hót :v

Trung Đức

7 năm trước

hot là như nào bạn? :D

Toidicode.com

7 năm trước

có một vấn đề là khi import excel như kiểu này vào database với khoảng 1 triệu dòng thì sẽ bị treo máy, ko biết bạn có cách nào để giải quyết ko. thank

Vũ Công Thành

7 năm trước

Chào bạn,
Rất cảm ơn bạn đã quan tâm đến chủ đề.
Ở trường hợp như bạn đưa ra thì mình có 2 cách giải quyết như sau:

  • Chia ra thành nhiều lần import.
  • Chuyển đổi nó thành các câu SQL rồi import bằng cmd thì sẽ được bạn nhé!

Vũ Thanh Tài

7 năm trước

Anh cho em hỏi là: muốn import dữ liệu từ database vào 1 file excel đã có dữ liệu sẵn (vd như ảnh, cấu hình font chữ) thì làm như thế nào ạ?

Trung

6 năm trước

Có vấn đề khi file excel có nhiều dòng dạng đơn 10878 trong hình

https://imgur.com/a/NpxkFX6

Thì làm sao gom dữ liệu để insert vào data anh

Vũ Trần

6 năm trước

Chào bạn,
Trong trường hợp này bạn có thể merge rows lại được,  bằng cách sử dụng mergeCells()

http://consistentcoder.com/merge-cells-in-phpexcel

Vũ Thanh Tài

6 năm trước

mình làm theo mà gặp bug này mãi ko bug đc

Fatal error: Class 'XMLWriter' not found in /home/bitrix/www/Classes/O2/PHPExcel/Shared/XMLWriter.php on line 36

Mình đã check và cài cắm các extendsion như yêu cầu mà vẫn ko đc :*(

Xin được chỉ giáo

Cảm ơn ad nhiều

Tung Lv

6 năm trước

Chào bạn!
Lỗi này xuất hiện do PHP của bạn chưa cài cài gói php-xml nhé bạn

Vũ Thanh Tài

6 năm trước

ad cho mình hỏi thêm mình set value cho cell công thức này thì nó ko ăn

$excel->getActiveSheet()->setCellValue('A2', '=IF(B2<>"",VLOOKUP(B2,dulieu!$A$2:$B$10,2,FALSE),"")');

ko ghi đc vào cell, mình thay các hàm khác thì vẫn đc

Tung Lv

6 năm trước

Để thực hiện được hàm IF trong excel thì bạn phải sử dụng đối tượng caculation. Ví dụ như sau:
 

$ifFormula = '=IF(B2-300=0,B6,C6)';
$formulaResult = PHPExcel_Calculation::getInstance($objPHPExcel)
    ->calculateFormula(
        $ifFormula,
        'A1',
        $objPHPExcel->getActiveSheet()->getCell('A1')
    );
var_dump($formulaResult);

 

Vũ Thanh Tài

6 năm trước

cảm ơn bạn, bài viết rất hay và chi tiết.

Nguyễn Tình

6 năm trước

1 Hướng dẫn đầy đủ và chi tiết, mình không phải dân lập trình cũng có thể thực hiện ok.

nhưng có 1 thắc mắc là nếu mình muốn đọc file XLS có nhiều sheet thì câu lệnh phải thực hiện thế nào vậy bạn?

cảm ơn!

sơn

5 năm trước

Chào bạn, cảm ơn bạn đã quan tâm!
Bạn muốn đọc trang nào thì sửa dòng này nhé bạn
 

//Chọn trang cần truy xuất
$sheet = $objPHPExcel->setActiveSheetIndex(0);

 

Vũ Thanh Tài

5 năm trước

làm phiền bạn thêm 1 chút, đoạn code trên là mình echo ra web, mình muốn định dạng cột thứ 3 $item[3], kiểu như số xuất hiện ở cột đó nếu lớn hơn 5000 thì màu nền backgound sẽ là màu đỏ. mình tìm trong các bài hướng dẫn về CSS của bạn nhưng chưa tìm được cách làm. Nếu bạn rảnh có thể chỉ mình được không, cảm ơn nhiều.

echo '

         <tr>

        <td>'.$item[1].'</td>
         <td>'.$item[2].'</td>
         <td>'.$item[3]</td> </tr>';

sơn

5 năm trước

Bạn tham khảo background cho ô: 
https://toidicode.com/live?id=729 hoặc https://toidicode.com/live?id=730

Vũ Thanh Tài

5 năm trước

ý mình là style ô đó theo điều kiện ấy, vì ô đó được tạo bằng vòng lặp không phải 1 ô cố định:

</tr>';
        foreach($data as $id => $item) {


         echo '

         <tr>
         <td>'.number_format($item[0],2).'</td>
         <td>'.number_format($item[2],2)."%".'</td>
         <td>'.number_format($item[3],).'</td>
         <td>'.number_format($item[4]).'</td>
         </tr>';

sơn

5 năm trước

Cái này bạn kiểm tra là được mà, dùng biểu thức điều kiện để check nhé. Link: https://toidicode.com/cau-lenh-dieu-kien-if-else-trong-php-57.html

VD:
 

'<td' . ($item[3] > 5000 ? 'style="background: red"' : '') . '>' . $item[3] . '</td>'

 

Vũ Thanh Tài

5 năm trước

lại xin làm phiền bạn 1 lần nữa. Dữ liệu trong cột đầu tiên của file XLS của mình là ngày tháng, nhưng khi mình dùng PHP xls thì nó hiện thị ra dạng số, mình đã thử dùng hàm Date để định dạng :

foreach ($data as $id => $item)
 {
?>
<tr>
  <td ><?= date("d/m/Y",$item[0])?></td>
  <td ><?=$item[1]?></td>
  <td ><?=$item[2]?></td>

Nhưng trên hiển thị nó chỉ ra ngày 01/01/1970, xin hướng dẫn cách khắc phục với, cảm ơn bạn

sơn

5 năm trước

Chào bạn,

Do dữ liệu date đã được chuyển sang int nên bạn cần convert nó về time trước khi domat date

date("d/m/Y", strtotime($item[0]))

 

Vũ Thanh Tài

5 năm trước

Mình đã theo hướng dẫn đọc được file excle rồi nhưng bị lỗi font thì phải xử lý như nào ạ?

Lý Đông Hà

4 năm trước

Bình luận

Captcha