[CakePHP] Xuất dữ liệu ra Excel sử dụng thư viện PHPExcel
Bài đăng này đã không được cập nhật trong 3 năm
Trong các ứng dụng web hẳn các bạn đã gặp hoặc từng phát triển chức năng cho phép người dùng xuất dữ liệu ra file dưới dạng Excel. Với ứng dụng sử dụng CakePHP nói riêng hay PHP nói chung thì bạn sẽ có hẳn một thư viện đồ sộ hỗ trợ - đó chính là PHPExcel. Bài này tôi sẽ thiệu về nó, cách tích hợp vào CakePHP và cách sử dụng cơ bản với một demo nhỏ.
Yêu cầu môi trường
Thư viện PHPExcel đòi hỏi môi trường của bạn phải có những thiết lập như sau :
- Phiên bản PHP sử dụng bằng hoặc cao hơn 5.2.0
- Gói php_zip phải enabled lên nếu bạn muốn xuất Excel 2007
- Gói php_xml enabled
- Nếu gói php_gd2 không được compile cùng PHP thì cũng cần enabled
Tích hợp PHPExcel với CakePHP
Hiện bản stable mới nhất của PHPExcel là 1.8.0, các bạn hãy download về theo đường link này. Sau khi download xong, hãy giải nén và copy thư mục và file có trong thư mục PHPExcel_1.8.0_doc/Classes/ vào thư mục app/Vendor/
Tiếp theo, bạn hãy tạo một file mới trong app/View/Helper với tên PhpExcelHelper.php, nội dung file này bạn hãy copy từ file có tên tương ứng ở link này của tác giả segy trên github. Nhưng bạn sẽ gặp lỗi not found nếu không sửa dòng 41 và 54 trong file đó thành như dưới đây :
App::import('Vendor', 'PHPExcel');
Việc tích hợp với CakePHP đã hoàn thành với vài bước rất đơn giản như trên, giờ hãy tạo một demo nho nhỏ.
Vừa tìm hiểu vừa code demo
Chuẩn bị
Tôi sẽ dựng một demo với nội dung đơn giản là một hệ thống bán hàng online rút gọn với chỉ 2 chức năng chính : list các invoice ra với các thông tin về khách hàng và ngày order (index.ctp), tiếp đến là khi vào chi tiết sẽ có chức năng xuất invoice ra dưới dạng file Excel (view_invoice_detail.ctp). Lần lượt các view này được gọi từ 2 phương thức index() và viewInvoiceDetail($invoiceId) có trong Controller tương ứng, code và giải thích cho phần này các bạn có thể xem chi tiết trong InvoicesController.php. Phần DB các bạn hãy xem script tạo có trong readme.md trên github. Về phần app/Model, có các file model tương ứng định nghĩa mối quan hệ giữa các bảng sử dụng kiến thức tôi đã có post ở bài này, các bạn có thể tham khảo nếu chưa hiểu về Linking model.
Sau khi chuẩn bị và chạy thử với các phần trên, bạn sẽ có kết quả tương tự như 2 hình sau :
Invoice list
Invoice detail
Demo
Tạo và xuất Excel
Với Helper của segy, bạn cần khai báo sử dụng nó trong Controller
var $helpers = array('PhpExcel');
Tiếp theo là cần phương thức để lấy dữ liệu cần thiết xuất ra Excel
public function exportToExcel($invoiceId) {
// phần code bạn hãy xem trong source, nội dung giống với viewInvoiceDetail()
}
Giờ bạn đã có nhưng cái cần thiết, tiếp theo là tạo ra một file (worksheet) để thao tác với nó, bạn code như sau trong view tương ứng :
$this->PhpExcel->createWorksheet();
Đây là cách tạo mới, ngoài ra bạn còn có thể load lên một file sẵn có để làm việc với nó nhưng bài này tôi sẽ không đề cập đến.
Để kết thúc một phiên làm việc với file đó, bạn có thể output nó ra browser cho end user hoặc save nó xuống local server. Ở đây, tôi sẽ đưa file ra browser :
$this->PhpExcel->output('invoice.xlsx');
Cuối cùng, bạn hãy giải phóng bộ nhớ được dùng bởi PHPExcel bằng dòng sau :
$this->PhpExcel->freeMemory();
Tạo nội dung Excel
Nếu bạn chạy chương trình thì sẽ thấy được file Excel nhưng nội dung vẫn chưa có, đương nhiên ! bởi ta chưa làm gì cả 
Giờ hãy bắt tay vào xây dựng nội dung cho invoice sẽ xuất ra.
1)setDefaultFont()
Việc đầu tiên là tôi sẽ thiết lập giá trịnh font và size cho toàn bộ worksheet bằng phương thức sau, bạn có thể chọn bất cứ font, size chữ nào thích hợp cho web của bạn :
$this->PhpExcel->setDefaultFont('Time New Roman', 12);
2)setCellValue()
Tiếp theo, như hình invoice detail ở trên tôi cần xuất ra những thông tin cơ bản về invoice như tên khách hàng, địa chỉ ... tôi sẽ bắt đầu từ cột A dòng đầu tiên đến dòng thứ 5, do các giá trị này tôi biết chắc chúng sẽ ở đó. Lúc này, tôi sẽ dùng đến setCellValue() để làm việc này. Nó nhận vào 2 đối số là cell và giá trị cho cell đó :
$this->PhpExcel->getActiveSheet()->setCellValue(
'A1', 'Detail of invoices #' . $invoiceDetails[0]['Invoice']['id']);
$this->PhpExcel->getActiveSheet()->setCellValue(
'A2', 'Customer : ' . $invoiceDetails[0]['Invoice']['customer_name']);
$this->PhpExcel->getActiveSheet()->setCellValue(
'A3', 'Address : ' . $invoiceDetails[0]['Invoice']['customer_addr']);
$this->PhpExcel->getActiveSheet()->setCellValue(
'A4', 'Tel : ' . $invoiceDetails[0]['Invoice']['customer_tel']);
$this->PhpExcel->getActiveSheet()->setCellValue(
'A5', 'Order date : ' . $invoiceDetails[0]['Invoice']['order_date']);
3)setRow()
Nếu tiếp tục xuất dữ liệu thì nhìn sẽ bị sát nhau quá nên hãy cách ra một dòng bằng cách set lại row hiện tại với hàm setRow(), do đã đến dòng thứ 5 nên tôi sẽ set con trỏ đến dòng 7 :
$this->PhpExcel->setRow(7);
4)addTableHeader()
Giờ đến lúc xuất ra chi tiết những sản phẩm mà khách hàng đã mua, hỗ trợ cho việc này Helper có phương thức addTableHeader(). Nó nhận vào đối số thứ nhất là mảng giá trị các header, thứ hai là mảng các giá trị để định đạng header. Vì là header nên tôi sẽ bôi đậm chúng.
$table = array(
array('label' => __('No'), 'width' => 5),
array('label' => __('Code'), 'width' => 15),
array('label' => __('Product'), 'width' => 30, 'wrap' => true),
array('label' => __('Price')),
array('label' => __('Quantity')),
array('label' => __('Sub total'))
);
$this->PhpExcel->addTableHeader($table, array('bold' => true));
Ở trên, các bạn thấy chúng ta có thể thiết lập độ rộng cũng như giá trị nếu dài quá thì xuống dòng hay không với width và wrap.
5)addTableRow()
Phần header đã xong, tiếp theo là nội dung của bảng dữ liệu, tôi sẽ dùng phương thức addTableRow(). Nó nhận vào một mảng giá trị thông qua việc lặp biến mảng $invoiceDetails lấy được từ Controller.
$i = 1;
foreach ($invoiceDetails as $invoiceDetail) {
$this->PhpExcel->addTableRow(array(
$i,
$invoiceDetail['Product']['product_code'],
$invoiceDetail['Product']['product_name'],
$invoiceDetail['InvoiceDetail']['product_price'],
$invoiceDetail['InvoiceDetail']['product_quantity'],
$invoiceDetail['InvoiceDetail']['sub_price_total']
));
$i++;
}
6)Thiết lập các công thức tính
Nếu bạn xuất ra file lúc này thì có thể được kết quả gần như ý, việc còn lại chỉ là tính ra tổng của invoice khi không có và có thuế VAT.
// get next row to let a blank row before export total
$next2Row = $this->PhpExcel->getRow() + 2;
// set label for total
$this->PhpExcel->getActiveSheet()->setCellValue('E'. (string)$next2Row, 'Total excl');
$this->PhpExcel->getActiveSheet()->setCellValue('E'. (string)($next2Row + 1), 'VAT (10%)');
$this->PhpExcel->getActiveSheet()->setCellValue('E'. (string)($next2Row + 2), 'Total incl');
Đoạn code này có một chút tính toán để lấy được dòng hiện tại mà PHPExcel đang thao tác đến, sau đó cộng thêm 2 để cách ra một dòng để nội dung bảng không quá sát với phần tính tổng. Sau đó bạn vẫn dùng setCellValue() để xuất title cho 3 dòng như trên.
Bạn thấy tôi có dùng phương thức getRow() để làm việc đó nhưng do nó chưa có trong Helper nên tôi đã thêm nó vào với nội dung sau :
public function getRow() {
$currentRow = $this->_tableParams['header_row'] + $this->_tableParams['row_count'];
return $currentRow;
}
Bằng cách cộng dòng header bắt đầu với số dòng dữ liệu add vào ta sẽ có được dòng hiện tại mà con trỏ đang ở đó.
Ok, việc cuối cùng là đưa công thức vào ô tương ứng để tính toán một cách tự động tổng tiền của invoice :
$subTotalFrom = $this->PhpExcel->_tableParams['header_row'] + 1;
$subTotalTo = $next2Row - 2;
$this->PhpExcel->getActiveSheet()->setCellValue('F'.(string)($next2Row),
'=SUM(F' . (string)($subTotalFrom) . ':F' . (string)($subTotalTo) .')');
$this->PhpExcel->getActiveSheet()->setCellValue('F'. (string)($next2Row + 1),
'=F' . (string)($next2Row) . '*0.1');
$this->PhpExcel->getActiveSheet()->setCellValue('F'. (string)($next2Row + 2),
'=F'. (string)($next2Row) . '+F' . (string)($next2Row + 1));
Nhìn qua thì có vẻ phức tạp nhưng nó rất đơn giản nhưu khi bạn muốn thực hiện một công thức trong Excel đó là bao giờ cũng bắt đầu bằng dấu =. Phức tạp ở chỗ bạn phải tính ra được các giá trị cell cần đưa vào công thức, ví dụ =SUM() thì bạn phải biết được cell bắt đầu và kết thúc. Tiếp theo là tính VAT, rồi tính tổng đã gồm VAT là xong.
Lưu ý : do các biến trả về có kiểu int nhưng setCellValue() lại nhận kiểu string nên để chắc chắn bạn hãy ép kiểu cho chúng.
7) setWrapText()
Mặc dù tôi đã thiết lập 'wrap' => true cho cột Product nhưng có vẻ nó không có tác dụng nên tôi đã tìm cách khác là dùng setWrapText()
$this->PhpExcel->getActiveSheet()->getStyle('C1:C'.$this->PhpExcel->getActiveSheet()->getHighestRow())
->getAlignment()->setWrapText(true);
Dòng trên có nghĩa thiết lập style cho một cột cố định với giá trị từ dòng đầu cho đến dòng lớn nhất mà PHPEXcel trỏ đến. Trường hợp này là cột C để wrap những tên Product dài quá width = 30 đã thiết lập.
8) Chạy thử
Đến đây bạn đã có code hoàn chỉnh để có thể chạy thử và xuất ra được layout tương tự như giao diện web :
Lời kết
Ở bài này có thể nói là làm quen với PHPExcel, trong các bài sau tôi sẽ tìm hiểu và chia sẻ thêm nhưng tính năng hữu ích mà thư viện này cung cấp. Đơn cử như bạn thấy nội dung của file vẫn chưa được đinh dạng đẹp lắm, file chưa có thông tin metadata, có thiết lập được file để in ra đúng chuẩn mong muốn hay ko? ...
All rights reserved
