DANH MỤC: PHP TIP

Đọ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ẻ!

Nguồn: Toidicode.com

Thông tin tác giả

Vũ Thanh Tài

Vũ Thanh Tài

The best way to learn is to share

Hãy tham gia group facebook để cùng giao lưu chia sẻ kiến thức! Tham Gia

Bình Luận

    • 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

  • 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

    • 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à.

    • 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?

    • 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

  • 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

    • 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é!
  • 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 ạ?

  • 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

  • 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

  • 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

    • Để 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);

       

  • 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!

    • 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);

       

  • 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>';

  • ý 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>';

    • 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

    • 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]))